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.
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 calculation of the future value in Excel. Let’s look at the syntax of the future value formula in Excel.
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 it 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 represent payments made at the end of period and 1 represent 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 annual payment of 5,000$.
Future value can be calculated by using FV formula 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$.
We will be achieving the same result if we replace the Pmt argument with Pv argument, hence replacing the Pmt with 21,647$.
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.
Consider a cash flow as following:
Now we can calculate the future value of each cash flow as:
The future value of these cash flows will be 22,149$.
Hope you enjoyed this post on Future value calculation in Excel. If you have any questions, let me know through the comment section below.
Did you know, you can download the Beginners' Guide to Real Estate Financial Terms for FREE!