Excel was originally created to facilitate calculations in many areas, including business. Using its capabilities, you can quickly perform complex calculations, including for forecasting the profitability of certain projects. For example, Excel allows you to easily calculate the IRR of a project. How to do this in practice, this article will tell.
What is IRR?
This abbreviation refers to the internal rate of return (GNI) of a particular investment project. This indicator is often used to compare proposals for the prospect of profitability and business growth. In numerical terms, IRR is the interest rate at which the present value of all cash flows necessary for the implementation of the investment project is canceled (denoted by NPV or NPV). The higher the GNI, the more promising is the investment project.
How to rate
, . , , , IRR . , IRR . , . « » « » ( ).
, :
:
- IRR > , ;
- IRR = , , ;
- IRR < , .
IRR
, (. ).
:
IRR , . , , .
, . IRR r, NPV, r, .
, IRR = r , NPV(f(r)) .
.
. , , .
IRR 2 :
- IRR r1 r2 , r1 < r2;
- r, IRR, .
r1 r2 , NPV = f (r) (r1, r2) .
, IRR , .
, IRR %- .
NPV, PI :
- NPV , IRR > PI > 1;
- NPV = 0, IRR = PI = 1;
- NPV , IRR < PI< 1.
, , IRR , , , . IRR. NPV, .
IRR Excel
, — . . , IRR Excel ( . ).
Microsoft — , IRR .
IRR ( , ) Excel , (; ).
:
Microsoft Excel . «», , 0,00001 %. 20 , , , «#!».
, «». , 0,1 (10 %).
«#!» , , «».
"": 1
IRR ( ) . , 9 , Excel.
| A | B | C | D | E |
1 | () | | | | |
2 | 0 | 200 000 . | - . | 200000 . | 200000 . |
3 | 1 | - . | 50000 . | 30000 . | 20000 . |
4 | 2 | - . | 60000 . | 33000 . | 27000 . |
5 | 3 | - . | 45000 . | 28000 . | 17000 . |
6 | 4 | - . | 50000 . | 15000 . | 35000 . |
7 | 5 | - . | 53000 . | 20000 . | 33000 . |
8 | 6 | - . | 47000 . | 18000 . | 29000 . |
9 | 7 | - . | 62000 . | 25000 . | 37000 . |
10 | 8 | - . | 70000 . | 30 000 . | 40000 . |
11 | 9 | - . | 64000 . | 33000 . | 31000 . |
12 | | IRR | 6% |
12 «= (3:2)». 6 %.
"": 2
, , IRR « ».
NPV=0. ( NPV). .
| A | B | C | D | E | F |
1 | () |
|
|
|
|
|
2 | 0 | 200000 . | - . | 200000 . | 200000 . | |
3 | 1 | - . | 50000 . | 20 000 . | 20000 . | 20000 . |
4 | 2 | - . | 60000 . | 20000 . | 27000 . | 27000 . |
5 | 3 | - . | 45000 . | 20000 . | 17000 . | 17000 . |
6 | 4 | - . | 50000 . | 20000 . | 35000 . | 35000 . |
7 | 5 | - . | 53000 . | 20000 . | 33000 . | 33 000 . |
8 | 6 | - . | 47000 . | 20000 . | 29000 . | 29000 . |
9 | 7 | - . | 62000 . | 20000 . | 37000 . | 37000 . |
10 | 8 | - . | 70000 . | 20000 . | 40000 . | 40000 . |
11 | 9 | - . | 64000 . | 20000 . | 31000 . | 31000 . |
12 | | NPV | 69000 . |
| | IRR | |
«=E5/(1+$F$11)^A5».
NPV «=(F5:F13)-B7».
« » IRR, NPV . «» « ».
« », NPV, . . +$F$16. :
F17 . , 6%, , Excel.
MIRR
. . MIRR .
:
- MIRR — ;
- COFt — t;
- CIFt – ;
- r – , WACC;
- d – %- ;
- n – .
MIRR
IRR ( ), Excel.
. , . IRR , . MIRR .
| A | B | C | D | E |
1 | | 10 % | | |
2 | | 12% | | |
3 | () | | | | |
4 | 0 | 200000 . | - . | 200000 . | 200000 . |
5 | 1 | - . | 50000 . | 30000 . | 20000 . |
6 | 2 | - . | 60000 . | 33000 . | 27000 . |
7 | 3 | - . | 45000 . | 28000 . | 17000 . |
8 | 4 | - . | 50000 . | 15000 . | 35000 . |
9 | 5 | - . | 53000 . | 20000 . | 33000 . |
10 | 6 | - . | 47000 . | 18000 . | 29000 . |
11 | 7 | - . | 62000 . | 25000 . | 37000 . |
12 | 8 | - . | 70000 . | 30000 . | 40000 . |
13 | 9 | - . | 64000 . | 33000 . | 31000 . |
14 | | MIRR | 9% |
14 MIRR «=(E3:E13;C1;C2)».
, IRR . . :
. :
! MIRR, .
IRR
, , , (). . , , Microsoft Excel . ( , = 12, , = 365) .
Now, if you know what the internal rate of return is, therefore, if you are told: “For each of the projects listed below, calculate the IRR”, you will not have any difficulties.