Clarify Microsoft's explanation of MIRR

Question

I don't understand Microsoft's MIRR example. The second parameter is the finance_rate:

  Finance_rate Required. The interest rate you pay on the money used in the cash flows.

The example then describes the first cash flow for -120,000 as a loan:

   Annual interest rate for the 120,000 loan

The MIRR after the full term, 5 years, is 13%.

Why is it, if the finance rate is changed, the MIRR does not change? The finance rate only impacts the MIRR if additional investments (negative cash flows) are made.

I'm not suggesting that the example is wrong. I just can't wrap my head around the idea that when there is a single investment cash flow and the investment rate changes, the MIRR doesn't change. How is this possible?

You can copy and paste the example to Excel as described and change the value in A8 to test this.

Edit, August 3rd: When I ask "how is this possible", I don't mean how can the equation produce this outcome. What I mean is, how would one explain it to a Finance 101 class that the finance rate can change and the MIRR does not change? That fact just is not intuitive and I think, needs an explanation.

Here is another non-intuitive result. Enter four cash flows - two negative followed by two positive so that the net cash flow is positive (i.e. a profit). Calculate the MIRR using a finance rate of 10% and refinance rate of 5%. Note the result. Lower the finance rate, say to 6%. The new MIRR will also be lower.

It does not seem logical that when the cost of financing goes down and the refinance rate does not change, that the MIRR would decrease. How can a cost go down, and all other variables remain the same and the return also go down?

Answer 1

The MIRR formula uses the finance rate to discount negative cash flows, but since the only negative cash flow in the example in in the current period, there's nothing to discount. It's meant to solve problems with IRR like when there are both positive and negative cash flows, which can result in multiple answers for IRR.

The example they give isn't a good one for MIRR because it's a simple spend now, earn later scenario, which IRR is perfectly fine for. If you add a negative cashflow somewhere after the first one you'll see the answer change with difference financing rates.

Answer 2

The value does change from 12.61% to 13.48%. The difference between re-investing cashflows at 14% vs 12% is not big enough to change the rounded value.

Edit:

The initial cashflow is discounted at t0, meaning it's already equal to its present value and the finance rate doesn't have an effect. It does impact future outgoing cashflows, as you've noted.