I've been posting a lot of different analyses of investment performance recently. They all rely on the same underlying data which are in fact fairly easy to compute. I have the impression that people think it is harder to compute that data than it is. Several trading blogs I read report daily trading results and several PF bloggers report some annual investment performance figures, but I haven't seen anyone else compute the kind of statistics that I've been discussing here. Let me know if you know other blogs that are computing longer term investment performance statistics.
What you need to know is the value of the accounts you want to include in your computation at the end of each month and how much money you put into and out of the accounts. I use my total net worth and calculate how much I earn from salary etc. and how much I spend each month. But you can look at just investing and trading accounts and ignore checking accounts, credit cards etc.
OK, so here is a simple example. Let's assume that Yoyo has a brokerage account and a retirement account. Yoyo checks up her statements for the two accounts for the last couple of months:
31 January 2007:
Brokerage: $35,500
Retirement: $93,000
Total: $128,500
28 February 2007:
Brokerage: $34,400
Retirement: $94,200
Total: $128,600
She also collects the following information:
February retirement contributions: $550
15 February put $500 into brokerage account
28 February withdrew $600
That's all the data needed. Now let's help Yoyo do the calculations for February's rate of return.
Investment gain in brokerage account = $34,400-$35,500-$500+$600 = -$1000
or: monthly change in account value - net contribution to account.
And for the retirement account:
$94,200-$93,000-$500 = $700
So Yoyo's loss for the month is the sum of these two investment returns = $700-$1000 = -$300.
To get the rate of return we divide this number by the value of her accounts at the beginning of the month:
ROR = -300/128,500 = -0.23%
The hard bit is doing this for every month you want to include in your statistics. I'll explain some basic statistics in an upcoming post. Please let me know if anything is unclear here!
5 comments:
Wouldn't you have to account for exactly when the additions/withdrawals have been made in the example you gave?
eg. The IRR will be different for the two cases below:
Case A
1/1/07 opening balance $10,000
3/1/07 contribution $ 1,000
28/1/07 withdrawal -$ 700
31/1/07 end balance $10,500
XIRR()=24.85%
Case B
1/1/07 opening balance $10,000
23/1/07 contribution $ 1,000
28/1/07 withdrawal -$ 700
31/1/07 end balance $11,000
XIRR()=26.66%
You have to use the XIRR function instead of the IRR function if the you additions/withdrawals at irregular periods.
Obviously as the extra $1000 contribution was invested for a smaller part of the month in case B the rate of return had to be higher to achieve the same end value.
I've also never been quite clear on how to correctly handle gearing in IRR calculations. Would I count my interest payments as contributions and the interest charges as withdrawals (or does their impact automatically work itself out in the lower end balance)? Do the loan amounts count as contributions and repayments as withdrawals? As I'm not sure of the correct calculation method I've never gone to the trouble of doing more than an annual "back of the envelope" style calculation to check how I'm tracking against my projections.
I'd hate to spend time making complicated spreadsheets and graphs and end up with very precise but inaccurate stats!
Regards
http://enoughwealth.com
ps. I haven't played around much with XIRR calculations as its an add-on to excel that I usually don't have installed. I just checked and found out the google spreadsheets has the XIRR function available online, so it's easy to have a play around. Now, if online the googlefinance data also included australian stocks, and the online spreadsheet had some simple charting I'd be a happy little camper.
Oops - In case B the end balance figure should also read as $10,500 - only the dates were changed in the XIRR calculations. Otherwise the whole example doesn't make any sense.
It's too much of a pain to do the XIRR calc each month. I use the midpoint Dietz estimation:
Gain% = Gain$/(last month's total + 0.5 net contribution)
The inherent assumption is that all contributions are made in the middle of the month.
Hi Enough Wealth
Yes, taking into account the exact timing would be more accurate. But approximations like I gave or ML gives here are good enough I think. Another approximation is to use:
Return/(0.5*(account(t)-account(t-1))
So you take a rough estimate of the average value of the account over the month instead of my starting value.
For a very small account with large inflows and outflows these approximations will be less accurate than for a large fairly stable account.
OK - I'll do an example with margin loans in the next post!
Umm there should have been a plus in the formula in my last comment. Also see my post on margin - it really doesn't make it any more complicated.
Post a Comment