Return on investment calculation is simple
In the life of a business, investing is an important event that has a serious impact on the company’s future. Its purpose is to launch a new product in the traditional market, to conquer a new market, or to improve profitability in parallel with an increase in sales, for example, when buying another company. Investment decisions are usually preceded by a serious survey or analysis, one of which is the return calculation . It doesn't matter how long it takes for the money invested to pay off. Does it pay off at all?
There are simple calculation methods. Such is the ROI metric, which responds to how the return on investment is proportional to the cost of the investment:
There are simple calculation methods. Such is the ROI metric, which responds to how the return on investment is proportional to the cost of the investment:
ROI = (Investment profit - Cost of Investment) / Cost of InvestmentInvestment profit = profit after-tax + depreciation
However, this is a static calculation, as it does not take into account the time value of money, which means that e.g. In 5 years, 1000 EUR will not be worth as much as today. To take this into account, the dynamic return calculation (indicators of net present value, NPV and internal rate of return, IRR) was introduced, for which I offer an Excel template in my webshop.
Lately, I’ve found that there’s a growing interest in this template. That's why I improved it.
The development was three-way.
1. I created three worksheets in order for the designer to examine different versions. The most important data of the worksheets - working capital, material and labor costs - can be planned in detail.
The planing steps are as follows:
- investment income planning (grants, loans),
- investment-related expenses (planning, acquisition of assets, personnel costs, etc.),
- operating revenues (sales of products, sales, other income, e. g. dividends),
- planning of operating expenses, cooperation, wages, other expenses). To plan the material and labor costs, their ratio to sales revenue must be given. Other costs can be planned by cost item on a separate worksheet and entered in the main tables.
2. I created a dashboard that shows the most important data of the three types and the diagrams of the versions.
At the dashboard, you can specify how many time intervals the investment will be shown on the dashboard. These data will be displayed in the table, and a vertical line on the diagrams indicates the current values of the curves for the period under study.
This is what the dashboard looks like.
3. In order to read the payback timeI created a dynamic Quick Calculator that displays NPV, IRR, and cumulated cash on a chart by entering a few pieces of data:
A limited-edition trial is now available for download.
The restrictions are as follows: only the two lines of the Quick Calculator, Discount Rate and Revenue, can be used.