Numbers: Simulate XIRR annualized rate of return

Jan 06, '12 07:30:00AM

Contributed by: jdsmith

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:

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

Comments (1)


Mac OS X Hints
http://hints.macworld.com/article.php?story=20120103143716743