It would be easier to just assume I'm not saving enough for retirement...
December 16, 2007 11:11 PM Subscribe
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 1 Dec 7 Dec 14 Dec 21 Dec 28 Balance
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.
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:
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.
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
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
posted by Chris4d at 10:31 PM on December 19, 2007
This thread is closed to new comments.
posted by spacewrench at 11:13 PM on December 16, 2007