The Excel FV function has the syntax shown below.

FV(Rate, Nper, Pmt, PV, Type)

**Arguments used in the Excel FV function**

Rate = Discount rate per period (i)

Nper = Number of periods (n)

Pmt = Periodic payment

PV = Present Value

FV = Future Value

Type = 0 or 1 depending on whether Pmt is at the end or start of a period

## Use of the Excel FV Function

The Excel FV function is used to calculate the future value (FV) of the following:

- Future value of a lump sum
- Future value of an annuity
- Future value of an annuity due
- Future of a combined annuity and lump sum
- Future value of a combined annuity due and lump sum

## Future Value of a Lump Sum Using the Excel FV function

The Excel FV function can be used to calculate the future value (FV) of a lump sum of money paid today.

### Future Value of a Lump Sum Example 1

If a lump sum of 10,000 is invested today at a discount rate of 5%, what is the amount received at the end of period 2. The future value is given by the Excel FV function as follows:

Rate = 5% Nper = 2 Pmt = 0 (not used) PV = -10,000 (negative as payment is a cash flow out) Type = 0 (not used) Future value = FV(Rate, Nper, Pmt, PV, Type) Future value = FV(5%,2,0,-10000,0) Future value = 11,025.00

The future value (FV) is positive as it represents the amount you would receive back (cash flow in) at the end of period 2 following the investment of 10,000 (cash flow out) at the start of period 1.

When using the Excel FV function to calculate a lump sum value, the Pmt and the Type arguments are not used, they can be left blank or set to zero.

### Future Value of a Lump Sum Example 2

If a lump sum of 6,000 is invested today, and the discount rate is 6% per year compounded monthly, then the value of the lump sum at the end of year 3 is given by the Excel FV function as follows:

Rate = 6%/12 = 0.5% Nper = 3 x 12 = 36 Pmt = 0 (not used) PV = -6,000 Type = 0 (not used) Future value = FV(Rate, Nper, Pmt, PV, Type) Future value = FV(0.5%,36,0,-6000,0) Future value = 7,180.08

Again the future value is positive as it represents the amount to be received at the end of year 3 following an investment made at the start of year 1 of 6,000.

This time the discounting is monthly so the argument Rate is set to the monthly rate of 6%/12 = 0.5%, and the number of periods argument Nper is set to 3 x 12 = 36 months.

It should be noted that formulas (without the equals sign) can be entered as arguments in the Excel FV function, so in the above example FV(6%/12,3*12,0,6000,0) would return the same answer.

## Future Value of an Annuity Using the Excel FV Function

An annuity is a series of constant periodic payments received at the end of each period. The Excel FV function can be used to calculate the future value of an annuity.

### Future Value of an Annuity Example

If the discount rate is 8%, what is the future value of an annuity of 600 per month received at the end of each month for the next 5 years.

The amount to be paid must be the same as the future value of the monthly payments and is calculated using the Excel FV function as follows:

Future value = FV(Rate, Nper, Pmt, PV, Type) Rate = 8%/12 monthly Nper = 5 x 12 = 60 months Pmt = -600 per month PV = 0 (not used) Type = 0 (payment at the end of the period) Future value = FV(8%/12,60,-600,0,0) Future value = 44,086.11

At a discount rate of 8%, the amount of 44,086.11 would be received at the end of 5 years if the amount of 600 was invested at the end of each month.

In this example the argument PV is not used and can be set to 0 or left blank. The argument Type is used and is set to 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

## Future Value of an Annuity Due Using the Excel FV Function

An annuity due is a series of constant periodic payments received at the start of each period. An annuity due is similar to an annuity except that the payments are made at the start of each period instead of the end.

### Future Value of an Annuity Due Example

If the discount rate is 4%, what is the future value of an annuity due of 500 per month for 6 years.

Future value = FV(Rate, Nper, Pmt, PV, Type) Rate = 4%/12 monthly Nper = 6 x 12 = 72 months Pmt = -500 per month FV = 0 (not used) Type = 1 (payment at the start of the period) Future value = FV(4%/12,72,-500,0,1) Future value = 40,746.65

If the discount rate is 4%, the amount of 40,746.65 would be received at the end of 6 years if the amount of 500 was invested at the **start** of each month for 6 years.

In this example the argument FV is not used and can be set to 0 or left blank. The argument Type is used and is set to 1, and indicates that the annuity payment is at the start of each period.

## Future Value of Combined Annuity and Lump sum

The Excel FV function can be used to calculate combinations of regular annuity payments together with a lump paid at the start of the term. This is particularly useful for example, when at initial lump sum is invested followed by a series of regular payments.

Suppose the discount rate was 7%, and an initial amount of 40,000 was invested followed by a series of regular monthly payments of 700 at the end of each month. Then the amount available at the end of 5 years would be given by the Excel FV function as follows:

Future value = FV(Rate, Nper, Pmt, PV, Type) Rate = 7%/12 per month Nper = 5 x 12 = 60 months Pmt = -700 per month PV = -40,000 Type = 0 (payment at the end of the period) Future value = FV(7%/12,60,-700,-40000,0) Future value = 106,820.04

The Excel FV function has worked out the future value of the annuity of 700, and the future value of the lump sum of 40,000, invested at the start of year 1 and combined them.

The future value of the cash flows from the investment is 106,820.04.

A similar calculation can be carried out using an annuity due by setting the Type argument to 1. In all cases the lump sum initial investment (PV) is deemed to have been invested at the start of period 1 and is not affected by the Type argument.

## Using the Excel FV Function with Variable Arguments

Excel is at its most useful when the arguments can be varied without having to enter the Excel function each time a calculation is made. To do this a simple spreadsheet can be set up as shown below.

In this example spreadsheet, the variable arguments are entered in cells B4 to B8, and the Excel FV function is entered at cell B11 as =FV(B4,B5,B6,B7,B8). By changing any of the variables in B4 to B8, the future value can be recalculated without having to enter the Excel FV function each time.

The Excel FV function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

## About the Author

Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.