Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer

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!

share|improve this answer
    
Thanks for the info. And yes, each row is actually the sum of a month's deposits and withdrawals, with zeroes as necessary. –  Aric TenEyck Nov 2 '11 at 13:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.