ROI Calculator for IT Projects Using NPV, IRR and Payback Period

ROI Calculator TemplateAn ROI calculator is the best way to develop the financial justification, or business case, for IT projects as long as it includes NPV, IRR and payback period. 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 though, 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.

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

spreadsheet illustrating ROI Calculations for NPV, IRR and payback for three projects

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.

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 your project submission process that is available free to subscribers.

The ROI Calculator Template is a comprehensive tool for determining the ROI of an IT investment or IT project which produces IRR, NPV, and Payback values. 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.

ROI Calculator TemplateThis MS Excel file is editable and includes two versions in one file allowing you a great deal of flexibility in setting up your own model.

To ensure an accurate business case and financial justification is produced, the ROI Calculator Template separates hard and soft dollar savings. Some organizations may want 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 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.

This entry was posted in IT Financial Management and tagged , , , , . Bookmark the permalink.

19 Responses to ROI Calculator for IT Projects Using NPV, IRR and Payback Period

  1. Pingback: Project Selection Criteria Using ROI, NPV, IRR and Risk

  2. Amit Masand says:

    Do you have any Document, which talks about it?

    Please do advise.

    Thanks,
    Amit Masand

  3. Pingback: Lease vs Buy - Net Advantage to Leasing Calculation

  4. The Higher Ed CIO says:

    I will send you an email.

  5. At Intel IT, we use a TCO based assesment to calculate NPV savings as our core measure of IT Business Value. However, when assessing projects, we do also evaluate softer savings like productivity, velocity, efficiency as well.
    For server refresh we created a tool to communicate the benefits to our business leaders (www.intel.com/go/xeonestimator). We have also used a TCO estimator to determine optimal timing for refresh of our 90,000 PC’s.

  6. The Higher Ed CIO says:

    Very cool and I am sure powerful for demonstrating the benefits. I wondered if cost per KWH would help NPV but developing energy savings as operational cost savings for refresh as well? Especially for P-V refresh the numbers could really be significant.

    Thanks for sharing it.

  7. Chris Garbett says:

    This is most useful. I assume you have no objections to people using your template. I intend to adapt this for use in course costings.

    Thanks awfully,

    Chris Garbett
    Leeds Metropolitan University
    UK

  8. The Higher Ed CIO says:

    Chris

    Not at all. My materials are all under Creative Commons (non-commercial) so please go ahead and use them in your courses.

  9. Pingback: Does Your RFP Template Support Open Source and Cloud Computing

  10. NPV, IRR, Payback period – these are all great and widely popular financial tools that help calculate the real financial benefits to organization and aide decision making of investing in a particular technology.

    I looked at the multi-purpose ROI calculator… Fairly exhaustive I must say! I think however, the discussion can be taken up a notch to the usage of such financial tools and TCO/ROI calculators and how, they are often flawed in today’s context. A tool-driven approach without a comprehensive financial view of the business impact can potentially shift cost from IT to other departments/ groups within the company. As a result, you end up not taking any cost out of the system in real terms.

    Some thoughts here (http://bit.ly/ucMUmM) discuss this issue in great detail and talk about the need for a composite cost management framework that bridges the business-IT gap in the understanding of IT costs. It is a framework that goes beyond project evaluation and redefines approach to IT budgeting and management accounting practices. You could try our calculator (http://bit.ly/vZe5b6) which helps arrive at an hourly cost of application delivery for the enterprise, which is a key financial performance indicator for IT operations.

  11. The Higher Ed CIO says:

    Sanjiv – Thank you so much for the comment and the additional resources. I too am a firm believer in business cases developed by the projects owner not IT. Although you can do ROI/TCO just on the IT side, I do not recommend it as I have outlined in many other posts. The business must assess the total cost of a project or investment in order to make a sound decision.

  12. Tina Dy says:

    I received an email upon subscribing however it did not contain the download link for the ROI Calculator Template. Hoping for your immediate assistance. Thanks.

  13. The Higher Ed CIO says:

    Tina – I will send it to you directly.

  14. Santo says:

    Hi Ed,

    Good article. Request you to send me the ROI calculator. Thanks

    reg

    Santo

  15. The Higher Ed CIO says:

    Santo,

    You can get the ROI Calculator on this page which is offered free to subscribers.

  16. Keith Neuman says:

    I subscribed but did not get the link to the ROI calculator in the email. Can you please send it to me? Thanks.

  17. The Higher Ed CIO says:

    On its way with my apologies for the hiccup.

  18. Gary says:

    Hi, thanks for your blog it is great.

    I subscribed but did not get the download link, can you please send it to me or forward the model so that I may look at it.

    Kind regards

    Gary

  19. The Higher Ed CIO says:

    I seem to have a minor issue with the links for some visitors since moving my site. I hope to have this resolved today. Meanwhile, I will email you the file directly.

Comments are closed.