2

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?

5

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.

  • Thank you for this, but please see my edit. Sorry not to be clearer when I originally posted. – Karl Aug 3 '17 at 15:35
0

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.

  • 1
    I believe you are changing the 3rd parameter, the Reinvest_rate, not the Finance_rate. – Karl Aug 2 '17 at 19:25
  • @Karl My bad, please see edit. – 0xFEE1DEAD Aug 2 '17 at 19:43
  • Thanks, but please see my edit. – Karl Aug 3 '17 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.