IRR calculation by using Quadratic Equation
-
With reference to example discussed in lesson #29 required rate of return is calculated by using quadratic equation. Complete procedure to calculate required rate of return is given below.
r* = 16% AND r = - 5 % are assumed values. Actual rate is calculated below with the values given in example of lesson 29 of handouts.Quadratic Equation
Following is the process which shows that how quadratic equation helps to determine the value of IRR ®.
CF0 = cash outflow in zero year = Rs. 1,000
CF1 = cash inflow in first year = Rs. 100
CF2 = cash inflow in second year = Rs. 100+Rs. 1,000= Rs. 1,100q= CF1 + {( CF1)2 - 4 CF0CF2}0.5 and q= CF1 - {( CF1)2 - 4 CF0CF2}0.5
2 CF0 2 CF0q= 100 + {(100)2 - 4 (-1,000)(1,100)}0.5 and q= 100- {(100)2 - 4 (-1,000)(1,100)}0.5
2 (1,000) 2 (1,000)q= 100 + 2,100 and q= 100- 2,100
2,000 2,000q= 2,200 and q= - 2,000
2,000 2,000q= 1.1 and q= - 1
As we know that q=1+i (it is assumed above)
Now value of i or r will be:
1+i= 1.1 and 1+i = - 1
i =0.1 and i =-2
Negative rate will be ignored and conclude that for this investment the internal rate of return (r or i) equals 10 %( 0.1) -
If a project require Rs.17200 initial investment and its expected cash flow Rs.5100 continuously 05 year what will be its IRR?
If a project require Rs.22430 initial investment and its expected cash flow Rs.7500 continuously 05 year what will be its IRR?
If a project require Rs.1065000 initial investment and its expected cash flow Rs.350000 continuously 05 year what will be its IRR?(17,200) (22,430) (1,065,000) (1,000,000) 1st year 5,100 7,500 350,000 150,000 2nd year 5,100 7,500 350,000 150,000 3rd year 5,100 7,500 350,000 150,000 4th year 5,100 7,500 350,000 150,000 5th year 5,100 7,500 350,000 150,000 14.74% 20.00% 19.22% 150,000 15% 150,000 150,000 150,000 150,000 TIPS 8.14% Ø Initial investment will be written with minus sigh for example (-17200)
Ø Cash flow will be written according to chronological order for example in first cell cash flow of first year will be written and respectively other years.
Ø If at last year 02 cash flow happen than it will be written in one cell for example if last year cash flow of 5th year is (5100 & 4500). It will be considered as 9600 in 5th year.
Ø After completing all the parameter. Click a cell and Insert the IRR function
Ø Write = Irr( drag all the cash flow in the bracket) press inter key for example =IRR(F10:F15)
Ø (F10:F15) ( F10 to F15 consist of initial investment & all cash flow chronological order
Ø If it demand guess give it to 0.1 otherwise leave it
Ø Result will be 15%, now you have split it in two decimal place for accuracy