How to Do a Cost-Benefit Analysis in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
List every cost and benefit in rows, with one column per year. Total them with SUM, then calculate the net benefit (benefits minus costs) and the benefit-cost ratio (benefits divided by costs). A ratio above 1 means benefits exceed costs. For projects over several years, add a cumulative row for the payback period and use NPV to discount future amounts.
Key takeaways
- Lay out costs and benefits as rows and years as columns, so every number has one clear place.
- Net benefit is total benefits minus total costs. The benefit-cost ratio is total benefits divided by total costs.
- A cumulative net row shows the payback period: the year it turns from negative to zero or positive.
- Use NPV to discount future years, because money later is worth less than money now.
- The result is only as good as your estimates, so test what happens when they change.
On this page+
- What is a cost-benefit analysis?
- Step 1: Set up the cost-benefit table
- Step 2: Total the costs and benefits with SUM
- Step 3: Calculate net benefit and the benefit-cost ratio
- Step 4: Find the payback period
- Step 5: Discount future years with NPV
- Step 6: Test your assumptions
- Common mistakes in a cost-benefit analysis
- Practise it
A cost benefit analysis in Excel compares what a project will cost with what it is expected to bring in, so you can see whether the benefits outweigh the costs. You list both sides by year, total them with SUM, and work out a few simple measures: net benefit, benefit-cost ratio, payback period and, for longer projects, net present value. This guide builds one step by step on a small example you can type in. The figures are examples only, not advice about any real decision.
What is a cost-benefit analysis?#
A cost-benefit analysis (CBA) puts the costs and the benefits of a decision in money terms and compares them. It answers two questions: do the benefits beat the costs, and by how much?
The main measures are:
| Measure | Formula | What it tells you |
|---|---|---|
| Net benefit | Total benefits − total costs | How much more you gain than you spend |
| Benefit-cost ratio (BCR) | Total benefits ÷ total costs | How many units of benefit per unit of cost |
| Payback period | Year when cumulative net turns positive | How long until the costs are covered |
| Net present value (NPV) | Discounted benefits − discounted costs | The net benefit in today's money |
A BCR above 1 means estimated benefits are larger than estimated costs. Below 1, they are smaller.
Step 1: Set up the cost-benefit table#
Put each cost and benefit on its own row and each year in its own column. Year 0 is "now", when one-off costs usually happen. This layout keeps every number in one obvious place.
The example is a team choosing new scheduling software. All figures are made up for this guide:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Year 0 | Year 1 | Year 2 | Year 3 |
| 2 | Setup and training | 15,000 | 0 | 0 | 0 |
| 3 | Subscription | 0 | 4,000 | 4,000 | 4,000 |
| 4 | Staff time saved | 0 | 9,000 | 9,000 | 9,000 |
| 5 | Fewer errors | 0 | 2,500 | 2,500 | 2,500 |
Rows 2–3 are costs and rows 4–5 are benefits. Type 0 rather than leaving blanks, so it's clear the cell was considered. In a bigger model, colour the input cells so readers know which numbers are estimates. The department budget practice lesson uses the same habit of clear inputs and totals.
Step 2: Total the costs and benefits with SUM#
SUM can add a whole block of cells at once, not just one column. That keeps the totals short:
=SUM(B2:E3)
=SUM(B4:E5)Total costs are 27,000 (15,000 + 3 × 4,000). Total benefits are 34,500 (3 × 11,500). If SUM is new to you, the SUM function guide shows every way to write it, and the SUM and AutoSum lesson lets you practise.
Step 3: Calculate net benefit and the benefit-cost ratio#
Net benefit subtracts costs from benefits, and the ratio divides them:
=SUM(B4:E5)-SUM(B2:E3)
=ROUND(SUM(B4:E5)/SUM(B2:E3),2)The net benefit is 7,500. The benefit-cost ratio is 34,500 ÷ 27,000 = 1.28, so in this example every 1 spent is expected to bring back about 1.28. ROUND keeps the result to two decimals without changing the maths elsewhere.
Formatting the ratio as a number (not a percentage) avoids confusion. If you prefer a percentage, the ratio 1.28 reads as 128%.
Step 4: Find the payback period#
The payback period is how long it takes for benefits to cover costs. Add two rows under the table:
- Row 6, Net cash flow. In B6:
=SUM(B4:B5)-SUM(B2:B3), then fill right to E6. You get −15,000, 7,500, 7,500, 7,500. - Row 7, Cumulative. In B7:
=B6. In C7:=B7+C6, then fill right. You get −15,000, −7,500, 0, 7,500.
The cumulative row reaches 0 in Year 2, so the payback period is 2 years. To fill right, select the cells and press Ctrl+R (Mac: ⌘R).
This idea is close to a break-even point. The guide to break-even analysis in Excel works out how many units you need to sell to cover costs, and the cash flow forecast guide builds a running balance the same way.
Step 5: Discount future years with NPV#
Money received in three years is worth less than money today, because today's money could earn a return in the meantime. NPV (net present value) shrinks each future amount by a discount rate and adds them up.
Excel's NPV function assumes the first value comes at the end of year 1, so add Year 0 separately:
=NPV(8%,C6:E6)+B6At an example rate of 8%, the result is about 4,328. It is lower than the plain net benefit of 7,500 because the benefits arrive later than the setup cost. The discount rate is an assumption: organisations choose their own. The NPV function guide explains the timing rule in more detail.
You can also discount each side and compare them. Here the discounted ratio is about 29,637 ÷ 25,308 = 1.17, lower than the simple 1.28.
Step 6: Test your assumptions#
Every number in a CBA is an estimate, so check how much the answer depends on them. Change one input at a time and watch the results:
- What if staff time saved is 6,000 a year, not 9,000?
- What if setup costs run 20% over?
- What if the discount rate is 10%?
Keep the original figures on a separate tab so you can always go back. Once the project runs, compare real numbers with your plan in a budget vs actual report.
Common mistakes in a cost-benefit analysis#
Watch out for these:
- Counting a benefit twice. "Time saved" and "lower overtime" may be the same money.
- Mixing one-off and yearly amounts. Put each in the year it happens.
- Forgetting running costs. Subscriptions, maintenance and support often last for years.
- Putting Year 0 inside NPV.
=NPV(8%,B6:E6)discounts the setup cost too, which makes the project look better than it is. - Hiding guesses in totals. List hard-to-measure benefits in a notes section instead.
For a project with many cost lines, a project budget in Excel is a good companion sheet that feeds the cost rows.
Practise it#
Try the exercise on this page to calculate the benefit-cost ratio yourself. Then practise SUM in a live spreadsheet and work through the department budget practice, which combines SUM, AVERAGE, ROUND and absolute references on a realistic sheet. Each lesson checks your formulas instantly.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Example figures for a software project are in B2:E5: costs in rows 2–3 and benefits in rows 4–5. In H2, calculate the benefit-cost ratio, rounded to 2 decimal places, and press Enter.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Year 3 | ||||||||
| 2 | Benefit-cost ratio | ||||||||
| 3 | |||||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
Tip: Divide the total benefits, SUM(B4:E5), by the total costs, SUM(B2:E3), then wrap it in ROUND(...,2).
Practise this in a lesson
Frequently asked questions
What is the formula for a cost-benefit analysis?
The two core formulas are net benefit = total benefits - total costs, and benefit-cost ratio = total benefits / total costs. In Excel that is =SUM(benefits)-SUM(costs) and =SUM(benefits)/SUM(costs).
What is a good benefit-cost ratio?
A ratio above 1 means the estimated benefits are larger than the estimated costs, and below 1 means they are smaller. How high a ratio an organisation wants depends on its own rules and the risk of the project.
Should a cost-benefit analysis use discounted values?
For projects that last more than a year, it usually should, because money received later is worth less than money today. Excel's NPV function discounts each year's amount at a rate you choose.
How do I show intangible benefits in a cost-benefit analysis?
Put a money value on them only if you have a reasonable basis. Otherwise list them in a separate notes section next to the numbers, so readers see them without the totals being inflated by guesses.