The ROI Calculator Template is a comprehensive tool and the best way of determining the ROI of an IT investment or IT project which produces IRR, NPV, and Payback Period values.
By using an ROI calculator that produces the NPV, IRR and payback period for proposed projects, CIO’s and IT governance committees can more easily evaluate unrelated projects and IT investments for funding decisions.
To be most effective, the use of an ROI calculator must reflect the overall business case of the proposed project by also incorporating the intangible benefits that are not always easily supported by a pure financial analysis.
ROI Calculator Template
Determining the ROI of any proposed IT project is essential to making a sound funding decision. So I have created an ROI Calculator Template which you can use in conjunction with the other project management templates available on this site.
This MS Excel file is fully editable giving you complete flexibility in setting up your specifc model while still saving you several hours of work creating something from scratch.
To help you save even more time I have included two versions in this one file. The differences allow you to decide on using a 7-year period or 5-year period for analysis.
The users simply fills in some administrative information and the financial estimates from the business case for revenues and expenses and the ROI Calculator computes depreciation, taxes and the ROI values automatically.
To ensure an accurate business case and financial justification are produced, the ROI Calculator Template separates hard and soft dollar savings. The two versions of the Template also offers an option to include intangible sources of revenue, savings or expense since many organizations choose to exclude all soft dollar savings all together while others may combine them to keep it simple.
The ROI Calculator Template also includes a second version of the worksheet that accounts for corporate taxes and a longer period which may be overboard for some companies and not applicable to public entities and non-profits.
CIO’s should review the ROI Calculator Template with their CFO before implementing it to ensure it meets your organization’s standards. Ideally, the ROI Calculator would be merged with the IT Project Budget Request template shared earlier.
Importance of an ROI Calculator
The function of an ROI calculator is to provide a standardized financial analysis method for developing the financial justification of IT projects and IT investments and determining their return on investment (ROI). The purpose of using a standardized ROI calculator is so that all projects in an organization’s project portfolio can be compared for approval and funding decisions based on the same criteria, even when they are unrelated initiatives.
Of course there are other methods of evaluating IT projects and IT investments for approval. Many of them were covered in my previous post on Evaluating IT Investments so I won’t cover them here.
NPV, IRR, and Payback
To illustrate the importance of using a standardized ROI calculator I thought some examples would help. Referring to Projects A and B, where each project requires and initial investment of $40,000 and will produce $75,000 of cash flow over the next 5 years. Many project sponsors and CIO’s would represent a simple ROI of $35,000 for each project.
But the simple method does not account for the time value of money which says that a dollar expected sooner is worth more than a dollar expected later. The time value of money requires that future cash flows be discounted to their present value. Determining the discounted cash flow is done using the calculations for Net Present Value (NPV) and Internal Rate of Return (IRR).
Referring back to Projects A and B, we see that because Project A cash flow occurs later than those of Project B they are discounted more in the NPV calculation giving a lower IRR and longer payback period. By using NPV, IRR and Payback Period to evaluate the projects reveals Project B has a better ROI than Project A.
Project C offers another illustration with a higher initial investment and projected cash flows. But when evaluated using a discounted cash flow using NPV and IRR, Project C reveals a more favorable ROI than the numbers might suggest on their face.