How To Calculate Future Value in Excel

Related Page:
Present Value
Varying the Period for a Future Value Calculation

The future value calculations on this page are applied to investments for which interest is compounded in each period of the investment.

However if you are supplied with a stated annual interest rate, and told that the interest is compounded monthly, you will need to convert the annual interest rate to a monthly interest rate and the number of periods into months:

 monthly interest rate = annual interest rate / 12 number of months = number of years * 12

A similar conversion is required if interest is paid quarterly, semi-annually, etc.

For an example of this, see the section on How To Calculate Future Value When Interest is Compounded Monthly

Future Value of a Single Cash Flow With a Constant Interest Rate

If you want to calculate the future value of a single investment that earns a fixed interest rate, compounded over a specified number of periods, the formula for this is:

=pv*(1+rate)^nper

where,

• pv   is the present value of the investment;
• rate   is the interest rate per period (as a decimal or a percentage);
• nper   is the number of periods over which the investment is made.
Future Value of a Single Cash Flow
(with a Constant Interest Rate):
AB
1Present Value:10000
2Annual Interest Rate:4%
3Number of Years:5
4Future Value:=10000*(1+4%)^5

For example, if an investment of \$10,000 earns an annual interest rate of 4%, the investment's future value after 5 years can be calculated by typing the following formula into any Excel cell:

=10000*(1+4%)^5

which gives the result 12166.52902.

I.e. the future value of the investment (rounded to 2 decimal places) is \$12,166.53.

As with all Excel formulas, instead of typing the numbers directly into the future value formula, you can use references to cells containing values. Therefore, the future value formula in cell B4 of the above spreadsheet could be entered as:

=B1*(1+B2)^B3

which returns the same result.

Using the Excel FV Function to Calculate the Future Value of a Single Cash Flow

Instead of using the above formula, the future value of a single cash flow can be calculated using the built-in Excel FV function (which is generally used for a series of cash flows).

The syntax of the FV function is:

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

where,

• rate   is the interest rate per period (as a decimal or a percentage);
• nper   is the number of periods over which the investment is made;
• [pmt]   is the regular payment per period (if omitted, this is set to the default value 0);
• [pv]   is the present value of the investment (if omitted, this is set to the default value 0);
• [type]   specifies whether the payment is made at the start or the end of the period.

This can have the value 0 or 1, meaning:

0   -   the payment is made at the end of the period (as for an ordinary annuity);
1   -   the payment is made at the start of the period (as for an annuity due).

If omitted, the [type] argument is set to the default value 0.

Note that, in line with the general cash flow sign convention, the FV function treats negative values as outflows and treats positive values as inflows.

Future Value of a Single Cash Flow
(with a Constant Interest Rate):
AB
1Present Value:10000
2Annual Interest Rate:4%
3Number of Years:5
4Future Value:=FV( 4%, 5, 0, 10000 )

For example, if an investment of \$10,000 earns an annual interest rate of 4%, the investment's future value after 5 years can be calculated by the Excel FV function as follows:

=FV( 4%, 5, 0, 10000 )

which gives the result -\$12,166.53.

Note that in the above FV function:

• The [pmt] argument is set to 0, as there are no ongoing payments after the initial investment;
• The returned future value is negative, representing an outgoing payment.

How To Calculate Future Value When Interest is Compounded Monthly

If the interest on your investment is compounded monthly (while being quoted as an annual interest rate), the annual interest rate needs to be converted into a monthly interest rate and the number of years needs to be converted into months.

I.e.

 monthly interest rate = annual interest rate / 12 number of months = number of years * 12
Future Value Formula With Interest Paid Monthly:
AB
1Present Value:10000
2Annual Interest Rate:4%
3Number of Years:5
4Future Value:=FV( 4%/12, 5*12, 0, 10000 )

Therefore, if an initial investment of \$10,000 has a stated annual interest rate of 4%, (compounded monthly), the future value of the investment can be calculated as follows:

=FV( 4%/12, 5*12, 0, 10000 )

which gives the result -\$12,209.97.

(Note that, once again, the value returned from the FV function is negative, representing an outgoing payment).

Future Value of a Single Cash Flow With a Variable Interest Rate

If you want to calculate the future value of a single investment whose interest rate varies over the lifetime of the investment, the built-in Excel FVSCHEDULE function can be used for this.

The syntax of the FVSCHEDULE function is:

FVSCHEDULE( principal, schedule )

where,

• principal   is the present value of the investment;
• schedule   is an array of values that provides the schedule of interest rates to be applied to the principal.

If provided as a range of cells, these may contain numeric values or be empty (empty cells denote a zero interest rate).

Future Value of a Single Cash Flow
(with a Variable Interest Rate):
AB
1Present Value:10000
2Schedule:5%
3 5%
4 3%
5 3%
6 3%
7Future Value:=FVSCHEDULE(B1, B2:B6)

The above spreadsheet on the right shows the FVSCHEDULE function used to calculate the future value of an investment of \$10,000 that is invested over 5 years and earns an annual interest rate of 5% for the first two years and 3% for the remaining three years.

In the example spreadsheet, the value of the initial investment of \$10,000 is stored in cell B1 and the interest rates over each of the 5 years of the investment are stored in cells B2-B6. As shown in cell B7, the formula to calcuate the future value of the investment is:

=FVSCHEDULE( B2, B2:B6 )

which gives the result 12047.31518.

I.e. the future value of the investment (rounded to 2 decimal places) is \$12,047.32.

Future Value of a Series of Cash Flows (An Annuity)

If you want to calculate the future value of an annuity (a series of periodic constant cash flows that earn a fixed interest rate over a specified number of periods), this can be done using the Excel FV function.

The syntax of the FV function is:

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

where,

• rate   is the interest rate per period (as a decimal or a percentage);
• nper   is the number of periods over which the investment is made;
• [pmt]   is the regular payment per period (if omitted, this is set to the default value 0);
• [pv]   is the present value of the investment (if omitted, this is set to the default value 0);
• [type]   specifies whether the payment is made at the start or the end of the period.

This can have the value 0 or 1, meaning:

0   -   the payment is made at the end of the period (as for an ordinary annuity);
1   -   the payment is made at the start of the period (as for an annuity due).

If omitted, the [type] argument is set to the default value 0.

Note that, in line with the general cash flow sign convention, the FV function treats negative values as outflows and treats positive values as inflows.

Future Value of a Series of Periodic Constant Cash Flows:
AB
1Annual Interest Rate:4%
2Number of Years:5
3Annual Payment:500
4Future Value:=FV( 4%, 5, 500 )

For example, to calculate the future value of an ordinary annuity that has an annual interest rate of 4% and returns payments of \$500 per year for 5 years, type the following formula into any Excel cell:

=FV( 4%, 5, 500 )

which gives the result -\$2,708.16.

Note that in the above FV function:

• The [pv] argument is omitted, and so takes on the default value 0;
• the [type] argument is omitted, and so takes on the default value 0 (i.e. the calculation assumes that the payment is made at the end of each year);
• The returned future value is negative, representing an outgoing payment.

Again, as with all Excel formulas, instead of typing the numbers directly into the future value formula, you can use references to cells containing values. Therefore, the FV function in cell B4 of the above spreadsheet could be entered as:

=FV( B1, B2, B3 )

which returns the same result.