MIRR Function sensitivity to Finance Rate

[SongBear]

Hi all, i was trying to check the sensitivity of the MIRR function to changes
in the frate. The equation uses input like this: =MIRR(values,frate,rrate)
where the values are the cash flows, the frate is the finance rate for the
capital, and the rrate is the re-investment rate of the return cash flows.
I changed the frate (also called cost of capital) from one percent (.01) to
a rediculous 90% (.9) and the answer (MIRR) didn't change.
Does anybody know enough about the formula or function that could explain
this?
In the help file, you can see where frate is used, but changing it, or even
leaving it out, doesn't change the answer.
So the question is...is there a bug in the implementation of the MIRR
formula, or is it supposed to do that, and why?
thanks!
SongBear

 

[Jerry W. Lewis]

There is a formula and example in HELP for MIRR. Unfortunately, Help
does not relate the example to the formula, and I have not managed to
reproduce the MIRR results from that formula. The formula also seems to
be unaffected by the frate in that example. As best I can tell, the
formula seems to assume that the loan is paid in full after the first
period (hence the insensitivity), which seems unreasonable. Perhaps one
of the accountants out there will weigh in?

Jerry

 

[opieandy]

Would be curious if anyone in this thread ever found an answer. I suspect
there is none, because the MIRR formula appears flawed. I'm an accountant
and I can tell you there is no explanation for why the finance rate doesn't
impact this formula. I can't find any literature online to specify how one
should evaluate IRR when the full cost of an investment is financed with
debt. I suspect you have to run an IRR assuming you paid cash up front, and
run an IRR separately on the debt, and net the two.