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.
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
iTunes
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.
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