It would be easier to just assume I'm not saving enough for retirement...

December 16, 2007 11:11 PM

Excel/OpenOffice: How can I use XIRR to show an estimated percentage return based on account deposits/withdrawals/balances?

I keep a spreadsheet with information about a brokerage account. I add entries when I deposit money, when I withdraw money, and occasionally I check the balance and put that into the spreadsheet too. For example:
Dec 1Dec 7Dec 14Dec 21Dec 28Balance
100
101
99
102
103
Transaction
+100
-3
+5

I'd like to add another row that gives the equivalent interest rate of a simple savings account that had the same transactions and balances, up to that date. I know that the XIRR function does what I want, but the last cell in the VALUES array has to show a "hypothetical" withdrawal of the balance at the corresponding end date. As far as I can tell, this means that if I want a "To Date" value for each date in the spreadsheet, I need a triangular array based on the Balance and Transaction rows -- the first row of the array shows the first two dates, the second row of the array shows the first three dates, the third row of the array shows the first four dates, and so on. The final cell of each of the triangular-array rows would be the hypothetical withdrawal amount. Then I'd do XIRR on each of the triangular-array rows, and put the result under the corresponding date. I expect that this would show that sometimes I've had a great return, and sometimes I've lost money, etc; the value under today's date would show the time-weighted average from the beginning of the spreadsheet.
Is there an easier way to say this in Excel-speak? It would be trivial in a real programming language, or if the XIRR function had a way to pass in a final date/value pair, but I've reached my spreadsheet-understanding limit.

posted by spacewrench to computers & internet (3 comments total)

Oh noes! <tables> don't work, except in Preview! Apologies for the mess there; I hope you can still figure out what I'm asking.
posted by spacewrench at 11:13 PM on December 16, 2007

If I understand your question right, you want to know how to handle the fact that you must have a withdrawal at the ending date to compute the XIRR. You don't need a triangular array. On the date you want to compute the XIRR, place a withdrawal and compute over the range up to and including that withdrawal. On the next row (or column, depending on which way you are incrementing dates), add an identical deposit back in on the same date, positive this time. Then the next month or quarter do the same. The withdrawal and deposit on the same day cancel out in computations for subsequent periods and are ignored over ranges that include both. I hope this is what you were asking.
posted by JackFlash at 12:05 AM on December 17, 2007

JakcFlash, I couldn't get your method to work... logically it ought to, though. Anyway spacewrench, I can't think of a better way to do what you're asking; I was hoping that there was a way to tack on the withdrawal via union operators within the function arguments, but it doesn't seem to work.
posted by Chris4d at 10:31 PM on December 19, 2007