This tutorial looks at how to use the FV function in Excel to find the future value of a series of periodic payments and a single lump-sum payment.
Building your personal and corporate finances requires thorough planning. One of the most important factors of success is understanding how much an investment made today will grow to in the future. That is called the future value of investment, and this tutorial will teach you how to calculate it in Excel.
The future value (FV) is one of the key metrics in financial planning that defines the value of a current asset in the future. In other words, FV measures how much a given amount of money will be worth at a specific time in the future.
Normally, the FV calculation is based on an anticipated growth rate, or rate of return. When the money is deposited in a saving account with a predefined interest rate, determining a future value is quite easy. The FV of investments in stocks, bonds or other securities may be hard to calculate accurately because of a volatile rate of return.
Luckily, Microsoft Excel provides a special function that does all the math behind the scenes based on the arguments that you specify.
FV is an Excel financial function that returns the future value of an investment based on a fixed interest rate. It works for both a series of periodic payments and a single lump-sum payment.
The function is available in all versions Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.
The FV syntax is as follows:
Where:
To correctly build a FV formula in your worksheets and avoid common errors, please keep in mind these usage notes:
This example shows how to use the FV function in Excel in its simplest form to calculate future value, given a periodic interest rate, the total number of periods, and a constant payment amount per period.
Let's say you are going to make a yearly $1,000 payment for 10 years with an annual interest rate of 6%. It is assumed to be a regular annuity where all payments are made at the end of the year.
To find the future value, configure the FV function in this way:
=FV(C2, C3, C4)
Please notice that pmt is a negative number because this money is paid out.
If the payment is represented by a positive number, don't forget to put the minus sign right before the pmt argument:
=FV(C2, C3, -C4)
The basic Excel FV formula is very simple, right? Now, let's have a look at how to tweak it to handle a couple of most common scenarios.
When investing money through a series of regular savings, it often happens that you are provided with an annual interest rate and the investment term defined in years, whereas the payments are to be made weekly, monthly, quarterly or semiannually. In such situations, it is very important that the rate and nper units be consistent.
To convert an annual interest rate to a periodic rate, divide the annual rate by the number of periods per year:
To get the total number of periods, multiply the term in years by the number of periods per year:
Now, let's see how it works in practice. Suppose you monthly invest $200 for 3 years with an annual interest rate of 6%. The source data is input in these cells:
To calculate the future value of this investment, the formula in B7 is:
=FV(B2/B5, B3*B5, B4)
As shown in the image below, the same formula determines the future value based on quarterly savings equally well:
If you choose to invest money as a one-time lump sum payment, the future value formula is based on the present value (pv) rather than periodic payment (pmt).
So, we set up our sample data as follows:
The formula to calculate the future value of the investment is:
=FV(C2, C3, ,C4)
Please notice that:
If the compounding periods for your investment are not annual, then to determine the future value accurately, you need to make the following adjustments to the formula:
As an example, let's find the future value of the above investment with an interest rate compounded monthly. For this, we divide an annual interest rate (C2) by 12 and multiply the number of years (C3) by 12:
=FV(C2/12, C3*12, ,C4)
or
=FV(C2/C5, C3*C5, ,C4)
Where C5 is the number of compounding periods per year:
To compare the amount of growth generated by various compounding periods, you need to supply different rate and nper to the FV function.
To have all calculations performed with a single formula, do the following:
=FV($F$2/B2, $F$3*B2, ,$F$4)
Please pay attention that we lock the annual interest rate ($F$2), the number of years ($F$3) and the investment amount ($F$4) references with the dollar sign ($) so they won't shift when copying down the formula.
If your goal is to build a universal FV calculator that works for both periodic and lump-sum payments with either annuity type, then you will need to use the Excel FV function in its full form.
For starters, allocate cells for all the arguments, including the optional ones like shown in the screenshot below. And then, define the arguments in this way:
Putting the arguments together, we get this formula:
=FV(B2/B7, B3*B7, B4, B5, B7)
Suppose you wish to save some money for renovating your house in 5 years. You deposit $3,000 to your saving account at an interest rate of 7% compounded monthly. Furthermore, you are going to add $100 at the beginning of each month. How much money will there be in your saving account in 5 years? According to our Excel FV calculator - around $11,500.
When setting up a future value calculator for other users, there are a few things to take notice of:
If a FV formula results in an error or yields a wrong result, in all likelihood, that will be one of the following.
May occur if one or more arguments are non-numeric. To fix the error, check if any of the numbers referenced in your formula are formatted as text. If some are, then convert text values to numbers.
If the returned future value is negative or much lower than expected, most likely, either the pmt or pv argument, or both, are represented by positive numbers. Please remember that negative numbers should be used for all outgoing payments.
That's how to how to calculate future value of annuity in Excel. I thank you for reading and hope to see you on our blog next week!
Future value formula in Excel (.xlsx file)