When the question is in calculating the payback of an investment project, within the framework of its financial and economic model, a simple and an understandable mechanism for building this algorithm.
The basis for calculating the payback of an investment project
For many, the payback period of the project appears to be the result of dividing the total investments by the average annual profit from the project. But not everything is like that, in fact, it is banal if you approach this process more professionally. There are some nuances that need to be taken into account when taking the right approach to calculating this fundamental indicator of evaluating the effectiveness of a project.
An important element of the payback process of a project is its dynamics. I.e., the period of time from the start of its financing to the moment of complete wear and tear and the inability to generate income for them (usually within its normative service life). This is the calculation horizon.
The payback of an investment project is always calculated in relation to the total amount of investments consisting of:
- capital assets (non-current assets)
- net working capital
The sum of these two components is the total cost of the investment project. The VAT paid during the purchase of these objects is not included in its total cost, because it is, in fact, returned to the organization by offset when calculating the total amount of VAT.
Capital investments are equipment, construction works and other costs associated with the maintenance of these facilities (for example, bank interest and commissions on their purchase).
Net working capital (PSC) is necessary (for a planned production program) raw materials, WIP, goods, accounts receivable, etc. (i.e. current assets) minus the amount of short-term accounts payable, which reduces it and gives a certain PURITY to working capital due to this (PSC in another way).
We have figured out what should pay off. Now let's move on to what all this should be due to.
The project pays off at the expense of net income (return), which it generates (from the sale of generated products, assets) for each period within the entire calculation horizon.
It is important to understand that not at the expense of profit, but precisely at the expense of the net income received from the work of this project. It is the net income that is the free cache of the project going to cover the funds that were invested in it. This is his economic return.
A separate period (calculation step) can be as a day, month, quarter, year, depending on the analytical needs of the project. Usually, they take a year for investment projects (long-term investments).
As mentioned above, the total project implementation period (calculation horizon) is the time interval from the start of project financing to its complete depreciation.
Well, that's basically it.
That is, in order to calculate the payback of an investment project, we need total (total) investment costs and net income for each period that the project will issue after commissioning, within the entire calculation horizon (until its complete wear and tear).
Algorithm for calculating the payback of a project in EXCEL
To calculate the payback of the project in EXCEL, we will create a small table in it, fill it with the necessary data and lay down a simple algorithm with simple functionality (formulas).
For an easier understanding of this process, let's calculate the payback using a simple example.
There is an organization that plans to implement a certain investment project in accordance with its schedule.
The start of investment financing is planned from the 01st of the 04th month of the current period (base year - when investments are made).
That is, from 01.04, the organization pays to the address of the manufacturer /supplier of the equipment $ 1000 thousand. This is the starting point in the implementation of the project, which means that the payback period will already include (1 - 1/4) of the base (first) year.
The purchased equipment will be delivered, installed and put into operation only in the 3rd period (year). From the same period (and further by the years of the project implementation), products will already be produced, from the sale of which net income will be credited to the organization's current account (return, i.e. revenue minus cost, except depreciation).
Similarly, production activities are always associated with the need for net working capital. The calculation of the PSC is always based on the planned production program and the turnover of current assets for this type of activity.
In our case, these investments are planned and distributed during the 2nd and 4th periods of the project, from the moment of the start of production and the gradual increase in production capacity. The total amount of net working capital growth for these periods will amount to $ 350 thousand.
Since the service life of the main technological equipment (according to its technical data sheet) is 10 years in our case, we will evaluate the payback of the project within this period. I.e. in our case, the calculation horizon for the project will be 11 years, with the planned service life of the main technological equipment in 10 years (the financing period, development (construction) + operation).
All these data are entered into our table in the columns corresponding to the periods (years).
We have sorted out the investments (cash outflow), we are moving on to net income (cash inflow).
The receipt of net money (net income) from the sale of products, as already noted, is planned from the 3rd year of the project. The dynamics of its receipt over the years of the project is determined by the market and production capabilities, which is expressed in a gradual increase in the receipt of returns with the release from the 4th year to its stable level of $ 400 thousand.
The basis for calculating the payback of the project is laid in our table. It remains to apply a certain EXCEL functionality to determine its main integral indicators, on the basis of which the payback will be calculated.
In line 3 of our table, we calculate the Net Cash Flow (this is the difference between Net income and Total outflow, for each analyzed period (p.2- p.1)).
In line 5, we discount our Net Cash Flow and turn it into Discounted Cash Flow (page 3 x page 4).
Discounting is carried out using the calculated discount coefficient for each year (in page 4).
Vadim