ROI Calculator Template for IT Investments Using NPV, IRR and Payback Period

ROI Calculator Template

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.

ROI Calculator Template

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.

ROI Calculator Template
ROI Calculator Template captures the hard and soft dollars revenues and savings along with the intangibles for an IT investment or project and computes the IRR, NPV, Payback values and any depreciation, taxes and the ROI values automatically.
Price: $14.95

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.

ROI Calculator Example with NPV IRR and Payback

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.

ROI Calculator Template
ROI Calculator Template captures the hard and soft dollars revenues and savings along with the intangibles for an IT investment or project and computes the IRR, NPV, Payback values and any depreciation, taxes and the ROI values automatically.
Price: $14.95

4 Responses to ROI Calculator Template for IT Investments Using NPV, IRR and Payback Period

  1. Dave says:

    In regard to ‘ROI Calculator Template for IT Investments Using NPV, IRR and Payback Period’, does the calculator account for intangible benefits or just simply tangible benefits? In particular, does it offer explicit calculations about how to calculate intangible benefits? I am sold IF it explicitly and extensively demonstrates how to conclusively quantify intangible benefits.

  2. Jerry says:

    The calculator offers you that ability as well as a simplified version to avoid confusing your organization. You will still need to quantify the intangibles and associate them with a line item in revenue or expense.

  3. Dave says:

    By saying “The calculator offers you that ability…” do you mean that it actually has examples of explicit quantifiable metrics used to calculate some intangibles OR do you mean there’s simply an intangible line of item in the spreadsheet such as “Overall experience” with a dollar amount assigned to it but no explicit metrics to justify how that dollar amount was arrived[this is the work of the purchaser to figure this out]? I am interested in explicit demonstrable and quantifiable metrics used to justify a final dollar value to the example of intangible[if any in your spreadsheet]…and not simply intangible lines of item in the spreadsheet with no explicit justification at the arrival to the final dollar amount.

  4. Jerry says:

    I will email you the fields with an explanation n intangibles.

Leave a Reply

Your email address will not be published.


− 2 = 3

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>