Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using MIRR, if cash flows are monthly, should rate be / 12?
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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 a =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. |
#4
|
|||
|
|||
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 a =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. |
#5
|
|||
|
|||
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 a =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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the PMT function to calculate using 360 days not 365 | Excel Worksheet Functions | |||
Effective Annual Interest Rate | Excel Worksheet Functions | |||
MIRR Function sensitivity to Finance Rate | Excel Worksheet Functions | |||
How do i create an automatic monthly cash flow from lists of cost. | Excel Discussion (Misc queries) | |||
How do you calculate rate of return on monthly cash flows | Excel Worksheet Functions |