Net Present Value (NPV)
Calculate the current dollar value of future cash flows, discounted at your required rate of return.
=NPV(discount_rate, cash_flows) + initial_investment How It Works
NPV answers: “What is this investment worth in today’s dollars?” It discounts all future cash flows back to present value using your required rate of return. Positive NPV means the investment creates value; negative means it destroys value.
Syntax
=NPV(rate, value1, [value2], ...) + initial_investment- rate: Discount rate (your required return)
- values: Future cash flows (years 1, 2, 3, etc.)
- initial_investment: Year 0 outlay (added separately, as negative)
Important: NPV function assumes first cash flow is Year 1. Add Year 0 separately.
Example
Business Investment Analysis:
- Initial Cost: $100,000 (Year 0)
- Required Return: 10%
| Year | Cash Flow |
|---|---|
| 1 | $25,000 |
| 2 | $35,000 |
| 3 | $40,000 |
| 4 | $45,000 |
| 5 | $50,000 |
Formula:
=NPV(10%, B2:B6) + B1
=NPV(10%, 25000, 35000, 40000, 45000, 50000) - 100000Result: $46,789
This investment creates $46,789 in value at your 10% required return.
The Decision Rule
| NPV | Decision |
|---|---|
| Positive | Accept - creates value |
| Zero | Indifferent - earns exactly required return |
| Negative | Reject - destroys value |
Understanding the Math
Each cash flow is discounted by how far in the future it is:
| Year | Cash Flow | Discount Factor | Present Value |
|---|---|---|---|
| 0 | -$100,000 | 1.000 | -$100,000 |
| 1 | $25,000 | 0.909 | $22,727 |
| 2 | $35,000 | 0.826 | $28,926 |
| 3 | $40,000 | 0.751 | $30,053 |
| 4 | $45,000 | 0.683 | $30,735 |
| 5 | $50,000 | 0.621 | $31,046 |
| Total | $43,487 |
Discount Factor = 1 / (1 + rate)^year
Comparing Investments
Use NPV to compare different-sized investments:
| Project | Initial | NPV @10% | IRR |
|---|---|---|---|
| Project A | -$50,000 | $23,500 | 18% |
| Project B | -$100,000 | $35,000 | 15% |
IRR says A is better (18% > 15%) NPV says B is better ($35K > $23.5K)
Which is right? Depends on your goal:
- NPV for maximum dollar value created
- IRR for maximum percentage return
Rental Property Analysis
| Year | Cash Flow | Notes |
|---|---|---|
| 0 | -$75,000 | Down payment + closing |
| 1-9 | $6,000/yr | Net rental income |
| 10 | $126,000 | Rental income + sale |
NPV at 8%:
=NPV(8%, B2:B11) + B1If NPV > 0, this property beats your 8% required return.
Sensitivity Analysis
How does NPV change with different discount rates?
| Discount Rate | NPV |
|---|---|
| 5% | $72,234 |
| 8% | $53,897 |
| 10% | $43,487 |
| 12% | $34,219 |
| 15% | $21,456 |
| 18% | $10,234 |
Note: NPV = 0 when discount rate = IRR
Building an NPV Calculator
| Input | Value |
|---|---|
| Initial Investment | -100000 |
| Discount Rate | 10% |
| Year 1 Cash Flow | 25000 |
| Year 2 Cash Flow | 35000 |
| Year 3 Cash Flow | 40000 |
| Year 4 Cash Flow | 45000 |
| Year 5 Cash Flow | 50000 |
| Output | Formula |
|---|---|
| NPV | =NPV(B2, B3:B7) + B1 |
| Decision | =IF(B9>0, "Accept", "Reject") |
NPV Profile Chart
Plot NPV against different discount rates:
| Rate | NPV |
|---|---|
| 0% | =NPV(A2, flows) + initial |
| 5% | =NPV(A3, flows) + initial |
| 10% | =NPV(A4, flows) + initial |
| … | … |
Where the line crosses zero is the IRR.
Adjusting for Risk
Higher risk investments deserve higher discount rates:
| Investment Type | Typical Discount Rate |
|---|---|
| Government bonds | 3-5% |
| Corporate bonds | 5-8% |
| Established business | 10-15% |
| Startup/high risk | 20-30%+ |
NPV vs. Other Metrics
| Metric | Measures | Limitation |
|---|---|---|
| NPV | Dollar value created | Requires choosing discount rate |
| IRR | Percentage return | Can have multiple solutions |
| Payback | Time to break even | Ignores time value of money |
| ROI | Simple return % | Ignores timing |
Pro Tips
-
Discount rate matters - small changes in rate significantly affect NPV
-
Risk-adjust the rate - riskier projects need higher discount rates
-
Consider opportunity cost - your discount rate should be at least what you could earn elsewhere
-
Terminal value - for ongoing businesses, include a terminal value for cash flows beyond your projection
-
Real vs. nominal - if cash flows include inflation, use nominal discount rate; otherwise use real rate
Common Errors
- Forgetting Year 0: NPV function assumes Year 1 start - add initial investment separately
- Wrong timing: Cash flows must be end-of-period (or adjust formula)
- Mixing real and nominal: Be consistent with inflation assumptions
- Too optimistic projections: Garbage in, garbage out - NPV is only as good as your cash flow estimates