Project Return on Investment Tool
Every company has customers, but not all companies have customers who care about Projected Rate of Return on their investment in your product. So what do you do if your customers are the ones who want to see how you are affecting their bottom line or if you want to determine if an internal project is worth the investment? You use the Project Return on Investment Tool.
This can become a valuable sales tool to work through with customers to show the savings they will reap in return for their payment for your product. This tool provides an ROI calculation that can be used for internal projects as well and takes into account multi-year returns.
Please keep the following in mind while you are going through this calculator:
This spreadsheet is built to be simple and flexible
Since every Business Case is unique, it is meant to be a solid basis for any analysis, not a complete solution for every possible scenario
Save a master copy so you always have one, make any changes you see fit for each analysis.
Steps on Input/Output
Input information in the blue areas
An example is pre-populated, be sure to zero out the example before starting
Blue inputs drive the analysis page which calculates the outputs
All input changes automatically ‘roll’ and update outputs
Outputs are on both the analysis page and also the inputs/outputs page
Use either Monthly Recurring or Annual Recurring, depending on your needs.
The analysis pulls from both columns, so do not enter twice as it will double count.
“One Time” cost savings and “upfront” expenditures are placed into Year 1 only
Analysis will depreciate the capital amount over the term of the project automatically
If you want to see gridlines and cell information, click on ‘Tools’, ‘Options’, ‘View’, and check ‘Gridlines’ and ‘Row/Column Header’
The analysis tab shows a detailed view of the analysis.
ROI versus IRR
ROI stands for ‘Return on Investment’. It is important to define ROI and also how it relates to Internal Rate of Return, or IRR.
ROI is simply the percentage return on an investment or expenditure. If you invest $100 and receive $110 in return, the ROI is $10 or 10% in this case. The actual formula is ((Revenue or Cost Savings minus Investment Amount) divided by Investment Amount) multiplied by 100 or (($110-$100)/$100) x 100 = 10%.
IRR is essentially the same thing, it is cash flow received over a period of time vs. capital outlay. Excel has a function, which calculates IRR and is used in this ProForma.
VERY IMPORTANT
If the time period of the analysis is one year or less, IRR = ROI. Both calculations will yield the exact same result.
- For time periods longer than one year, IRR is a more accurate measure as it takes into account these time periods (i.e. the time value of money) whereas a simple ROI calculation does not.
In our above example, if it takes you 2 years to get the $110, the return is 10% overall, but really is closer to 5% per year which is a more accurate metric. In this scenario, IRR for $110 received evenly over 2 years ($55/yr) is 6.6%. Why is it not 5%? It is because of the compounding and iterative assumptions an IRR takes into account. It is not essential you know the details, just that it takes into account the time period, or time value of money.
IRR will yield better results depending on the amounts of cash flows throughout the time period. The sooner money comes in, the higher the IRR. Remember the above 6.6% IRR if we received $55/yr each year for 2 years? If instead we received $0 in the first year and $110 all in the second year the IRR would drop to 4.9%. The larger the amount of payments and the earlier they occur, the better the IRR.
ONE FINAL CLARIFICATION:
This proforma calculates IRR on both the entire project expenditures as well as the capital outlay only. ROI calculation is based on entire project expenditures and savings.
