Why  MS-Excel's^® Net Present Value
Function Is Incorrect 

The Net Present Value (NPV) measurement of a cashflow series is widely used in business, and may be even more commonly used to gauge the financial acceptability of a new project or venture than is the Internal Rate of Return (IRR).  

Unlike the IRR calculation,  which forces a discount rate, the calculation of the NPV  requires the analyst to provide a discount rate. This discount rate can be either a "hurdle rate" or the "opportunity cost" of funds.   The Present Value concept  uses the discount rate selected to convert all future cashflows backward in time to an equivalent amount of cash today. This PV is the summation of all the individual PVs for each future cashflow.
 

The NPV function goes one step further and subtracts from the summarized PV the amount of the initial investment.  

The result is the Net Present Value of a cashflow series. 


Why Businesses Like the NPV

The financial test applied by the use of the NPV determines whether the  Present Value of all future cashflows, discounted at the selected rate, is equal to, less than or greater than, the amount of the cash to be initially invested.  

If the NPV is a negative number, it shows that the Present Value of all future cashflows is less than the amount to be initially invested, and that the IRR (yield) of the project will be less than the selected discount rate. These are projects likely to be rejected. 

If the NPV is positive, it shows the Present Value of all future cashflows is greater than the amount to be initially invested, and that the IRR of the project will exceed the selected discount rate. 

If the NPV is zero, it shows that the IRR for the project is exactly equal to the selected discount rate. 
 

How the HP-12C Handles Calculations for the NPV 

On the HP-12C, the amount of the initial investment is stored in CFo (Storage Register 0) as a negative number. After the HP-12C calculates the PV of the series, it adds to the PV the amount stored in Storage Register 0.  

Since the amount stored in Register 0 is a negative, the effect is to subtract from the PV the value stored in Register 0.
In other words, the PV of the series is netted with the negative amount stored in Register 0. Hence, Net Present Value. If no value is stored in Register 0, the NPV key on the HP-12C delivers the PV of the cashflow.
 

The NPV Function as Found on MS-Excel 

The NPV function described by MS-Excel is analogous to the uneven cashflow methodology  (CFo, CFj, Nj,) of the HP-12C calculator: it computes the Present Value of a cashflow consisting of  unequal PMTs.  

If you are dealing with a series which consists of uneven (unequal in amount or sign) cashflows, and wish to compute the NPV of the series using MS- Excel, you must subtract from the result of the NPV function the amount of the initial investment. The amount of the initial investment should not be included in the cashflow series.

For example:   = NPV( rate, Cf₁,Cf₂,Cf₃,Cf₄....Cf_n)   – Initial Investment. 
 

When the Cashflows Occur at the Beginning Of the Period (BOP)

Notice that Excel's NPV function provides no means to change the timing of the cashflows between BOP and EOP. The timing of the function is set at EOP. If the cashflows in the series occur at the beginning of the period (BOP), the value of the first cashflow must be extracted from the cashflow series and added to the result of the NPV function. On Excel, for example the following formula yields the correct NPV for a cashflow whose first payment occurs at the beginning of the period:  

"= NPV( rate,  Cf₂, Cf₃, Cf₄....Cf_n)  -   Initial Investment  +  Cf₁" 

This error affecting the proper use of the NPV function is frequently repeated in many books and manuals devoted to MS-Excel usage.

Note: If you use the NPV function (pulldown menu) on Excel, you will find that you are limited to 29  distinct cashflows. If your cashflow series involves more than 29 cashflows, including cashflows that repeat, this function will not work for you.

Instead, construct the cashflow on a regular spreadsheet and use the formula provided above to determine the NPV. Remember to subtract the initial (negative) cashflow.