Template · Sales engineering
Total cost of ownership (TCO) comparison for three options
Five-year cost by year, NPV, cost per user per month and rank for three options, with escalation and risk.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Total cost of ownership comparison | |||
| Example data — replace the blue input cells with your own. | |||
| LOWEST-COST OPTION | LOWEST TOTAL COST | SAVING VS NEXT OPTION | |
| Hybrid | $1,946,450 | $117,002 | |
| Analysis years: 5. Users: 240. | |||
| Total cost by year (USD) | |||
| Year | On-premises | Cloud subscription | Hybrid |
| 0 | $470,000 | $100,000 | $265,000 |
| 1 | $323,000 | $367,000 | $312,000 |
| 2 | $332,690 | $378,010 | $321,360 |
| 3 | $342,671 | $389,350 | $331,001 |
| 4 | $352,951 | $401,031 | $340,931 |
| 5 | $423,539 | $428,062 | $376,159 |
| Total cost over the analysis period | $2,244,851 | $2,063,453 | $1,946,450 |
| NPV of costs at the discount rate | $1,874,008 | $1,659,078 | $1,598,764 |
Showing the first 16 of 24 rows and 4 of 4 columns. Cells with formulas show the formula on hover.
| Cost lines for each option | ||||
| Blue cells are inputs. Recurring lines escalate from year 2; acquisition and decommissioning do not. | ||||
| Cost line | Option A | Option B | Option C | How it is used |
| Option name | On-premises | Cloud subscription | Hybrid | Names appear on the Comparison tab |
| Acquisition (year 0, one time) | ||||
| Hardware | $240,000 | $0 | $90,000 | Servers, storage, network or sensors; zero for a subscription |
| Software licenses | $120,000 | $0 | $45,000 | Licenses bought up front |
| Implementation services | $85,000 | $60,000 | $95,000 | Vendor or partner deployment and configuration |
| Data migration | $25,000 | $40,000 | $35,000 | Moving historical data into the option |
| Acquisition subtotal | $470,000 | $100,000 | $265,000 | |
| Operating cost per year (year 1, escalated from year 2) | ||||
| Subscription and license fees | $0 | $264,000 | $96,000 | Annual fees for a subscription or term license |
| Maintenance and support | $36,000 | $0 | $22,000 | Vendor support for systems the utility owns |
Showing the first 16 of 37 rows and 5 of 5 columns. Cells with formulas show the formula on hover.
| Total cost of ownership comparison |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Compares the total cost of three ways to meet the same need, such as an on-premises system, a cloud subscription and a hybrid setup. It totals acquisition, operating, downtime and end-of-life costs for each option over the analysis period… |
| How to use it |
| 1. On the Costs tab, name the three options in row 5. |
| 2. Enter the acquisition lines (hardware, licenses, implementation and data migration) for each option. |
| 3. Enter the operating lines per year (fees, maintenance, hosting, FTEs, training) and the expected downtime hours and cost per hour. |
| 4. Enter the decommissioning cost, then the settings: analysis years (1 to 5), escalation, discount rate, number of users and loaded cost per FTE. |
| 5. Read the lowest-cost option, its total and the saving over the next option on the Comparison tab. |
| Formulas and method |
| Acquisition cost is paid in year 0: the acquisition subtotal of each option. |
| Recurring cost in year t (1 to the analysis years) = (operating cost per year + expected downtime cost per year) times (1 + escalation) raised to the power t minus 1. |
Showing the first 16 of 33 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This workbook compares the total cost of three ways to meet the same need, such as an on-premises system, a cloud subscription and a hybrid setup. The Costs tab holds the cost lines for each option: acquisition in year 0 (hardware, licenses, implementation and data migration); operating costs per year (subscription fees, maintenance and support, hosting, power and cooling, staff time and training); expected downtime cost per year; and decommissioning in the final year.
The Comparison tab escalates the recurring lines, so a recurring cost in year t grows by (1 + escalation) raised to the power t minus 1. It totals each option over the analysis period, calculates the NPV of costs at the discount rate, divides the total by users and months to give the cost per user per month, ranks the options from lowest to highest and shows the gap to the lowest option. The headline tiles name the lowest-cost option and the saving over the next one.
All figures are fictional examples. A low total is not a recommendation for any option. Agree each cost line with the customer before you present the ranking.
What’s inside
- Acquisition, operating, downtime risk and end-of-life costs for three options
- Recurring lines escalate by (1 + escalation) raised to the power of year minus 1
- Total cost, NPV of costs and cost per user per month for each option
- Rank and difference against the lowest option, updated as inputs change
- Headline tiles name the lowest-cost option and the saving over the next one
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Comparison | Cost by year for each option, totals, NPV, cost per user per month, rank and the gap to the lowest option. |
| Costs | Cost lines for each option, grouped by acquisition, operating, downtime risk and end of life, with settings. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 55 formulas in 202 cells across 3 tabs, so 27% of its cells calculate. They use 12 distinct functions; the longest formula is 143 characters and 30 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 59 | one result when a test is true, another when false |
AND | 19 | true when every test is true |
MIN | 6 | smallest value |
SUM | 6 | adds numbers |
NPV | 3 | net present value of a cash flow series |
OR | 3 | true when any test is true |
RANK | 3 | position of a value in a list |
COUNTA | 1 | counts non-empty cells |
INDEX | 1 | value at a position in a range |
INT | 1 | rounds down to a whole number |
MATCH | 1 | position of a value in a range |
SMALL | 1 | nth smallest value |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- On the Costs tab, name the three options in row 5.
- Enter the acquisition, operating and downtime cost lines for each option.
- Enter the decommissioning cost, the number of users and the loaded cost per FTE.
- Set the analysis years (1 to 5), the escalation rate and the discount rate.
- Read the lowest-cost option and the saving over the next option on the Comparison tab.
What is it good for?
- On-premises versus cloud subscription decisions
- Hybrid designs compared with the two extremes
- Showing a buyer what a one-year delay in migration costs
- Comparing vendor packages over a fixed term
Questions about this sheet
How is recurring cost escalated?
Each recurring line in year t is multiplied by (1 + escalation) raised to the power t minus 1, so year 1 uses the entered amounts. Decommissioning is not escalated.
How is the NPV of costs calculated?
It is the year 0 cost plus the NPV of years 1 to 5 at the discount rate, using the Excel NPV function on the yearly totals.
Why does cost per user per month divide by months?
The total over the analysis period is divided by users times analysis years times 12. The total includes the year 0 acquisition cost, so the figure is an average for the whole period.
Does the sheet model revenue or productivity?
No. It compares costs only. Revenue, productivity gains and tax effects are outside the model; use the ROI calculator for benefits.