Calculating Internal Rate of Return in Excel and Google Sheets

[subornaakter40]

Let's look at several formulas for calculating the IRR.

Formula for calculating GNI in Excel
There are two methods for calculating the internal rate of return of a project in Excel:

using built-in functions;

using the Solver tool.

Let's start with the functionality built usa mobile phone numbers database into the system. To calculate the internal rate of return of the project, it is necessary to create a table with annual planned indicators, which will include several columns. It is important to reflect in this table such important data as initial investments and annual financial results of the project.

Please note that financial results presented annually must be presented in undiscounted format, i.e. without recalculating them to current prices.

For greater transparency, you can add a breakdown of the planned annual income and expenses at each stage, from which the final result of the project is formed.

Example:

A year of project life Initial investment, RUB Planned income for the project, RUB. Planned project expenses, RUB. Financial results of the project, RUB.
1st 100,000 0 100,000 – 100,000
2nd 0 40,000 30,000 10,000
3rd 0 50,000 35,000 15,000
4th 0 45,000 30,000 15,000
After creating this table, you need to use the IRR formula to calculate the internal rate of return.

Make sure that in the cell intended for the value of the IRR formula, you have specified the range of amounts from the column with the financial results of the project.

In real practice, investment projects do not always have stable cash flows. There is a possibility of pauses: freezing of projects, their temporary suspension for various reasons, etc. In this situation, another formula is used, designated in the Russian version of Excel as ЧИСТВНОДХ. The main difference between this formula and the previous one is that, in addition to the financial results of the project, it is necessary to specify the time intervals (dates) in which specific financial results are formed.

Formula for calculating GNI in Excel

Source: shutterstock.com

To calculate the internal rate of return of a project using the Solution Finder tool, you need to supplement the table of project values ​​with a column reflecting the annual discounted financial results.

Then, in a separate cell, you should indicate that the NPV calculation will be performed here, and enter the appropriate formula, which will refer to another empty cell where the internal rate of return will be calculated.

Please note! In the "Set Target Cell" line, you must specify a reference to the cell containing the NPV formula. Then you must set that the target cell must have a zero value.

In the "Changing cell value" section, you need to specify a reference to an empty cell in which the indicator we need will be calculated. After that, you should use the "Search for solutions" function to find the interest rate value at which NPV becomes zero.

Formula for calculating GNI in Google Sheets
You can calculate your GNI in Google Sheets in just a few steps:

select an empty cell;

in the menu, select the items “Insert — Functions — Financial functions — IRR”;

Select the required cells or add new ones if you want to perform calculations manually: start with the negative value of investment costs and end with the last period of cash flows;

To calculate the second project, repeat the same process.

After this, all that remains is to compare the profitability of all projects.

To ensure the safety and profitability of the business, it is important to use formulas, calculations and terms associated with both Russian and foreign abbreviations. It is necessary to apply calculations, a practical approach and analytical thinking - this is the basis for successful business.