Question

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)

Answer 1

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 %