What affect do new cash additions have when calculating internal rate of return?

[chuckstuff]

I use the XIRR function on the OpenOffice spreadsheet to calculate my annualized rates of return for a stock portfolio that dates back to 2003. The top of column A is the original cash investment, and everything else in that column reflects either new money added or an occasional cash withdrawal. The last entry in the column is the present value of the portfolio expressed as a negative. Column B lists the dates corresponding to the entries in Column A. An example of the formula used is =XIRR(A1:A10;B1:B10)

Wanting to see how the portfolio has done since 2009, I used the portfolio value on 1/1/09 at the top of Column A ($55,332). There were a couple of small additions subsequent, but then in 2014 there was a large cash addition of approximately $100,000. The closing value on 12/31/15 was $296,493.15. This was expressed as a negative at the bottom of Column A.

Using XIRR, the calculation said that my internal rate of return was 13.51%. This is several percentage points above what I expected to achieve, so I wanted to know if the large cash addition is somehow skewing the results. It's possible that I had several really good years, but the realist in me tends to think that I'm missing something--even though I've always operated under the assumption that XIRR somehow takes into account the cash additions when calculating the portfolio's return.

Is there another calculation I should be doing? Because it seems to me that my cash additions will always artificially inflate the portfolio's performance.

Thanks for your time everyone...

 

[chuckstuff]

Your example can be looked at this way:
$55,332 is deposited in an account.
5 years later, a deposit of $100,000 is made.
At end of 7th year, account is worth $296,493
What is the effective annual rate?

This rate is 16.4675% (still more than you quote!).

Thanks Denis. I bow to the superior intellect.

I must be on a lucky streak, but I'm sure means regression will take care of that soon enough.

 

[Ishuda]

I use the XIRR function on the OpenOffice spreadsheet to calculate my annualized rates of return for a stock portfolio that dates back to 2003. The top of column A is the original cash investment, and everything else in that column reflects either new money added or an occasional cash withdrawal. The last entry in the column is the present value of the portfolio expressed as a negative. Column B lists the dates corresponding to the entries in Column A. An example of the formula used is =XIRR(A1:A10;B1:B10)

Wanting to see how the portfolio has done since 2009, I used the portfolio value on 1/1/09 at the top of Column A ($55,332). There were a couple of small additions subsequent, but then in 2014 there was a large cash addition of approximately $100,000. The closing value on 12/31/15 was $296,493.15. This was expressed as a negative at the bottom of Column A.

Using XIRR, the calculation said that my internal rate of return was 13.51%. This is several percentage points above what I expected to achieve, so I wanted to know if the large cash addition is somehow skewing the results. It's possible that I had several really good years, but the realist in me tends to think that I'm missing something--even though I've always operated under the assumption that XIRR somehow takes into account the cash additions when calculating the portfolio's return.

Is there another calculation I should be doing? Because it seems to me that my cash additions will always artificially inflate the portfolio's performance.

Thanks for your time everyone...

I tend to add a check column to calculations like this which would result in an added column. If the deposits/withdrawals/interest are not on the same day [and even if they are since that may change], I would use a daily interest, i.e. 13.51% annual return is compounded daily growth rate of 1.0003472 [0.03472%]. Thus starting with the initial balance in the 'balance' column, the new 'balance' for each transaction would be
balance2 = round(1.0003472^(date2-date1)*balance1+transaction,2)
where date2 is the transaction date, date1 is the last transaction date, and balance1 is the 'balance' on date1. Of course the actual balances will probably only match at the beginning and end and may suffer a little from round-off.

This, in fact is probably about how XIRR works. That is, go through the list performing the calculations for a guesstimated rate of return. If it isn't correct, correct your guesstimate according to algorythm. Repeat until you are close enough or have tried long enough.

 

[Ishuda]

Not sure what you mean with that analysis, Ishuda.

If looking over a 7year period to approximate a yield,
I don't see what benefit "daily" calculations will contribute.

Calculating on daily basis versus monthly basis results
in very little affecting the big picture....

You are right if you want an estimate within 'a few percent'. However, it is easier to handle on an automatic basis if you have a lot of deposits/withdrawals on an irregular basis if you want to actually reflect the deposit day. Most of the formulas are just a copy and paste.