This discussion is locked
v474
Level 1 (0 points)

Q: IRR, looking for an equivalent to the XIRR in Excel.

Hi,
when working with irregular dates in cash-flows I used XIRR and XNPV in excel.

Could not find an equivalent in Numbers 08-09 so have to create dailies what for 10 year projects is a bit cumbersome and prone to errors when converting daily interest to annuals.
Anyone found a shortcut (equivalent to the Excel functions) for this in Numbers?
TX.

iMac, Mac OS X (10.5.6), TC with software issues

Posted on May 25, 2009 2:51 AM

by Badunit,Solvedanswer
Badunit Badunit
Level 6 (11,705 points)
iTunes
A:
I have not used the financial functions so bear with me if I'm way off base. XIRR is not available so you seem to be forced into using daily data, the vast majority of which will be zeros. One of your concerns is errors due to the large table of data you'll need. So here is my idea on how to eliminate the errors by having it create the "dailies" for you from your much shorter list of cash flow data:

1. Create a new sheet with a new table. Column B of this table will be dates; one row for every day for the entire period (yes, about 3600 rows for 10 years). The easy way to create this long table is to make it a two row table at first, put a date in the header cell B1, enter the formula =B1+1 in B2, then you can drag down the handle at the bottom left corner of the table to make it 3600 rows long with the formula in each row (do the dragging after the next step).

2. Use the SUMIF function in column C to automatically fill in the dollar figures from your much shorter original table. As an example, let's say your cash flow data is in Table 1 on Sheet 1. The dates are in cells B2-F2 and the dollars are in cells B3-F3. The formula would be:

cell C2 =SUMIF(Sheet 1 :: Table 1 :: $B$2:$F$2,B2,Sheet 1 :: Table 1 :: $B$3:$F$3)

This will copy the dollars for that date from your original table to this new one.

Now do the drag down to make a 3600 row table

3. Use the IRR function on column C of this new table.

This was actually harder to write up than to do. I hope I made it clear enough to understand.

Posted on May 25, 2009 6:48 PM

Close
v474

Q: IRR, looking for an equivalent to the XIRR in Excel.

  • All replies
  • Helpful answers

  • by KOENIG Yvan,

    KOENIG Yvan May 25, 2009 11:40 AM in response to v474
    Level 8 (41,790 points)
    May 25, 2009 11:40 AM in response to v474
    I'm really wondering if users are able to read.

    In the iWork Formulas and Functions User Guide, I searched the function IRR.
    It is described pages 118/119.
    At the end of the description everybody may read:

    For related functions and additional information, see:
    “MIRR” on page 121
    “NPV” on page 125


    As I learnt the way to read, I swirtched to page 121 and saw:


    MIRR
    The MIRR function returns the modified internal rate of return for an investment that
    is based on a series of potentially irregular cash flows


    I'm not interested by financial functions but was able to find this one which resemble to what you are asking for.

    I really don't understand why someone interested by such feature was unable to apply the protocol which I used.

    Page 125, we may read:


    NPV
    The NPV function returns the net present value of an investment based on a series of
    potentially irregular cash flows that occur at regular time intervals.


    Is it surprising if I am tempted to write that laziness is ruling the world of users?

    Yvan KOENIG (from FRANCE lundi 25 mai 2009 20:40:33)

  • by v474,

    v474 v474 May 25, 2009 2:39 PM in response to KOENIG Yvan
    Level 1 (0 points)
    May 25, 2009 2:39 PM in response to KOENIG Yvan
    Wellwell Yvan,

    my question is about the irregularity of dates on which cash flows occur.
    The Xirr and Xnpv functions in Excel address that issue

    Your elaborate comment refers to irregularity of amounts.
    Thank you anyway, I am glad that you are, like me, a good reader of the Manual.

  • by Badunit,Solvedanswer

    Badunit Badunit May 25, 2009 6:48 PM in response to v474
    Level 6 (11,705 points)
    iTunes
    May 25, 2009 6:48 PM in response to v474
    I have not used the financial functions so bear with me if I'm way off base. XIRR is not available so you seem to be forced into using daily data, the vast majority of which will be zeros. One of your concerns is errors due to the large table of data you'll need. So here is my idea on how to eliminate the errors by having it create the "dailies" for you from your much shorter list of cash flow data:

    1. Create a new sheet with a new table. Column B of this table will be dates; one row for every day for the entire period (yes, about 3600 rows for 10 years). The easy way to create this long table is to make it a two row table at first, put a date in the header cell B1, enter the formula =B1+1 in B2, then you can drag down the handle at the bottom left corner of the table to make it 3600 rows long with the formula in each row (do the dragging after the next step).

    2. Use the SUMIF function in column C to automatically fill in the dollar figures from your much shorter original table. As an example, let's say your cash flow data is in Table 1 on Sheet 1. The dates are in cells B2-F2 and the dollars are in cells B3-F3. The formula would be:

    cell C2 =SUMIF(Sheet 1 :: Table 1 :: $B$2:$F$2,B2,Sheet 1 :: Table 1 :: $B$3:$F$3)

    This will copy the dollars for that date from your original table to this new one.

    Now do the drag down to make a 3600 row table

    3. Use the IRR function on column C of this new table.

    This was actually harder to write up than to do. I hope I made it clear enough to understand.

  • by v474,

    v474 v474 May 26, 2009 1:31 AM in response to Badunit
    Level 1 (0 points)
    May 26, 2009 1:31 AM in response to Badunit
    Jan, thank you for your creative thinking.
    I tried to do it with a complicated lookup function but in that case the payment data set must be (manually) sorted by value date, best of all, I think your solution uses a lot less processor time.

    Great workaround, Thank you.