In: Finance
What is Mirr? how do you find it using excel without using excel formula?
Below is cash flow. I found IRR is 15.59% and required return is 12%
CF | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 |
Capital spen | $ (30,000,000.00) | $ 15,062,400.00 | |||||
Opp cost | $ (5,000,000.00) | ||||||
NWC | $ (1,120,000.00) | $ (150,000.00) | $ (170,000.00) | $ 140,000.00 | $ 1,300,000.00 | ||
OCF | $ 9,015,600.00 | $ 11,392,500.00 | $ 12,193,200.00 | $ 10,435,800.00 | $ (2,640,000.00) | $ (3,960,000.00) | |
CF | $ (36,120,000.00) | $ 8,865,600.00 | $ 11,222,500.00 | $ 12,333,200.00 | $ 26,798,200.00 | $ (2,640,000.00) | $ (3,960,000.00) |
Modified Internal Rate of Return:
MIRR is called as the Modified Internal rate of Return. It is a financial Measure of an Investment's attractiveness. It is used in capital budgeting to rank alternative investments of equal size.
Calculation of Modified Internal Rate of Return:
CF | Y0 | Y1 | Y2 | Y3 | Y4 | Y5 | Y6 |
Capital Spen | -30000000 | 15062400 | |||||
Opp Cost | -5000000 | ||||||
NWC | -1120000 | -150000 | -170000 | 140000 | 1300000 | ||
OCF | 9015600 | 11392500 | 12193200 | 10435800 | -2640000 | -3960000 | |
CF | -36120000 | 8865600 | 11222500 | 12333200 | 26798200 | -2640000 | -3960000 |
Required Return @ 12% | 1 | 0.892857 | 0.797194 | 0.71178 | 0.63551808 | 0.567427 | 0.506631 |
Discounted CF | -36120000 | 7915714 | 8946508 | 8778528 | 17030740.6 | -1498007 | -2006259 |
PV | 39167225.16 | ||||||
MIRR = nth root of (PV of Cashflows/investment)(1+ r) -1 | |||||||
= 6th root of (39167225.16/36120000) (1+12%) -1 | |||||||
= 1.032917 - 1 | |||||||
= 3.292 % | |||||||