Creating an Excel range out of disjoint cells

I have a spreadsheet with my investment accounts, showing for each month the deposit or withdrawals that happened that month and the end-of-month balance.

I want to use the IRR function to calculate my rate of return. I can give it the column of deposits and withdrawals, and it'll calculate the return assuming a zero balance at the end. For example this:

``````Deposit 100
Withdraw 10
``````

Will show a -90% rate of return. However, if I still have \$95 in the account, the real rate of return is +5%.

My table looks something like this:

``````Deposits  Balance
100       100
-10       95
0         103
0         98
100       215
``````

I can add a column to the right that is "Rate of Return", with a formula of `"=IRR(\$A\$2:A3)"`

However, again, this gives me inaccurate data because the IRR function assumes that the final balance is zero and only accepts a single range. Is there a way to do something like this:

``````=IRR(ConcatenateRange(\$A\$2:A3, B3))
``````

... so that it sees a final 'withdrawal' equal to my current balance?

-
Maybe I am misunderstanding. A 100 balance less 10 is not 95 so you must have additional deposits accounting for the the difference. Is interest accumulating but is not deposited? – datatoo Jul 29 '11 at 19:34
The 'Deposits' column is only money I've put into the account. Money that the account earns on its own, i.e. interest, investment returns, dividends, and the like, shows up in the 'Balance' table. I can manually do it by putting the negative of the balance at the bottom of the 'Deposits' column, but was hoping for an automated solution. – Aric TenEyck Jul 29 '11 at 22:11
if your online balance is incorrect though, would you want it to automatically adjust? Or would you want to reconcile with transactions showing the additions? – datatoo Jul 29 '11 at 22:14