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
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:
... so that it sees a final 'withdrawal' equal to my current balance?