Creating an Excel range out of disjoint cells

Question

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?

Answer 1

I think you are probably stuck with the workaround that you identified - show the ending balance as a (notional) withdrawal at the end of the month (or period) - and a deposit of the prior month's ending balance as a (notional) deposit at the beginning of the month. Unfortunately, I think you are making another subtle error: IRR assumes cash flows at even time periods - I don't see that you have that, unless the "0" entires are to maintain even periods (weekly?). To calculate your return if cash flows do not occur at regular intervals (especially if you are using this over longer time periods like a year or more), you will have to use the XIRR function, which takes two value ranges - the date of the transaction and the cash flow.

* Update *

I KNEW I had worked on something like this before - it just took a while to find it! Have a look at this thread from Mr Excel. The take-away is that you load the cash flows and dates, including the ending balance value and date, into two arrays, and then evaluate the XIRR of those two arrays. The post with a UDF to do that is about the third one down the list. The immediately prior post asserts that IRR will accept a non-continuous range - I have never tested that, but it is worth a try!