Free finance template
NPV and IRR business case
An investment and its yearly savings: NPV, IRR, a decision and the payback year.
It opens with this template loaded and runs in your browser. You don't need an account to use it; a free account saves your work and exports it.
The job it does
When you ask for money for a project: the business case finance will ask for. It comes with its own checks, so after you change it you can see at a glance that it still does the job.
What's inside
This is the template exactly as the Finance Lab opens it, read-only. Open it to run it, change it and export it.
- 1 sheet
- 16 formulas
- Exports to Excel, CSV and PDF
NPV discounts each year's cash flow back to today. NPV(rate, C7:H7) treats the first flow as one year away, so year 0 (the investment) is added outside it. IRR is the rate where NPV is zero: invest when it beats your discount rate.
- No formula errors anywhere
- B11 is a formula, not a typed number
- B12 is a formula, not a typed number
- B13 is a formula, not a typed number
| Number | Value |
|---|---|
| NPV | -$3,035 |
| IRR | 9.4% |
| Decision | do not invest |
- Discount rate: 10% (from 0.02 to 0.25)
- Power saved in year 1: $38,000 (from 10000 to 80000)
- Power price growth: 3% (from 0 to 0.1)
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Solar roof on the North distribution centre | |||||||
| 2 | Discount rate | 10% | ||||||
| 4 | Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| 5 | Investment | -$180,000 | ||||||
| 6 | Power saved | $38,000 | $39,140 (formula =C6*(1+$B$9)) | $40,314 (formula =D6*(1+$B$9)) | $41,524 (formula =E6*(1+$B$9)) | $42,769 (formula =F6*(1+$B$9)) | $44,052 (formula =G6*(1+$B$9)) | |
| 7 | Cash flow | -$180,000 (formula =B5+B6) | $38,000 (formula =C5+C6) | $39,140 (formula =D5+D6) | $40,314 (formula =E5+E6) | $41,524 (formula =F5+F6) | $42,769 (formula =G5+G6) | $44,052 (formula =H5+H6) |
| 9 | Power price growth | 3% | ||||||
| 11 | NPV | -$3,035 (formula =B7+NPV(B2,C7:H7)) | ||||||
| 12 | IRR | 9.4% (formula =IRR(B7:H7)) | ||||||
| 13 | Decision | do not invest (formula =IF(B11>0,"invest","do not invest")) | ||||||
| 14 | Payback year | 5 (formula =IF(SUM(C7:H7)<-B7,"after year 6",IF(SUM(C7:C7)>=-B7,1,IF(SUM(C7:D7)>=-B7,2,IF(SUM(C7:E7)>=-B7,3,IF(SUM(C7:F7)>=-B7,4,IF(SUM(C7:G7)>=-B7,5,6))))))) |
The lesson behind it
Learn why it works, with a podcast and a quiz for each lesson.
More finance templates
Cloud cost model
A price list and a monthly bill on two sheets, with reserved discounts, storage tiers and a CDN, and the cost per order.
Pricing calculator
A price from cost and target margin, rounded to .99, with demand that falls as the price rises.
Simple DCF
Five years of free cash flow, a terminal value and the enterprise value.