I should say that I'm not married to XIRR, I just want to calculate the return on a running basis in a column next to dates, deposits and balances. Here's a screenshot of a simplified spreadsheet in LibreOffice Calc (f/k/a OpenOffice.org Calc; I believe Google Apps is based on their code base):
In F2 I've got the formula I'd use to calculate the return over the entire timespan. Note that I had to include the value at the end as a negative cashflow. This makes it so that (a) I can't easily keep adding rows to the bottom and (b) I can't get a return% value for each row in column D.
Is there a way to include that final cashflow in the formula? Some kind of ad hoc array that can include the value from column C?
I have another spreadsheet where I've duplicated columns A and B (including the final negative cashflow) for every year that I want a return value for. This approach is tedious and error prone.
In case it matters, there are only deposits, never withdrawals -- I'm using this for a retirement account, and I've got a ways to go before I take anything out...