Net present value NPV Excel function
Written by:Abraham A.Learn to find NPV with MS Excel formula, download easy to use Excel template. Here we will show you how you can use Excel to find net present value with the NPV formula.
What is NPV
NPV by one definition refers to net present worth of an annuity and the NPV formula in MS Excel gives us this value. However, a point to note here is that, in capital budgeting, NPV is a discounted cash flow technique for evaluating capital investment proposals. NPV reflects the net monetary benefits if the investment is undertaken. It is computed by first summing up the discounted net cash flows and then subtracting initial expense from it. A positive NPV is preferred whereas we reject projects with negative NPV or one that has a NPV of zero.
NPV Formula for annuity in Excel
MS Excel provides a built in formula to find net present value of streams of receipts or payments, to use this formula you type in
=NPV(interest_rate,values).
Here the values are a range of cells such as A1:B10 that are annual or periodic annuity payments. The first argument in the formula is the the interest rate at which to discount the cash stream.
NPV Formula for capital budgeting in Excel
To find net present value to evaluate an investment proposal such a buying a new machine or extending a product line, we need to tweak the NPV formula in Excel to get the desired results. Since the NPV formula in Excel only finds the net present worth of cash flows, thus we need to subtract the initial cost of the project from the value given by the NPV formula in Excel.
NPV MS Excel Example
Newark Fire Department in New Jersey is planning an installation of Vending machine for its fire fighters. The machine will cost $13,000 and is expected to generate sales in amounts of $3100, $4100, $5100, and $6100 each of the next four years. Fire Department has a WACC of 11%. Compute NPV
In Cell A1, Type the text WACC. In Cell B1 enter the value 11%.
In Cell A2, Type the text Time, In Cell B2 type 0, in cell C2 type 1, in cell D2 type 2, in cell E2 type 3 and in cell F2 type 4.
In Cell A3, Type the text Cash Flows, In Cell B3 type -13000, in cell C3 type 3100, in cell D3 type 4100, in cell E3 type 5100 and in cell F3 type 6100.
In Cell A4, Type the text NPV. In Cell B4 enter the following function
=NPV(B1,C3:F3)+B3
MS Excel NPV Template
Download this MS Excel Template to find Net Present Value| A | B | C | D | E | F | |
| 1 | WACC | 11% | ||||
| 2 | Time | 0 | 1 | 2 | 3 | 4 |
| 3 | Cash Flow | -13000 | 3100 | 4100 | 5100 | 6100 |
| 4 | NPV | $867.78 |
Related Investment Analysis Excel Templates
Following is a list of related Excel templates that cover other 5 commonly used investment analysis methodsNPV Calculator
|
type in the authorization code in the box located below:
|
|
ResultsNPV = 31133.3
|
|
Input Datatype in the reinvestment rate (WACC) aka discount rate: %type in net cash flows in the space below: |
|
Instructions
- Enter the series of cash flows in the text box where each of the cash flows is separated by a space.
- Select the timings of the cash flows that may start of period payments/receipts for annuity due or end of period payments/receipts for ordinary annuity
- Enter the discount rate different at which to discount the cash flows
