Using MIRR, if cash flows are monthly, should rate be / 12?

[peak10]

Question on MRR function. Want to make sure the interest rate is correct. On
monthly cash flow stream, should the rate be divided by 12 as in thr RATE
function.

[Fred Smith]

Yes. As with almost all financial functions (XNPV and XIRR being the
exceptions), the period for the payments must match the period for the rate.
If you values are monthly, your interest rates must be monthly. Remember, if
you divide by 12, you're assuming monthly compounding. If you want to assume
annual compounding (eg, the effective annual rate), you'll need to convert
your rate using EFFECT.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"peak10" <peak10@discussions.microsoft.com> wrote in message
news:727A693E-99B6-4411-AF3A-9ED24D13DA71@microsoft.com...
> Question on MRR function. Want to make sure the interest rate is correct.
> On
> monthly cash flow stream, should the rate be divided by 12 as in thr RATE
> function.

[SongBear]

NOTE: Anybody else can chime in!
peak10, the first thing you should do is, in this forum, search for mirr.
you will only get three threads. Then go to the oldest thread and read the
replies, esp. those by alan and harlan grove.
When you have satisfied yourself that the function is accurate enough for
your needs, here is what i discovered just doing some simple tests:
If you do use monthly income, divide the rates (both of them), then multiply
the resultant mirr by 12 to get the annual rate.
the answer will only be ballparkishly close, like below.
I used a 3 yr cash inflow with a one-time outflow in year zero.
I then divided the annual income of the first problem by 12 and made 36 of
them as monthly cashflows. I tried several times to get the same result as
the MIRR with the 3 annual cash flows, the way i outlined above was the
closes I got. In the example below, the formulas are:
=MIRR($F$53:$F$89,$G$57/12,$G$58/12) for the 36 monthly cash flows (in F51)
=12*F51 to multiply it into
an annual amount
=MIRR(G53:G56,G57,G58) for the 3 year cash flows


2.70% 32.43% MIRR of 36 monthly inflows * 12
35.40% MIRR of three year inflows
-10000 -10000
1 625 7500
2 625 7500
3 625 7500
4 625 0.2 frate
5 625 0.1 rrate
6 625
7 625
8 625
9 625
10 625
11 625
12 625
13 625
14 625
15 625
16 625
17 625
18 625
19 625
20 625
21 625
22 625
23 625
24 625
25 625
26 625
27 625
28 625
29 625
30 625
31 625
32 625
33 625
34 625
35 625
36 625

Hope this helps
SongBear




"peak10" wrote:

> Question on MRR function. Want to make sure the interest rate is correct. On
> monthly cash flow stream, should the rate be divided by 12 as in thr RATE
> function.

[SongBear]

http://www.microsoft.com/office/comm...aec&sloc=en-us

That is the url of the thread you should read about the accuracy of the MIRR
function.

"SongBear" wrote:

> NOTE: Anybody else can chime in!
> peak10, the first thing you should do is, in this forum, search for mirr.
> you will only get three threads. Then go to the oldest thread and read the
> replies, esp. those by alan and harlan grove.
> When you have satisfied yourself that the function is accurate enough for
> your needs, here is what i discovered just doing some simple tests:
> If you do use monthly income, divide the rates (both of them), then multiply
> the resultant mirr by 12 to get the annual rate.
> the answer will only be ballparkishly close, like below.
> I used a 3 yr cash inflow with a one-time outflow in year zero.
> I then divided the annual income of the first problem by 12 and made 36 of
> them as monthly cashflows. I tried several times to get the same result as
> the MIRR with the 3 annual cash flows, the way i outlined above was the
> closes I got. In the example below, the formulas are:
> =MIRR($F$53:$F$89,$G$57/12,$G$58/12) for the 36 monthly cash flows (in F51)
> =12*F51 to multiply it into
> an annual amount
> =MIRR(G53:G56,G57,G58) for the 3 year cash flows
>
>
> 2.70% 32.43% MIRR of 36 monthly inflows * 12
> 35.40% MIRR of three year inflows
> -10000 -10000
> 1 625 7500
> 2 625 7500
> 3 625 7500
> 4 625 0.2 frate
> 5 625 0.1 rrate
> 6 625
> 7 625
> 8 625
> 9 625
> 10 625
> 11 625
> 12 625
> 13 625
> 14 625
> 15 625
> 16 625
> 17 625
> 18 625
> 19 625
> 20 625
> 21 625
> 22 625
> 23 625
> 24 625
> 25 625
> 26 625
> 27 625
> 28 625
> 29 625
> 30 625
> 31 625
> 32 625
> 33 625
> 34 625
> 35 625
> 36 625
>
> Hope this helps
> SongBear
>
>
>
>
> "peak10" wrote:
>
> > Question on MRR function. Want to make sure the interest rate is correct. On
> > monthly cash flow stream, should the rate be divided by 12 as in thr RATE
> > function.

[SongBear]

Like Fred Said:
After you have read the MIRR accuracy thread, read this kb file, it says
what Fred says, using monthly cashflows causes it to assume monthly
compounding of interest.
http://support.microsoft.com/default...b;en-us;214150

Another thing to consider: due to the time value of money, when I got a
slightly lesser value for MIRR using monthly cash inflows as opposed to
annual cash inflows, it may actually make some sense.

If the function assumes that all cash flows are at the begining of a period,
i.e. the initial outflow is on jan 1, year 0, and the first inflow is on jan
1, year 1, then when you do monthly cash flows, only one 12th of that is
coming in on jan 1, year 1, the next one 12th is on feb 1, year 0, etc.

In the annual model, you get your money and re-invest it sooner. Well, in
the monthly model, it may assume the year 0 outflow was only one month
before, on dec 1, year 0. But you would still get your money and re-invest it
sooner with the annual model, which would give you a slightly higher MIRR for
the annual model.
Hope this helps
SongBear


"SongBear" wrote:

> http://www.microsoft.com/office/comm...aec&sloc=en-us
>
> That is the url of the thread you should read about the accuracy of the MIRR
> function.
>
> "SongBear" wrote:
>
> > NOTE: Anybody else can chime in!
> > peak10, the first thing you should do is, in this forum, search for mirr.
> > you will only get three threads. Then go to the oldest thread and read the
> > replies, esp. those by alan and harlan grove.
> > When you have satisfied yourself that the function is accurate enough for
> > your needs, here is what i discovered just doing some simple tests:
> > If you do use monthly income, divide the rates (both of them), then multiply
> > the resultant mirr by 12 to get the annual rate.
> > the answer will only be ballparkishly close, like below.
> > I used a 3 yr cash inflow with a one-time outflow in year zero.
> > I then divided the annual income of the first problem by 12 and made 36 of
> > them as monthly cashflows. I tried several times to get the same result as
> > the MIRR with the 3 annual cash flows, the way i outlined above was the
> > closes I got. In the example below, the formulas are:
> > =MIRR($F$53:$F$89,$G$57/12,$G$58/12) for the 36 monthly cash flows (in F51)
> > =12*F51 to multiply it into
> > an annual amount
> > =MIRR(G53:G56,G57,G58) for the 3 year cash flows
> >
> >
> > 2.70% 32.43% MIRR of 36 monthly inflows * 12
> > 35.40% MIRR of three year inflows
> > -10000 -10000
> > 1 625 7500
> > 2 625 7500
> > 3 625 7500
> > 4 625 0.2 frate
> > 5 625 0.1 rrate
> > 6 625
> > 7 625
> > 8 625
> > 9 625
> > 10 625
> > 11 625
> > 12 625
> > 13 625
> > 14 625
> > 15 625
> > 16 625
> > 17 625
> > 18 625
> > 19 625
> > 20 625
> > 21 625
> > 22 625
> > 23 625
> > 24 625
> > 25 625
> > 26 625
> > 27 625
> > 28 625
> > 29 625
> > 30 625
> > 31 625
> > 32 625
> > 33 625
> > 34 625
> > 35 625
> > 36 625
> >
> > Hope this helps
> > SongBear
> >
> >
> >
> >
> > "peak10" wrote:
> >
> > > Question on MRR function. Want to make sure the interest rate is correct. On
> > > monthly cash flow stream, should the rate be divided by 12 as in thr RATE
> > > function.

[sturdona]

Actually the "divide by 12 and then multiply by 12" gives significant error (>10%)

It's better to use this:

=(MIRR(monthly cash flows,%/12,%/12)+1)^12-1

Your error will be much smaller.