Spreadsheets

SIP future value in Excel with the FV function (assumed return)

Use the FV function to see what a monthly SIP could grow to at an assumed return: the formula, the minus sign, and begin versus end of month.

Editorial illustration for SIP future value in Excel with the FV function (assumed return)

To see what a monthly SIP could grow to at an assumed return, use =FV(annual rate/12, years*12, -monthly amount). For ₹5,000 a month over 10 years at an assumed 10% a year, =FV(10%/12, 120, -5000) returns about ₹10,24,225. The 10% is a figure you type in to run a what-if, not something the formula, or anyone, can promise.

The FV formula, one argument at a time

The full syntax is =FV(rate, nper, pmt, [pv], [type]). It works the same way in Excel and Google Sheets.

  • rate: the return for one period. For monthly instalments divide the yearly assumption by 12, so 10% a year becomes 10%/12, or B2/12 if the rate sits in B2.
  • nper: the number of instalments, not the number of years. Ten years of monthly payments is 120, written as B3*12.
  • pmt: what you pay each period, entered as a negative number (the reason is explained below).
  • pv: money already sitting in the pot at the start. Leave it as 0 for a fresh SIP.
  • type: 0 if each payment is made at the end of the month, 1 if at the start.

Setting up the sheet

  1. A1 "Monthly amount", B1 5000.
  2. A2 "Assumed return per year", B2 10% (format the cell as a percentage).
  3. A3 "Years", B3 10.
  4. A4 "Future value", B4 =FV(B2/12, B3*12, -B1, 0, 0).
  5. A5 "Total paid in", B5 =B1*B3*12, which is ₹6,00,000 here.
  6. A6 "Growth", B6 =B4-B5, about ₹4,24,225 on these inputs.

Why does the payment need a minus sign?

Excel treats money leaving your pocket as negative and money coming back as positive. Each instalment goes out, and the lump sum comes back later. If you type 5000 instead of -5000 the answer is the same size but negative, which looks like a mistake and is not. Putting the minus inside the formula, as -B1, keeps your input cell a plain positive number.

Begin or end of the month: what does the type flag change?

With type 0 each payment earns for one month less than with type 1, so the type 1 result is a little higher. Same inputs as above:

TimingFormulaValue after 10 years
End of month (type 0)=FV(10%/12, 120, -5000, 0, 0)₹10,24,225
Start of month (type 1)=FV(10%/12, 120, -5000, 0, 1)₹10,32,760
Difference-₹8,535

Pick the one that matches your debit date. If your instalment leaves on the 5th and the month is counted from the 1st, either choice is a fair approximation.

Does the same formula work for a recurring deposit?

Roughly, yes. Put the deposit's quoted yearly rate in the rate cell. But banks may compound at intervals other than monthly, so the maturity amount printed on your deposit receipt can differ slightly from the sheet. The receipt is the figure that counts; the sheet is for planning before you open one.

What happens when the assumed return changes?

Run the same 10 years at three different assumptions, and include one you find uncomfortable:

Assumed returnValue after 10 yearsGrowth over ₹6,00,000 paid in
8% a year₹9,14,730₹3,14,730
10% a year₹10,24,225₹4,24,225
12% a year₹11,50,193₹5,50,193

What to check before you trust the number

  • nper is in months, so years must be multiplied by 12.
  • The yearly rate is divided by 12 inside the formula.
  • The percentage cell is really a percentage: typing 10 in a cell formatted as General makes 1000%.
  • The payment carries a minus sign, or the result is read as negative on purpose.
  • Step-ups, missed months and fund charges are not in this formula; the figure is a clean-line estimate.

A sheet shows what could happen; your statements show what did. If you want to see what actually went out each month, SpendixAI can take a bank or card statement in PDF, CSV or Excel, let you review the rows, and group the instalments under a category of your own.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports