Mad Money Machine

Auditing Lazy Portfolio Calculations

I don’t know if anyone ever reads comments to the blog postings, so I will think about raising some of them to the level of a posting itself. Here’s the first example:

wizkid writes in Some Lazy Portfolio Entries Backtested:

OK – Ready to audit the calculations.
Please define the calculation of Return and Risk using the multiple periods.
Is return – average annualized return, compounded return with reinvesting dividends, rebalanced annualized return, etc. etc.
Is the risk based on monthly returns, monthly rolling etc.

Explain the reason for the timeframe – why Aug 2000 as the start date?

Great job on comparing the portfolios!

To which I replied:

Oh excellent! Love a 2nd pair of eyes.

I started with August 2000 because if I went back further in time, VIPSX (Vanguard Inflation Protected Securities) didn’t exist prior to that (at least according to Yahoo). And it is used in several portfolios.

I pull monthly “Adjusted” historical stock quotes from Yahoo finance (using that free tool RCHGetYahooHistory) These presumably account for reinvested dividends and for things like splits.

I calculate but do not report the annualized return and annualized risk for each fund. Then I also calculate and report the total portfolio’s return and risk, as below:

Five columns are used in the spreadsheet for each fund:
Date, Adj Close, Normalized, Gain, Value
Date = the first trading day of each month
Adj Close = the funds value brought back from Yahoo including (subtracting) reinvested dividends
Normalized = first month, fund value starts at 1. Each subsequent month therefore shows the total gain
Gain = delta in the value between each month. This is used to calculate the annualized standard deviation.
Value = this fund’s contribution to the total portfolio. Basically, multiply the Normalized column by the fund’s percentage in the portfolio

Then to compute the returns and risk on the total portfolio, I add up all the Value columns for each month, calculate another monthly gain difference column for the total portfolio, compute the total annualized standard deviation, and report the annualized return as:

=+(Last value/First value)^(1/((End date-Start date)/365))-1

I calculate Annualized Standard Deviation as
=STDEV(RANGE*SQRT(12)) where RANGE is the gain column (each monthly difference in value).

It takes about a minute for the spreadsheet to load as it draws all the stock histories from Yahoo.

I DO NOT PERFORM REBALANCING. I should probably do this, huh? Will have to think about how to code it up in Excel.

Suggestions for improvement greatly welcomed.