How to use Net Present Value (NPV) and Return On Investment (ROI)【Excel Template】
For an investmnt analysis, using ROI% is famous, but also use Payout Time and Cumulative NPV (Net Present Value) as well. Especially converting to NPV is very important.
(Duration: 8:52)
DOWNLOAD ← Click this to download the “ROI Analysis (ROI, NPV)” template file.
- The Index for Investment: Return On Investment (ROI)
- Necessity of ROI Analysis and the ROI Analysis Excel Template
- 3 KPI’s for an Investment Analysis: ROI %, Payout Time and Cumulative NPV
- How to estimate your investments and profit gaining for each project
- Use this analysis for your PDCA Cycle afterwards too.
The Index for Investment: Return On Investment (ROI)
Hi, this is Mike Negami, Lean Sigma Black Belt.
We are experts in Process Improvement. We prioritize issues and problems and make and conduct Kaizen projects. For that prioritizing, it’s very effective to analyze the return on investment for each project. You can use this method for real estate investment, IT investment and so on in the same way.
You want to see how much money you would spend on and how much profit return you would receive from each project. We can use ROI % for that, which stands for Return on Investment Ratio. The calculation formula is simple. It’s a % of the profit amount obtained from the project divided by the amount you spent on the project.
ROI % = Profit $ ÷ Spent $ × 100%
For example, if you earn $20,000 on a project and you spent $100,000 on, that will be 20,000 divided by 100,000, so the return on investment will be 20%. Projects with a higher percentage of Return On Investment will have higher priority.
ROI % ＝ $20K (Profit) ÷ $100K (Spent) ×100% = 20%
Necessity of ROI Analysis and the ROI Analysis Excel Template
That formula itself is easy, but it’s quite difficult to use it in practice. No one can predict the future. Some people say, “Even if you calculate such a thing, it’s never accurate.” It may be true, but I strongly recommend doing this.
In order to complete the calculation, you have to compare the way you’ve been doing things so far and the way you would do in the future. In some case, you even have to consider the wages of your employees. Conversely, by doing this, you can understand each project more.
I made an Excel template for an investment analysis, and I’ll show you how to do that analysis accordingly. ⇒’ROI Analysis (ROI, NPV)’ Template Download
3 KPI’s for an Investment Analysis: ROI %, Payout Time and Cumulative NPV
This above is the summary table of the template. With this template, you can compare up to 5 projects or investment cases. First, write each project name on Column C. The Investment Period is the period of the investment’s effectiveness. It’s a maximum of 10 years in this template.
In addition to the ‘Return On Investment %’, you can get the ‘Payout Time’, which is how many years later the investment will be paid back. Of course, the shorter that number, the better the investment is.
Here is one problem: If there is a project with a high Return On Investment % and a short Payout Time, is it the best investment? It’s not so. It may just be a small investment project.
You can see how much profit $ amount can actually be expected in the next measurement, ‘Cumulative NPV’. NPV stands for ‘Net Present Value’.
Let me ask you, if you can choose whether somebody gives you $10,000 today, or will give you $10,000 one year later, which is better? Of course, you want it today before they change their mind.
There is another reason. The current $10,000 value is worth more than $10,000 a year later, because you can get interest just by putting some money in a savings account. Since the investment is for several years, it’s necessary to consider that the net value decrease over time. That is ‘NPV’.
How to calculate NPV and what’s Cumulative NPV?
In order to calculate this Net Present Value, you need ‘Depreciation Rate’, by which your value will decrease annually. For example, when the Depreciation Rate is 2%, the NPV of $10,000 2 years later is this:
NPV ＝ $10K ÷ (100%+ Depreciation%) Power of [Year]
＝$10K÷（100％＋2％）Power of 2
＝$10K ÷ 1.0404 ＝ $9.612K
Then, ‘Cumulative NPV’ is calculated by a sum total of each year’s expenses (Cash Out) and profit amounts (Cash In), converted into NPV with the year within the Investment Period.
With the example of the image above, the Gross Balance on Row 30 is each year’s total of Cash Out’s and Cash In’s. On Row 31, they are converted into NPV. On Row 32, Cumulative NPV accumulate from current to future (from left to right).
In this example, the Investment Period is 10 years and the value in Cell N32 is the final Cumulative NPV. If that value is negative, it indicates the investment has failed. The larger the amount, the better the investment.
By the way, in this template, you can put a % on Range O4:O8 for ‘Depreciation Rate’ for each project. You can change the % for each project. Also both the Return On Investment and Payout Time are calculated from their Net Present Value, which better reflects reality.
Please examine each investment project at various angles according to the Return On Investment, Payout Time and Net Present Value and prioritize your projects.
The Net Present Value is the most important, but please be aware that this number will change considerably depending on the Investment Period and Depreciation Rate.
How to estimate your investments and profit gaining for each project
These three calculations are done automatically by Excel, but the difficult thing is to enter estimated investment $ amounts and profit $ amounts for each project, which will be the source data of the calculations.
Think of each project and enter all their investments as negative numbers in the Cash Out sections in the light blue cells, and their gross profits in positive numbers in the Cash In sections. Clicking the [+] (plus) icon above Column N opens the hidden columns and allows you to enter for 10 years’ entries.
Project A is the investment in manufacturing machinery as an example. Put the purchase amount as a negative number in the ‘Current’ column. Then, if there are maintenance costs and/or administrative expenses afterwards, put in those $ amounts every year. If you hired new people due to the new machines, put those labor costs in the Cash Out section as well.
Next, let’s think about profit, which is Cash In. If there is a profit on sale of the old machines due to setting new machines, add that amount as a positive number.
Since maintenance costs of the old machines were ended, add them as positive numbers. If the new machines’ maintenance costs are lower than the old ones, it would be profitable.
As other Cash In, if you can expect new sales because of introducing the new machines, add their gross margin amounts from the sales. If you would have sales losses if you hadn’t purchased the new machines, you should put their gross margins in Cash In too.
Sometimes, you have to be creative, for example, such as when you’re evaluating quality improvement results or calculating time-savings. You need to convert those results into monetary value by considering sales increases due to the quality improvement or by calculating from labor costs.
Use this analysis for your PDCA Cycle afterwards too.
This evaluation is totally depending on the situation and there is no right answer. However, because of discussions and these investment analyses among stakeholders, they will gain a deep understanding of all investment projects. Therefore, this will be a very valuable activity.
You’ll use this as judgment material for the investment projects, but do not end with that. Since you would have forecasts for several years, you can use them for your PDCA cycle every year afterwards. When more detailed data comes out as you go, you may adjust your forecasts and use them as lessons learned to improve your next investment analysis.
“See these other popular articles.”
