Excel NPV Function

The Excel NPV function has the syntax shown below.

NPV(Rate, Value1, Value2, ...Value n)
Arguments used in the Excel NPV function
Rate = Discount rate
Value n = Cash flow at the end of period n

Use of the Excel NPV Function

The Excel NPV function is used to calculate present value of a series of cash flows for a given discount rate in time value of money calculations. The cash flows do not have to be the same, but must occur at the end of a period. The discount rate is the rate for a period.

The NPV function is used instead of the when the cash flows each period are unequal.

Net Present Value of an Investment Project

The Excel NPV function can be used to calculate the net present value of an investment project.

Despite its name, the Excel NPV function calculates the present value of the cash flows starting at the end of period 1, it does not include the original investment at the start of year 1 and so does not calculate net present value. To calculate net present value the original investment must be deducted from the answer given by the Excel NPV function.

Suppose for example, a business plans to invest 400 today (start of year 1) and will receive cash flows of 500, 1800, 600, and 300 in the following 4 years. If the discount rate is 8%, then the present value of the cash flows is given by the Excel NPV function as follows:

Rate = 8%
Value 1 = 500
Value 2 = 1,800
Value 3 = 600
Value 4 = 300
Present value = NPV(8%, 500, 1800, 600, 300)
Present value = 2,702.98 

This is the present value of the future cash flows, to calculate the net present value of the project the original investment of 400 must be deducted

Net present value = Present value - Original investment
Net present value = 2,702.98 - 400.00
Net present value = 2,302.98

It should be noted that the original investment is negative as it represents a cash flow out.

The Excel NPV function can be used with positive or negative values, for example had the cash flow in year 3 above been a cash flow out rather than a cash flow in, then the present value of the cash flows would be given by NPV(8%, 500, 1800, -600, 300) = 1,750.38.

In addition, if a value is zero, then it has to be entered as zero (0) and not left blank, for example had the cash flow in year 2 above been zero, then the present value of the cash flows would be given by NPV(8%, 500, 0, 600, 300) = 1,159.77

Using the Excel NPV 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 npv function
excel npv function

In this example spreadsheet, the variable arguments are entered in cells B4 to B10, and the Excel NPV function is entered at cell B13 as =NPV(B4,B7:B10). Note that the Excel NPV function could equally well be written as =NPV(B4,B7,B8,B9,B10).

By changing any of the variables in B4 to B10, the present value of the cash flows can be recalculated without having to enter the Excel NPV function each time. It should be noted that if a cash flow is zero it has to be entered as zero (0), it cannot be left blank otherwise the Excel NPV function will return the wrong answer.

To complete the calculation of the net present value, the original investment at the start of year 1 is entered at cell B15, and cell B17 is used to deduct this from the present value of the cash flows in cell B13 to arrive at the net present value of the project.

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

Excel NPV Function November 6th, 2016Team

You May Also Like


Related pages


formula perpetuityexamples of double entry journalsaccrued expenses double entrysingle entry bookkeepingthe contribution margin income statementvertical analysis income statement calculatoraccounts payable basicswhat is materials requisitionytm in exceluses of bank reconciliation statementdefine contraswhat does prepaid freight meanissuance of common stockwithholding tax entrychain discount calculatorasset turnover ratio calculationdso calculation exampleimprest amount meaninggross profit method of estimating inventoryretained earning debit or creditaccounting credits and debitsdepreciation schedule templatestraight line depreciation method calculatorinventory sold journal entrydegree of total leverage calculatorpastel accounting tutorialssample payroll journal entrycontribution margin and fixed costsprepaid expenses examplecalculate markup formulacost volume profit graph excel templateaccounts payable assessment testdeclared dividends journal entryjournal entry for collection of accounts receivablecalculate fixed asset turnoverprinciples of accounting conceptsaccounting template exceljournal entry format accounting examplevariable costing definition200 declining balance depreciationfifo method accountingaccounting entry for provision for doubtful debtsfvf tableflow chart of accounting cycleexamples current liabilitiesretail markup calculator ukpayback period formula exceldeferred tax liability formulacalculate paybackjournalizing notes payablesundry definition accountingprepaid rent balance sheethow to record accounts payable journal entriesamortised definitionhow to compute net sales in accountingdiscounting accounts receivablebook keeping equationsold treasury stock journal entrywages debit or creditcapital lease cash flow statementdouble declining balance tableexamples of prepaymentscreditors accounts payablewhat is an unadjusted trial balancebookkeeping defhow to find the cost of goods manufacturedannuity payout calculator excelfactory overheadsdebtors turnover ratio definitionhow to calculate pv of annuityexamples of accounts payablereducing balance method depreciation calculatordouble entry bookkeeping example questionsabsorption overheadreduced balance depreciationpayback method advantagescalculation of operating leveragecalculating operating leveragebank loan accounting entries