MIRR is going higher with a higher Finance rate, but it should not. Why is this?

[VDawg]

I can't figure out why my MIRR is getting higher when I boost my Finance Rate. Shouldn't it fall as the negative cash flows incur a higher expense?

 

[StephenCrump]

Welcome to the Forum!

The calculation is correct.

MIRR is based on the ratio of FV(returns on investment at the reinvestment rate) to NPV(cost of funding the investment at the funding rate), as shown below:

	A	B	C	D	E	F	G	H
1			0	1	2	3	4	5
2	IRR	210.62%	-10,000	-20,000	25,000	220,000	600,000	18,000
3
4	Finance	50%
5	Reinvest	20%
6
7	NPV - Finance	-23,333	-10,000	-20,000
8	FV - Reinvest	1,098,000	0	0	25,000	220,000	600,000	18,000
9	MIRR	116.04%
10	Check:	116.04%
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IRR(C2:H2)
B7	B7	=NPV(frate,C7:H7)*(1+frate)
B8	B8	=NPV(rrate,C8:H8)*(1+rrate)^(N+1)
B9	B9	=(-B8/B7)^(1/N)-1
B10	B10	=MIRR(C2:H2,B4,B5)

Named Ranges
Name	Refers To	Cells
frate	=Sheet1!$B$4	B10, B7
N	=Sheet1!$H$1	B8:B9
rrate	=Sheet1!$B$5	B10, B8

For an investor with a given funding cost, and a given reinvestment rate (or rates), MIRR can be used to evaluate the returns on different investments.

You are correct that if an investor has a higher funding cost, a given amount of finance will cost more to repay. But that's not what the MIRR is measuring.

 

[jorismoerings]

Hi,

as addition to @StephenCrump

By increasing the finance rate, the cost of borrowing and the profit from interest will go up.
In your example the sum of negative cash flows is significantly lower as the sum of positive cash flows, the incurring of a higher cost on negative cash flows is outweighed by the interest on positive cashflows.