XIRR is a classic and widely used Excel function which is very useful for calculating rates of return on investments given an initial and final value and a series of cash inflows and outflow in irregular amounts at irregular intervals (e.g. Jan. 15, invested $200, Mar 12, withdrew $100, ...). Although iWork '09 Numbers doesn't have this function, it can be simulated, as described below.
XIRR takes as input a series of dates and cash flow amounts, and computes the annualized rate of return over the time period with the correct allowance for the time impact of the transactions (earlier transactions have had more time to make an impact). As a typical application, you might want to calculate your yearly rate of return given an irregular set of investments and withdrawals, and that's the example we'll consider here. The Numbers IRR function calculates a per-interval return rate given an irregular series of inflows/outflows at fixed intervals. Here's how to use it to simulate XIRR:
Pretending the total yearly net inflow occurred at the beginning of the year, increasing the initial account value:
Pretending the total net inflow occured at the end of the year, reducing the final account value:
[crarko adds: I haven't tested this one. I am curious to know how many of you have successfully replaced Excel with Numbers. I'd guess that would be the most difficult piece of Office to do without.]
XIRR takes as input a series of dates and cash flow amounts, and computes the annualized rate of return over the time period with the correct allowance for the time impact of the transactions (earlier transactions have had more time to make an impact). As a typical application, you might want to calculate your yearly rate of return given an irregular set of investments and withdrawals, and that's the example we'll consider here. The Numbers IRR function calculates a per-interval return rate given an irregular series of inflows/outflows at fixed intervals. Here's how to use it to simulate XIRR:
- Create a Cash Flow Summary Table with columns Date, Amount (and optionally Notes). For every cash event flow in a year, enter the date it occurred on (Numbers is smart and will recognize and normalize any dates you enter), and the net amount for that day: positive for inflows (investments) and negative for outflows (withdrawals).
- Create another Cash Flow Daily table with only two columns: Date and Amount. In the first row of this column, enter the date as 'Start:' and for the amount, the starting account value, as a positive number. In the second row, enter the date 1/1/year for the year of interest. Select it and pull down from the circle at lower right to create a date entry for each and every day, through 12/31/year.
- Starting on the date 1/1 (e.g. in cell B2), enter in the Amount column the formula:
Where Cash Flow Summary is the name of the table listing the cash flows. Select this cell and drag it down to copy this over the rest of the days in the year. This will select any cash flow amounts from the Cash Flow Summary table above on the given date.
=SUMIF(Cash Flow Summary::$A,A2,Cash Flow Summary::$B)
- On the last day, 12/31, edit the formula, subtracting the final balance of the account. So, if there were no investments or withdrawals to the account on that day, this cell will contain the negative final account balance. You might consider referencing the initial and final account values from another, more convenient location.
- Anywhere else, you can now compute the annualized rate of return as:
Where Guess is an (annualized) initial guess for the return rate (like .05, or 5%), and Days in Year is the number of days in the year (365 or 366 for leap years).
=(IRR(Cash Flow Daily::Amount,(1+Guess)^(1/Days in Year)-1)+1)^Days in Year-1
- (Optional, for appearance) Select all the rows of the Cash Flow Daily table except the first and last, click on the black triangle which appears at left for any of them, and Hide Selected Rows.
Pretending the total yearly net inflow occurred at the beginning of the year, increasing the initial account value:
Final Value/(Initial Value+Net Inflow)-1
(Final Value-Net Inflow)/Initial Value-1
[crarko adds: I haven't tested this one. I am curious to know how many of you have successfully replaced Excel with Numbers. I'd guess that would be the most difficult piece of Office to do without.]
•
[6,464 views]

