Cost-benefit analysis over several years
Lay out what a project costs and what it brings in, year by year, and see whether it pays off once future money is discounted to today's value.
Educational use only, not financial advice. The example figures are made up; replace them with your own estimates.
| Item | Type | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Remove |
|---|---|---|---|---|---|---|---|---|---|---|---|
=Result
- Net present value
- $1,519benefits outweigh costs, at 3.5% a year
- Benefit-cost ratio
- 1.22each 1 spent returns more than 1
- Discounted payback
- Year 7cumulative net turns positive
- Internal rate of return
- 8.99%the rate at which NPV is zero
Undiscounted: costs $7,200, benefits $10,000. Present value: costs $7,031, benefits $8,550.
| Year | Costs | Benefits | Net | Discount factor | Discounted net | Cumulative |
|---|---|---|---|---|---|---|
| 0 | $6,000 | $0 | −$6,000 | 1.0000 | −$6,000 | −$6,000 |
| 1 | $150 | $900 | $750 | 0.9662 | $724.64 | −$5,275 |
| 2 | $150 | $1,300 | $1,150 | 0.9335 | $1,074 | −$4,202 |
| 3 | $150 | $1,300 | $1,150 | 0.9019 | $1,037 | −$3,165 |
| 4 | $150 | $1,300 | $1,150 | 0.8714 | $1,002 | −$2,162 |
| 5 | $150 | $1,300 | $1,150 | 0.8420 | $968.27 | −$1,194 |
| 6 | $150 | $1,300 | $1,150 | 0.8135 | $935.53 | −$258.64 |
| 7 | $150 | $1,300 | $1,150 | 0.7860 | $903.89 | $645.25 |
| 8 | $150 | $1,300 | $1,150 | 0.7594 | $873.32 | $1,519 |
Your table is saved in this browser only.
Costs and benefits in today's money
Money arriving later is worth less than money now, because today's money could be earning something in the meantime. A discount rate r turns an amount in year t into its present value, amount ÷ (1 + r)t. Year 0 is now and is not discounted.
NPV = Σ (benefits − costs) ÷ (1 + r)t; BCR = PV of benefits ÷ PV of costs
A positive net present value, or a benefit-cost ratio above 1, means the benefits outweigh the costs at the rate you chose. The discounted payback year is when the running total turns positive and stays there, and the internal rate of return is the discount rate at which NPV is exactly zero.
In the example, a 6,000 machine plus servicing is set against labour saved and extra orders. At 3.5% the NPV is about 1,519 and the running total turns positive in year 7. The UK Treasury's Green Book uses 3.5% for public projects; for a personal or business choice, the rate you could earn elsewhere is a common yardstick.
Sources
- HM Treasury. The Green Book: Central Government Guidance on Appraisal and Evaluation: discounting and the 3.5% social time preference rate.
- Net present value and cost–benefit analysis: the formulas for NPV, benefit-cost ratio and internal rate of return.
Questions
Should I include money already spent?
No. Spent money is a sunk cost: it is gone whichever way you decide. Include only future costs and benefits that depend on the decision.
Why does the IRR show a dash?
A single IRR exists when the yearly net figures switch once, from net cost to net benefit. With no switch, or several, there is no single rate to report.
Can I use a negative discount rate?
Yes, down to −50%, though it is unusual: it makes future amounts worth more than today's.