Excel FV Function

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.

excel fv function v 1.0
Excel FV Function

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.

Excel FV Function November 6th, 2016Team

You May Also Like


Related pages


freight collect shipping termexcel mirrhow to calculate asset turnovercontribution to sales ratio formularatio formula in excelannuity with growth formulasales accrualsallowance for uncollectible accounts journal entryfixed asset registerscash count templatetotal labor variance formulacalculate net realizable valuemeaning of narration in accountingcurrent ratio versus quick ratiomeaning deferred revenue expenditure give examplesnon profit balance sheet template excelprepaid expenses accounting definitionjournal entry for tdsaccounting for convertible debenturesjournal entry of provision for doubtful debtsnpv of an annuitypre incorporation expensespayback analysis examplemonthly depreciation formulawhat is consignment inventorydays in accounts receivable formulabond amortization accountingcogmpresent value of annuity calculatorcollection of accounts receivable journal entrycalculating deferred tax assetgeneral ledger formatledger sheets to printunearned service revenue debit or credittrial balance format in excelfinancial accounting exercises and solutionslifo perpetual vs periodicprepaid expenses journal entrycontra entry accounting definitiontrade discount accounting entrydilapidation provision ifrsdeclining balance method formulasample accounts receivable reportwhat does dividends declared meanaverage account receivablesdepreciation straight line method calculatorcontribution margin calculation examplejournal entry for depreciation expenseproforma of petty cash bookhow to calculate pmt in financeclosing stock trial balancedefine notes receivablecash operating cycle definitionproduct costing systemfixed and variable expenses definitionfob shipping point and destinationcomparative and common size balance sheetis unearned revenue an asset or liabilitydiminishing interest calculatorwarranty journal entrymonthly balance sheet excel templatesundry expenses definitionhow to calculate pv of annuityending finished goods inventory formulahow to calculate interest on notes receivablefactoring flow charthow to figure out ending inventoryendowment funds definitionwhat is materials requisitiongeneral ledger reconciliation templatebookkeeping entriesprofitability index formulahow to do adjusting journal entriesbad debts debit or creditis prepaid expense a liabilityformula for total asset turnoverinsurance accounting basicscontra entrieswhat is fixed asset turnover ratiowhat is debit note with exampleformulas of simple interest and compound interestaccounts payable adjusting entryfinished goods inventory formulatemplate for bookkeeping small business