Future Value Calculation in Excel

Future value calculation in Excel can be done either by using Excel FV formula or by manual calculation. Before we get into the calculations, let's review the concept of future value.

We have discussed the time value of money earlier. To remind ourselves, the basic premise of time value of money is that a dollar today is worth more than a dollar in the future.

Money has interest-earning potential and 'interest earned' over a period is referred to as the 'time value' of money.

Example of Future Value Calculation in Excel

Suppose you have 100$ as of today and the bank deposit rate is 5%. Next year it will become 100*(1+5%)^1 i.e. 105$ if you deposit in the bank.

Consider another scenario, your friend borrows 100$ from you and returns next year. He borrowed 100$ and returned 100$, but your 100$ is worth less now due to inflation.

So, simply put, the future value of 100$ after one year is 105$ at the rate of 5%.

Now let's get back to the Future Value Calculation in Excel. Let's look at the syntax of the future value formula in Excel.

=FV(rate,nper,pmt,[pv],[type])

Where; 'Rate' is the interest rate per period; 'Nper' is the total number of payment periods. 'Pmt' is the payment made each period. Note that in this formula the payment cannot change over the payment period.

'Pv' is optional input and the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero).

'Type' is also an optional input. The number 0 represents payments made at the end of the period and 1 represents payments made at the beginning of the period. The default is 0, i.e. at the end of the period.

Since 'Pv' is the optional argument, if it is omitted you must use the pmt argument. And if the 'pmt' is omitted, you must include the 'pv' argument.

Let's consider the rate of 5%, 5-year period, and an annual payment of 5,000$.

Future Value Calculation in Excel

Future value can be calculated by using FV formula in Excel.

Future Value Calculation in Excel

The future value is 27,628$.

If you noticed, I have left the Pv argument empty. You can replace the pmt argument by using the pv argument. The present value of all the cash flow will be 21,647$.

Future Value Calculation in Excel

We will be achieving the same result if we replace the Pmt argument with Pv argument, hence replacing the Pmt with 21,647$.

Future Value Calculation in Excel

The result is 27,628$. We can also link to the cell C10 rather than using the value in the formula.

This future value formula has some limitations. It assumes consistent cash flow (without any break) and same amount each year.

In such situation future value calculation in Excel can be done by a different approach. We can calculate the future values of each cash flow individually by using the below formula and then sum it.