How do I calculate IRR using months, not years

Question

Level 1

5 points

How do I calculate IRR using months, not years

When using the IRR funtion in Numbers for Mac (internal rate of return), how do I let the formula know that my time period is months and not years? In the Excel function, there is an extra arugment in the function that allows you to select the dates and the cash flow for that date. In the Numbers version, you can only select the cash flows, there is no variable for the dates. I believe that the function in Numbers automatically assumes you are using years when in fact my cash flows are represented in months (monthly rent from properties). The function description in Numbers clearly states that you can use months so I must be missing something. The examples that Numbers provides for using the IRR function all assume the cash flows are in years.

For non-finance people, the time frame makes an enourmous difference. If you invest $1.00 with me today and a year from now I give you back $2.00, your return on investment is 100% (you doubled your money) and your IRR is 100% because it took exactly 1 year. But if I wait five years to give you the $2.00, the return on investment is still 100% (still doubled your money) but the IRR is 20% because it took five years. Not as good a deal for you and the IRR represents that. In these scenarios, I can calcullate the IRR using Numbers. But what if I give you the money back after 6 months? Or after 18 months? Or give you .25 cents each month for 8 months? The IRR will be MUCH different. How do I tell Numbers my intervals are not in years?

Thanks for any help! For two years I've been doing all my spreadsheets in Numbers and then having to calculate the resulting IRR in Excel!! Yuck.

Numbers for Mac

Posted on Jun 13, 2012 6:59 AM

Reply

Answer 1

Jun 28, 2013 8:45 AM in response to FinancialEngineer

This is an edit of the last post where the EFFECT function values should be as follows

Using iWorks EFFECT function

=EFFECT( 0.016231 * 12, 12 )

= 0.213135

= 21.3135%

If you were using the IRR function, Excel syntax allows for arrays so let me put in the IRR call in EFFECT function

=EFFECT( IRR( { -100, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 } ) * 12, 12 )

= 0.213135

= 21.3135%

Message was edited by: FinancialEngineer

Reply

Answer 2

Jun 13, 2012 12:27 PM in response to dBig

=A*12

Jerry

Reply

Answer 3

Jun 13, 2012 7:17 AM in response to dBig

DB,

I hope a simple explanation of how IRR, and all the other financial functions, work will clear this up for you. Whether you are working in Numbers or with a book of tables or with a financial calculator, the only thing that matters is the number of periods and whether events happen at the beginning or end of a period. Your periods can be seconds, days, months, years, etc. All the compounding will be done accurately if your rates are consistent with how you think of the period. If your period is a month, then express your interest rate in terms of a month and you're done (that would roughly be 1/12 of the annual rate).

Jerry

Reply

Answer 4

Jun 13, 2012 10:20 AM in response to dBig

The answer is that XIRR (in excel) allows for NON-PERIODIC durations and takes as arguments the amount range and a date range. Numbers does not have an exquivalent function. IRR is periodic so it does not take an date ranges as an argument (nor does MIRR).

Reply

Answer 5

Barry

Level 9

60,023 points

Jun 13, 2012 10:55 AM in response to dBig

"That seems like such a major oversight!"

Then it's something you should bring directly to Apple's attention. Provide Numbers Feedback, in the Application menu ("Numbers" menu in Numbers) gives you the route to request inclusion of XIRR in the iWork function set. The link will take you to the same location as the menu item.

Regards,

Barry

Reply

Answer 6

dBig Author

Level 1

5 points

Jun 13, 2012 12:44 PM in response to Jerrold Green1

Yeah, unfortunately that isn't accurate. In your excel example, if you put dates next to the inputs and intially make the dates one year intervals, you'll get the 1.62%, which is correct. And yes, that can be duplicated in Numbers with the IRR funtion. But if you change the dates to one month intervals in your excel file and use XIRR, you'll get 21.31% which is the correct number. You might be able to duplicate 19.48% with both programs, but that number is incorrect regardless of the program due to the way you arrived there.

I think I stated my original question incorrectly: I guess what I need is an equilivent in Numbers to Excel's XIRR function and it's not looking like there is one.

Reply

Answer 7

Jun 14, 2012 5:50 AM in response to dBig

dB,

On further thought, I think the error, as you are calling it, is simply a difference in how the interest is represented. When I said above: " If your period is a month, then express your interest rate in terms of a month and you're done (that would roughly be 1/12 of the annual rate).", the "roughly" I was referring to is the difference between simple annual rate and compunded annual rate, which is a choice you can make as long as you specify how you are representing the rate. My 1.6231% monthly rate can be represented as a simple annual rate of 19.48% or an effective annual rate of 21.31% with monthly compounding.

Jerry

Reply

Answer 8

Jun 26, 2013 11:42 AM in response to dBig

Monthly IRR = 1.6231%

Annual IRR = 1.6231% * 12 = 19.4772%

Annualized IRR = (1 + 1.6231% )^12 - 1

= (1.016231)^12 - 1

= 1.213135 - 1

= 0.213135

= 21.3135%

Using iWorks EFFECT function

=EFFECT( 0.016231, 12 )

= 0.213135

= 21.3135%

Reply

Answer 9

dBig Author

Level 1

5 points

Jun 13, 2012 9:48 AM in response to Jerrold Green1

Thanks Jerrold, that makes sense. But I'm still having trouble duplicating a correct answer in Numbers. Lets say we have 12 periods represented in cells A1:A12. The first period is a negative cashflow of -$100 and then the following 11 periods have positive cash flow of $10 (for cumulative gain of $10 over the entire 12 periods). When I use XIRR in excel and have the periods represent twelve years, the IRR is 1.62%. When I use the Numbers formula IRR(A1:A12), I also get 1.62%. When I change the periods in excel to twelve months, the IRR becomes 21.31%. What would the Numbers formula be to get the same result? I tried IRR(A1:A12,10%/12) and various alternatives to that, and even the MIRR function and can't duplicate excels' XIRR result of 21.31%, which I know is correct.

Thanks so much for your help. Maybe this way you can just reply with the exact correct formula for this scenario?

Reply

Answer 10

Jun 13, 2012 12:17 PM in response to dBig

dB,

IRR works identically in Numbers and in Office. If that's not the function you want, that's another issue. Here's your IRR calculation in Office (LibreOffice):

And here it is in Numbers:

If your payment series is in uniform time periods, why would you use anything besides IRR?

Jerry

Reply

Answer 11

Jun 13, 2012 4:57 PM in response to dBig

I have no way to know what information you are giving the XIRR function, so I can't judge what a correct answer would be for that calculation. Would you mind sharing the full details of the data you used in your XIRR function example?

Jerry

Reply