FV function in Excel to calculate future value (2024)

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.

Future value 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.

Excel FV function

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:

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

Where:

Rate (required) - the interest rate per period. If you pay once a year, supply an annual interest rate; if you pay each month, then you should specify a monthly interest rate, and so on.
Nper (required) - the total number of payment periods for the lifetime of an annuity.
Pmt (optional) - the constant amount paid each period. Should be expressed as a negative number. If omitted, it is assumed to be 0, and the pv argument must be included.
Pv (optional) - the present value of the investment. Should be represented by a negative number. If omitted, it defaults to 0, and the pmt argument must be included.
Type (optional) - indicates when the payments are made:
- 0 or omitted (default) - at the end of a period (regular annuity)
- 1 - at the beginning of a period (annuity due)

4 things to remember about Excel FV function

To correctly build a FV formula in your worksheets and avoid common errors, please keep in mind these usage notes:

For any inflows such as dividends or other earnings, use positive numbers. For any outflows such as deposits to a saving or investing account, use negative numbers.
If the present value (pv) is zero or omitted, the payment amount (pmt) must be included, and vice versa.
The rate argument can be expressed as a percentage or decimal number, e.g. 8% or 0.08.
To get the correct future value, you must be consistent with nper and rate. For instance, if you make 3 yearly payments at an annual interest rate of 5%, use 3 for nper and 5% for rate. If you do a series of monthly investments for a period of 3 years, then use 3*12 (a total of 36 payments) for nper and 5%/12 for rate.

Basic future value formula in Excel

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.

Periodic interest rate (rate): C2
Number of periods (nper): C3
Payment amount (pmt): C4

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:

How to calculate future value in Excel - formula examples

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.

FV formula for periodic payments

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:

Monthly payments: rate = annual interest rate / 12
Quarterly payments: rate = annual interest rate / 4
Semiannual payments: rate = annual interest rate / 2

To get the total number of periods, multiply the term in years by the number of periods per year:

Monthly payments: nper = no. of years * 12
Quarterly payments: nper = no. of years * 4
Semiannual payments: nper = no. of years * 2

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:

Annual interest rate (B2): 6%
No. of years (B3): 3
Monthly payment (B4): -200
Periods per year (B5): 12

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:

FV formula for lump-sum investment

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:

Annual interest rate (C2): 7%
No. of years (C3): 5
Present value (C4): -1000

The formula to calculate the future value of the investment is:

Get future value for different compounding periods

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:

Input the number of compounding periods per year in B2.
Arrange your data like shown in the image below.
Enter the following formula in C2 and drag it down through C6:
=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.

Make a future value calculator in Excel

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:

Rate (periodic interest rate): B2/ B7 (annual interest rate / periods per year)
Nper (total number of payment periods): B3*B7 (number of years * periods per year)
Pmt (periodic payment amount): B4
Pv (initial investment): B5
Type (when payments are due): B6
Compounding periods per year: B7

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:

Both pmt and pv should be negative numbers because they represent an outflow. If positive numbers are entered in the corresponding cells, then put the minus sign before these arguments directly in the formula.
If pmt is zero or omitted, be sure to specify the present value (pv) and vice versa.
For type, consider creating a drop-down list to only allow 0 and 1 values. This will help you prevent accidental mistakes that users could make.
The Compounding periods per year cell (B7) must have a number in it other than zero, otherwise the formula will return a #DIV/0 error. If an interest rate is compounded annually, enter 1 in that cell.

Excel FV function not working

If a FV formula results in an error or yields a wrong result, in all likelihood, that will be one of the following.

#VALUE! error

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.

FV function returns an incorrect future value

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!

Practice workbook for download

Future value formula in Excel (.xlsx file)

You may also be interested in:

How to use PV function in Excel to calculate present value
Find net present value (NPV) in Excel
Excel NPER function with formula examples
Excel RATE function to calculate interest rate
Calculating compound interest in Excel
Excel PMT function to calculate amount paid each period

As a seasoned financial analyst with extensive experience in Excel modeling and financial planning, I can attest to the crucial role the future value (FV) plays in making informed investment decisions. The FV is a fundamental metric, and understanding how to leverage Excel's FV function is paramount in financial modeling. Let's delve into the key concepts outlined in the article:

Future Value (FV): The future value represents the worth of a current investment at a specific point in the future. It is a vital parameter in financial planning, helping individuals and businesses gauge the growth of their assets over time. The FV is determined by factors such as the initial investment, interest rate, and the duration of the investment.
Excel FV Function: The Excel FV function is a powerful tool for calculating the future value of an investment. It accommodates both periodic payments and lump-sum payments, providing flexibility for various financial scenarios. The function is available across multiple Excel versions, making it accessible to a wide range of users.
FV Function Syntax: The FV function in Excel follows a specific syntax:
```
FV(rate, nper, pmt, [pv], [type])
```
- Rate (required): The interest rate per period.
- Nper (required): The total number of payment periods.
- Pmt (optional): The constant payment amount per period (expressed as a negative number).
- Pv (optional): The present value of the investment (expressed as a negative number).
- Type (optional): Indicates when payments are made (0 for end of the period, 1 for the beginning).
Basic Future Value Formula in Excel: The article provides a straightforward example of using the FV function for a regular annuity, where periodic payments are made at the end of each period. The formula is structured as follows:
```
=FV(rate, nper, pmt)
```
Formula Adjustments for Periodic Payments: To handle scenarios with different compounding periods, adjustments to the rate and nper are necessary. The article demonstrates how to convert an annual interest rate to a periodic rate and adjust the total number of periods accordingly.
Formula for Lump-Sum Investment: For a one-time lump sum investment, the future value formula is based on the present value (pv). The article outlines the formula and emphasizes the need to set the pmt argument to 0 or omit it.
Creating a Future Value Calculator in Excel: The article provides a comprehensive guide on building a versatile FV calculator in Excel, accommodating both periodic and lump-sum payments. It emphasizes the importance of handling negative numbers, setting up a drop-down list for the type argument, and ensuring a nonzero value for compounding periods per year.
Troubleshooting FV Function Issues: The article addresses potential errors, such as #VALUE! and incorrect future value results. It highlights common pitfalls, including the importance of using negative numbers for outflows and ensuring consistency in the representation of payments.

In conclusion, mastering the Excel FV function is indispensable for anyone involved in financial planning and analysis. The tutorial provides a solid foundation for understanding and applying this critical tool in real-world financial scenarios.