Excel PV Function

The Excel PV function has the syntax shown below.

PV(Rate, Nper, Pmt, FV, Type)
Arguments used in the Excel PV function
Rate = Discount rate per period (i)
Nper = Number of periods (n)
Pmt = Periodic payment
FV = Future Value
PV = Present Value
Type = 0 or 1 depending on whether Pmt is at the end or start of a period

Use of the Excel PV Function

The Excel PV function is used to calculate the present value (PV) of the following:

  • Present value of a lump sum
  • Present value of an annuity
  • Present value of an annuity due
  • Present of a combined annuity and lump sum
  • Present value of a combined annuity due and lump sum

Present Value of a Lump Sum Using the Excel PV function

The Excel PV function can be used to calculate the present value (PV) of a lump sum of money received at the end of a period in the future.

Present Value of a Lump Sum Example 1

If a lump sum of 7,000 is received at the end of period 4, and the discount rate is 5%, then the value of the lump sum today is given by the Excel PV function as follows:

Rate = 5%
Nper = 4
Pmt = 0 (not used)
FV = 7,000
Type = 0 (not used)
Present value = PV(Rate, Nper, Pmt, FV, Type)
Present value = PV(5%,4,0,7000,0) 
Present value = -5,758.92

The present value (PV) is negative as it represents the payment you would need to make today 5,758.92 (cash out), in order to receive back the 7,000 (cash in) at the end of period 4.

When using the Excel PV 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.

Present Value of a Lump Sum Example 2

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

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

Again the present value is negative as it represents the payment out at the start of year 1 to receive 6,000 at the end of year 3.

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 PV function, so in the above example PV(6%/12,3*12,0,6000,0) would return the same answer.

Present Value of an Annuity Using the Excel PV Function

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

Present Value of an Annuity Example

If the discount rate is 9%, what lump sum would need to be paid today for an annuity of 500 per month to be received at the end of each month for the next 7 years.

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

Present value = PV(Rate, Nper, Pmt, FV, Type)
Rate = 9%/12 monthly
Nper = 7 x 12 = 84 months
Pmt = 500 per month
FV = 0 (not used)
Type = 0 (payment at the end of the period)
Present value = PV(9%/12,84,500,0,0) 
Present value = -31,076.98

At a discount rate of 9%, the amount of 31,076.98 would need to be paid out at the start of year 1 in order to receive payments of 500 at the end of each month for the next 7 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 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

Present Value of an Annuity Due Using the Excel PV Function

An 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.

Present Value of an Annuity Due Example

If the discount rate is 9%, what lump sum would need to be paid today for an annuity of 500 per month to be received at the start of each month for the next 7 years.

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

Present value = PV(Rate, Nper, Pmt, FV, Type)
Rate = 9%/12 monthly
Nper = 7 x 12 = 84 months
Pmt = 500 per month
FV = 0 (not used)
Type = 1 (payment at the start of the period)
Present value = PV(9%/12,84,500,0,1) 
Present value = -31,310.06 

If the discount rate is 9%, the amount of 31,310.06 would need to be paid out at the start of year 1 in order to receive payments of 500 at the start of each month for the next 7 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.

The only change made in this example compared to the previous annuity example is that the Type argument has been set to 1 to represent payments at the start of the period. This has increased the amount required (present value) since the payments from the annuity (500) are going to be received one period earlier.

Present Value of Combined Annuity and Lump sum

The Excel PV function can be used to calculate combinations of regular annuity payments together with a lump sum received at the end of the term. This is particularly useful for example, when carrying out bond price calculations.

Suppose the discount rate was 10%, and a business issued 10,000, 18 month, 8% bonds, with interest payable every 6 months. The total face value (par value) of the bonds is 10,000. The interest payment every 6 months for 18 months is 10,000 x 8% x 6 / 12 = 400.

The price of the bond can be calculated using the Excel PV function as follows:

Present value = PV(Rate, Nper, Pmt, FV, Type)
Rate = 10%/2 = 5% per 6 month period
Nper = 3 (6 month periods)
Pmt = 400 per month
FV = 10,000
Type = 0 (payment at the end of the period)
Present value = PV(5%,3,400,10000,0) 
Present value = -9,727.68 

The Excel PV function has worked out the present value of the annuity of 400, and the present value of the lump sum received at the end of the term and combined them.

The present value of the cash flows from the bond is 9,727.68 which is the theoretical price of the bond.

A similar calculation can be carried out using an annuity due by setting the Type argument to 1. In all cases the lump sum future value (FV) is deemed to have been received at the end of the term and is not affected by the Type argument.

Using the Excel PV 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 pv function v 1.4
Excel PV Function

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

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

Excel PV Function November 6th, 2016Team

You May Also Like


Related pages


example accounting equationcommon size income statement excelannualized formula excelprofit margin spreadsheetaccounting for accrued expensesoperating roastockholders equity examplesexplain retained earningsannuity factor excelrecording adjusting entriesfuture value of lump sum excelaccelerated methods of depreciationcreditors accounts payableshort term notes payable definitionfifo ending inventory calculatormarkup calculationsaccounting equation isadjusting journal entries examplestock turnover ratio exampleaccounting spreadsheet templatescashflow statement sampleafs securitiesremittance advice chequeconstruction accounting journal entriessample chart of accounts manufacturingpresent value of deferred annuity calculatornet realizable value for inventoryexcel calculate loan balancewhat is the meaning of retained earningsbookkeeping on excelprepaid insurance entryadjusting entry for accrued revenuepayback formulaapproximate doubling time formuladiscount on bonds payable balance sheetbasics of accounting entriesjournal entry for accounts payablepurchase ledgeaging accounts receivableperpetual method of inventoryexamples of accounting equationthe allowance for doubtful accountsaccounts payable basicsvariable overhead efficiency varianceprepaid expense meaningaccrual journal entry exampleunits of activity depreciation calculatorsundries expenses examplespaid interest on loan journal entryasset impairment journal entrydoubling rate equationhow to accrue payrollvariable expense per unit formuladeferred tax liabilities exampletabel present value lengkapprepaid insurance on balance sheetaccrual journal entriesformula for calculating payback periodaccounts receivable test questions and answersamortisation examplestandard costing formulacalculation of debtor dayshow to calculate retained earnings on balance sheetimplicit interest rateunrealized gain loss balance sheetperpetuity ba ii plussum of years digits methoddividend payout ratio formulacompute contribution marginwages expense journal entrywhat is vertical analysis in accountingbank overdraft asset or liabilitystraight-line method of depreciation calculatorvoucher entry in accounting