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


accrued income examplesdividend declaration form templateinternal control checklist templatepv annuity duehow to calculate depreciation expense straight line methodsales return process in sapjournalizing transactions in accounting examplesroa return on assetsexcel accounting templates for small businessescash flows excelamortization of bond discounttrade receivables formulaexamples of subsidiary ledgersdoubtful accounts expenseredeemable preference shares accounting treatmentdouble entry accounting transactions are made up ofretain earningmeaning of imprest systemtrial balance exercise with answercost variance definitioncalculate credit salesconstant growth annuityoverhead variancesexample of double declining balance methodwhat is unearned income in accountingare accounts receivable debit or creditquick ratio formula exampleadjusted journal entriesledger sheet templatepv of annuity tablehow to calculate mark up percentagefob merchandiseunearned revenue on a balance sheetformula of inventory turnoverexcel formulas pmtstraight line depcalculate days sales in receivablesdeffered tax calculationcalculating the effective annual ratecontribution margin methodcash flow balance sheet templateunpresented cheques in bank reconciliationbookkeeping procedures templatewhat kind of account is unearned revenueformula to calculate marginal costrecording a capital leasecommon stock valuation formuladefine payback periodwhat are reconciling itemssales ledger control account formatexcess and obsolete inventory definitionexamples of long term investments on a balance sheetaccounting t accounts templatecapitalize leasecapital lease interest ratepromissory note journal entryaccured expensedividends equationday sales outstanding calculationconsignment accounting treatmentbasic double entry bookkeepingexample of a trial balance worksheetbank reconciliation statement exercisesformula to work out marginfunctional format income statementhow to balance petty cashfuture value annuity due tableperiodic compound interest calculatorperiodic inventory systemspayback method calculatorjob costing template excelimprest amounttotal debt to equity ratio interpretationdegree of total leverage calculatorcalculate reorder pointdepreciation template excel