Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Numbers: Simulate XIRR annualized rate of return Apps
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:
  • 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:
    =SUMIF(Cash Flow Summary::$A,A2,Cash Flow Summary::$B)
    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.
  • 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:
    =(IRR(Cash Flow Daily::Amount,(1+Guess)^(1/Days in Year)-1)+1)^Days in Year-1
    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).
  • (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.
Now you can add any number of days with inflow/outflow to the summary table and the return value is updated at once. As a check, for most cash flow streams, the computed annualized return will lie between the two values:

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
Pretending the total net inflow occured at the end of the year, reducing the final account value:
(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.]
  • Currently 3.75 / 5
  You rated: 4 / 5 (4 votes cast)

Numbers: Simulate XIRR annualized rate of return | 1 comments | Create New Account
Click here to return to the 'Numbers: Simulate XIRR annualized rate of return' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Numbers: Simulate XIRR annualized rate of return
Authored by: Jaywalk3r on Jan 10, '12 12:49:51PM

I've completely replaced Excel with Numbers. In my own experience, Numbers works well for any task for which a spreadsheet is the best tool. Having said that, for anything much more complex than a checkbook register, I find that a more powerful tool, such as R, is far better suited than a spreadsheet. Spreadsheet apps are, ironically, poorly suited for doing mathematics. For one of many possible examples, typing "-3^2" (without quotes) into either Excel 2008 or Numbers '09 incorrectly returns 9, when the correct value is -9.

[ Reply to This | # ]