A cost benefit analysis template is a spreadsheet that lists every cost and benefit of a decision, puts a money value on each and compares the totals. For a software purchase, it shows whether the tool pays for itself, and how quickly.
It is a ready-made model for testing whether a purchase or project is worth the money. You enter what it will cost and what it will return, year by year, and the formulas convert both into today's money so the comparison is fair.
Budget owners build one when they ask finance to approve a new tool, a vendor switch or a headcount trade-off. Finance and procurement use the same layout to compare requests side by side, because every case shows the same three numbers: net present value, payback period and benefit-cost ratio.
For software, the hard part is honesty about costs. Licences are only one line; implementation, training, admin time and the old tool you keep paying during the switch often decide whether the case holds.
The decision being tested, the time horizon and the option you compare against, usually doing nothing.
One-off costs in year 0 and recurring costs in each later year, including internal time.
Savings and gains with a money value, such as hours saved, tools retired and errors avoided.
The return your company expects on its money, used to turn future amounts into today's value.
Net present value, benefit-cost ratio, payback period and ROI, calculated by formula.
The same metrics with lower benefits or higher costs, to see how fragile the case is.
Ready to use in Excel and Google Sheets. Fill it in, save it, reuse it.
State the decision, the alternative and the time horizon. Three years suits most software purchases.
Write down every cost and benefit, including internal time and the tool you would retire.
Put a money figure on each line, by year. Note the source of every number in a comment.
Discount future years to today's value and calculate NPV, benefit-cost ratio and payback.
Cut benefits and raise costs to see if the case survives, then recommend go, no-go or renegotiate.
The simplest version is one year, two columns: total costs and total benefits. If benefits are higher, the purchase pays back within the year; use the full grid for anything longer.
The example below is Lumen Retail weighing a new customer support platform against keeping its current tool. Year 0 holds one-off costs; years 1-3 hold recurring costs and benefits. Benefits ramp up in year 1 because adoption takes time, which is the most common thing teams forget.
| Line item | Year 0 | Year 1 | Year 2 | Year 3 | Total |
|---|---|---|---|---|---|
| Implementation and migration | 45,000 | 0 | 0 | 0 | 45,000 |
| Training | 15,000 | 0 | 0 | 0 | 15,000 |
| Licences (40 seats) | 0 | 44,000 | 44,000 | 44,000 | 132,000 |
| Admin time | 0 | 10,000 | 10,000 | 10,000 | 30,000 |
| Total costs | 60,000 | 54,000 | 54,000 | 54,000 | 222,000 |
| Agent time saved | 0 | 54,000 | 80,000 | 80,000 | 214,000 |
| Legacy tool retired | 0 | 24,000 | 24,000 | 24,000 | 72,000 |
| Fewer SLA credits | 0 | 12,000 | 26,000 | 26,000 | 64,000 |
| Total benefits | 0 | 90,000 | 130,000 | 130,000 | 350,000 |
| Net benefit | -60,000 | 36,000 | 76,000 | 76,000 | 128,000 |
Illustrative figures for a fictional company.
Works as an Excel cost benefit analysis template or in Google Sheets. Discount rate in B2, years 0-3 in B4:E4, costs in rows 5-8, benefits in rows 11-13.
| Col | Header | Entry or formula | What it does |
|---|---|---|---|
| B2 | Discount rate | 10% | Your company's hurdle rate; ask finance for it |
| B4:E4 | Year | 0, 1, 2, 3 | Year 0 is the purchase year |
| F5 | Line total | =SUM(B5:E5) | Copy down for every cost and benefit row |
| B9 | Total costs | =SUM(B5:B8) | Copy across to E9 |
| B14 | Total benefits | =SUM(B11:B13) | Copy across to E14 |
| B15 | Net benefit | =B14-B9 | Copy across to E15 |
| B16 | Discount factor | =1/(1+$B$2)^B4 | Copy across; year 0 is always 1 |
| B17 | Present value of net benefit | =B15*B16 | Copy across to E17 |
| B18 | Cumulative net benefit | =B15 | Starts the running total |
| C18 | Cumulative net benefit | =B18+C15 | Copy across to E18; turns positive at payback |
Put these formulas on a summary block under the grid, in B20 to B26. They read only from the rows above, so changing any cost or benefit updates the decision straight away. ROI and IRR are optional, but finance teams often ask for them.
| Metric | Formula |
|---|---|
| B20: Net present value (NPV) | =NPV(B2, |
| B21: Present value of benefits | =SUMPRODUCT(B14:E14, |
| B22: Present value of costs | =SUMPRODUCT(B9:E9, |
| B23: Benefit-cost ratio | =B21/B22 |
| B24: ROI, undiscounted | =(SUM(B14:E14)-SUM(B9:E9))/SUM(B9:E9) |
| B25: Internal rate of return | =IRR(B15:E15) |
| B26: Payback period in years | =IFERROR(COUNTIF(C18:E18, |
Excel's NPV function discounts its first value by one year, so year 0 (B15) is added outside it. The payback formula assumes the running total stays positive once it crosses zero.
| Metric | Lumen Retail result | Passes when |
|---|---|---|
| Net present value | 92,637 | Above 0 |
| Present value of benefits | 286,927 | Higher than costs |
| Present value of costs | 194,290 | Lower than benefits |
| Benefit-cost ratio | 1.48 | Above 1.0 |
| ROI, undiscounted | 57.7% | Above your hurdle |
| Internal rate of return | 74.3% | Above the discount rate |
| Payback period | 1.32 years (about 16 months) | Inside your limit, often 12-24 months for software |
Illustrative results at a 10% discount rate.
Most weak cases overstate benefits and miss half the costs. Use this list as a prompt when you fill the grid, and ask the vendor and your own team for each figure separately. Read more on SaaS TCO and ROI.
| Line | Type | How to value it |
|---|---|---|
| Licences or subscription | Cost | Seats times unit price, plus the uplift at renewal |
| Implementation and migration | Cost | Vendor or partner quote, plus internal project hours times loaded rate |
| Training | Cost | Hours per user times users times loaded hourly rate |
| Admin and integration upkeep | Cost | Hours per month to run the tool, times rate |
| Overlap with the old tool | Cost | Months both tools run together, times the old monthly fee |
| Time saved | Benefit | Hours saved per person per week, times people, weeks and rate |
| Tools retired | Benefit | Annual cost of each tool you will cancel |
| Errors or penalties avoided | Benefit | Last year's credits, refunds or rework, times the expected reduction |
| Better experience or lower risk | Intangible | Describe it in words; do not add it to the totals |
Copy the grid to a second tab and change one assumption at a time. Approvers trust a case more when it shows the downside, and the test tells you which number to firm up before you ask for sign-off.
| Scenario | NPV | Benefit-cost ratio | Payback |
|---|---|---|---|
| Base case | 92,637 | 1.48 | 1.32 years |
| Benefits 25% lower | 20,905 | 1.11 | 2.07 years |
| Costs 20% higher | 53,779 | 1.23 | 1.72 years |
| Adoption six months late | 18,670 | 1.10 | 2.43 years |
Illustrative results for the Lumen Retail example at a 10% discount rate. The delay case cuts year 1 benefits to 45,000 and year 2 to 90,000.
Usually time saved or adoption speed, since the vendor supplied it.
Lower that input until NPV hits zero; that is your margin of safety.
Show base and worst case side by side in the approval request.
Many requests do not need a three-year model: a design tool for five people or a scheduling app for one team. A simple cost benefit analysis template in Excel answers the only question that matters at that size: does it pay for itself this year?
Illustrative figures. Benefit-cost ratio 1.80; payback about 6.7 months.
| Measure | Formula |
|---|---|
| Net benefit | =B6-B3 |
| Benefit-cost ratio | =B6/B3 |
| Payback in months | =B3/(B6/12) |
Costs in B1:B2 with the total in B3; benefits in B4:B5 with the total in B6.
Spendflo pricing benchmarks show what similar companies pay, so your licence costs are real.
See pricing benchmarksThe yearly return your company expects on its money. Finance usually sets it.
Benefits minus costs, all converted to present value. Above zero means the purchase adds value.
Present value of benefits divided by present value of costs. Above 1 means benefits win.
How long until cumulative benefits cover cumulative costs, ignoring discounting.
The discount rate at which NPV is exactly zero. Compare it with your hurdle rate.
Every cost of a tool over its life, from purchase and set-up to running and exit.
The base option is keeping today's tool or process, with its own costs.
Implementation and admin hours are real costs even when no invoice arrives.
Year 1 rarely delivers the full gain, so phase benefits in as adoption grows.
Add a cell comment naming where each number came from and who confirmed it.
One agreed rate lets finance compare your case with every other request.
Use them as a prompt, then rebuild the benefit from your own volumes.
Saved hours only become savings if they cut cost or free capacity you will use.
Year 2 and 3 licence prices are often higher than year 1.
Describe them in words so the numbers stay defensible.
Collect the vendor quote, implementation estimate and what you spend today on the process or tool.
Enter one-off costs in year 0 and recurring costs and benefits in years 1-3.
Check NPV, benefit-cost ratio and payback against your company's thresholds.
Run the three what-if cases and attach the workbook to the purchase request.
Lumen Retail's base case gives an NPV of 92,637 and payback in 16 months. If adoption runs six months late, NPV falls to 18,670 and payback stretches to 2.43 years. Finance approves on condition that licences start at 20 seats in year 1, which cuts year 1 cost by 22,000. Figures are illustrative.
Every part on this page, in Excel and Google Sheets, with the examples filled in.
Use the simple tab. If payback is under twelve months and the tool replaces something, the case is usually clear.
Use the three-year grid and the metrics block. Show one downside case and attach it to the purchase request.
Add a five-year horizon, exit costs and at least three sensitivity cases. Compare two vendors on separate tabs with the same rows.
Best for the full model and offline review.
Best when finance comments on the inputs.
One year, costs against benefits.
$3.7B in software spend processed through Spendflo, at 30% average savings.
See your savingsA good cost benefit analysis counts every cost, values benefits honestly and shows what happens when assumptions slip. The approval that follows still needs a clear request, the right approvers and a price you have tested.
Quick answers to what people ask most about the cost benefit analysis template.
Define the decision and time horizon, list every cost and benefit by year, put a money value on each and discount future years to today's value. Then compare NPV, benefit-cost ratio and payback, test the downside and make a recommendation, or download the template to follow the same structure.
Define the decision, list the costs and benefits, value them in money, compare them with NPV and the benefit-cost ratio, and test sensitivity before recommending. The download has a section for each step.
Yes: you can download a free cost benefit analysis Excel template on this page, with NPV, payback, benefit-cost ratio, ROI and IRR formulas built in. It also opens in Google Sheets and includes a simple one-tab version.
A retailer weighing a new support platform: 222,000 in costs over three years against 350,000 in benefits, giving an NPV of 92,637 and payback in about 16 months at a 10% discount rate. The figures are illustrative, and the full example is in the download.
You can download it free on this page as an Excel workbook or a Google Sheets copy. It includes the three-year grid, decision metrics, sensitivity test and a simple version for small purchases.
Budgets and business cases
Purchase orders
Contracts
Vendor management
Sourcing and RFx
Procurement
Accounts payable
Purchasing
Software buying
Supply chain
Spendflo handles intake, approvals and pricing benchmarks for software purchases, so the cost lines in your analysis reflect what the market pays.
Enter your work email and we'll unlock every format.
Didn't start, or need another format? Pick one below.
Google Sheets: upload the file to Google Drive, then open it with Google Sheets.
