How is IRR calculated?
Posted: Sat Feb 01, 2025 6:35 am
To calculate manually using a special formula, you need the following information:
initial investment – funds that need to be invested at the project launch stage;
profit – expected financial receipts during the life cycle;
costs – expenses associated with the maintenance and operation of the project;
residual value – the final kenya email list amount of proceeds from sales.
Steps to calculate IRR
Source: shutterstock.com
Steps to calculate IRR
Determining NPV using the following formula:
NPV formula
Where:
CF 1 , CF 2 ...CF n – cash flows showing income, expenses and residual value;
R – discount rate;
CF 0 – the size of the initial investment.
Create the equation NPV = 0 and solve it by trial and error with the discount rate until the NPV (net present value) is zero. The equation used for this is:
NPV equation formula
As a result of calculating the IRR (IRR) using this formula, we obtain the rate at which the NPV is zero .
The method allows to estimate the profitability of initial investments. The formula allows to analyze profitability taking into account the terms of income, expenses, and the volume of initial investments.
Calculating IRR in Excel
To determine the internal rate of return in Excel, the built-in formula XIRR is used. To calculate, you need to know the cash flow in any of the periods where they occur.
The sequence of calculating IRR using Excel:
Create a new table.
In each cell of the first column, write the stages in which the cash flows occurred, from 0 to n (n is the number of project periods). If investments were also in the first, then the value 0 is skipped.
In the cells of the second column, write down the financial flows, taking into account income, expenses and residual value. Initial expenses are entered into the table with a minus sign.
Select the column with cash flows and use the IRR function (Insert – Function – Financial – IRR).
As an argument, select an interval with financial movements, except for the first period, if there were investments there.
Click "OK".
After finding the IRR using the above scheme, its value can be copied and placed in the table in the required place.
MS Excel is not the only way to perform such calculations. For these purposes, you can use special calculators or Google Sheets.
It should be taken into account that if cash flows are irregular or there have been several changes in the signs of cash flows, then the IRR calculation in Excel may give an incorrect result. For such cases, you need to know how the return on investment is calculated using other methods. You can also use other investment evaluation functions in Excel. For example, NPV (Net Present Value), MIRR (Modified Internal Rate of Return) (reinvestment interest rates are needed), and others. A plausible reinvestment hypothesis is made using the MIRR function.
Download a useful document on the topic:
Checklist: How to Achieve Your Goals in Negotiations with Clients.
initial investment – funds that need to be invested at the project launch stage;
profit – expected financial receipts during the life cycle;
costs – expenses associated with the maintenance and operation of the project;
residual value – the final kenya email list amount of proceeds from sales.
Steps to calculate IRR
Source: shutterstock.com
Steps to calculate IRR
Determining NPV using the following formula:
NPV formula
Where:
CF 1 , CF 2 ...CF n – cash flows showing income, expenses and residual value;
R – discount rate;
CF 0 – the size of the initial investment.
Create the equation NPV = 0 and solve it by trial and error with the discount rate until the NPV (net present value) is zero. The equation used for this is:
NPV equation formula
As a result of calculating the IRR (IRR) using this formula, we obtain the rate at which the NPV is zero .
The method allows to estimate the profitability of initial investments. The formula allows to analyze profitability taking into account the terms of income, expenses, and the volume of initial investments.
Calculating IRR in Excel
To determine the internal rate of return in Excel, the built-in formula XIRR is used. To calculate, you need to know the cash flow in any of the periods where they occur.
The sequence of calculating IRR using Excel:
Create a new table.
In each cell of the first column, write the stages in which the cash flows occurred, from 0 to n (n is the number of project periods). If investments were also in the first, then the value 0 is skipped.
In the cells of the second column, write down the financial flows, taking into account income, expenses and residual value. Initial expenses are entered into the table with a minus sign.
Select the column with cash flows and use the IRR function (Insert – Function – Financial – IRR).
As an argument, select an interval with financial movements, except for the first period, if there were investments there.
Click "OK".
After finding the IRR using the above scheme, its value can be copied and placed in the table in the required place.
MS Excel is not the only way to perform such calculations. For these purposes, you can use special calculators or Google Sheets.
It should be taken into account that if cash flows are irregular or there have been several changes in the signs of cash flows, then the IRR calculation in Excel may give an incorrect result. For such cases, you need to know how the return on investment is calculated using other methods. You can also use other investment evaluation functions in Excel. For example, NPV (Net Present Value), MIRR (Modified Internal Rate of Return) (reinvestment interest rates are needed), and others. A plausible reinvestment hypothesis is made using the MIRR function.
Download a useful document on the topic:
Checklist: How to Achieve Your Goals in Negotiations with Clients.