Excel: Npv Function
In Excel, the Npv function returns the net present value of an investment.
The syntax for the Npv function is:
Npv( discount_rate, value1, value2, ... value_n )
discount_rate is the discount rate for the period.
value1, value2, ... value_n are the future payments and income for the investment (ie: cash flows). There can be up to 29 values entered.
Note:
Microsoft Excel's Npv function does not account for the intial cash outlay, or may account for it improperly depending on the version of Excel. However, there is a workaround.
This workaround requires that you NOT include the initial investment in the future payments/income for the investment (ie: value1, value2, ... value_n), but instead, you need to subtract from the result of the Npv function, the amount of the initial investment.
The workaround formula is also different depending on whether the cash flows occur at the end of the period (EOP) or at the beginning of the period (BOP).
If the cash flows occur at the end of the period (EOP), you would use the following formula:
=Npv( discount_rate, value1, value2, ... value_n ) - Initial Investment
If the cash flows occur at the beginning of the period (BOP), you would use the following formula:
=Npv( discount_rate, value2, ... value_n ) - Initial Investment + value1
Acknowledgements: A special thanks to Martin P. for bringing this to our attention.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at a few examples:
This first example returns a net present value of $3,457.19. It assumes that you pay $7,500 as an initial investment . You then receive the following income for the first four years (EOP): $3,000, $5,000, $1,200, and $4,000. An annual discount rate of 8% is used.
=Npv(8%, 3000, 5000, 1200, 4000) - 7500
This next example returns a net present value of $8,660.77. It assumes that you pay $10,000 as an initial investment. You then receive the following income for the first three years (BOP): $3,400, $6,500, and $10,000. An annual discount rate of 5% is used.
=Npv(5%, 6500, 10000) - 10000 + 3400
VBA Code
The Npv function can also be used in VBA code as demonstrated by the following example:
This example returns a net present value of $3,457.19. It assumes that you pay $7,500 as an initial investment . You then receive the following income for the first four years (EOP): $3,000, $5,000, $1,200, and $4,000. An annual discount rate of 8% is used.
The VBA code would be:
Dim LNumber As Double
Static Values(4) As DoubleValues(0) = 3000
Values(1) = 5000
Values(2) = 1200
Values(3) = 4000LNumber = Npv(0.08, Values()) - 7500
In this example, the variable called LNumber would now contain the value of $3,457.19.