[jewillia]

A friend of mine looked at the offline Excel Portfolio Builder spreadsheet in a lot of detail and he found a small error in the calculation for CAGR. The error is due to how the time difference from End to Start is calculated. A Named range
called “DatesDuration” is used to calculate the time difference. The spreadsheet uses a fixed amount of time to count
the number of rows of data, which gives trading days, but the formula needs to use calendar days. Currently the
spreadsheet is adjusting the time by adding 5 * 100, there are approximately 100 days/year that are non trading,
and this adds 5 years of this. But since there are more than 5 years of historical data, it causes CAGR to be overstated.
One should use the difference between the start and end date, and let it change every time the data is updated.

Here’s a fix for the calculation:
Unhide the sheet named “Control”
Unprotect it
In cell B4 you will see the name “DatesDuration” . Need to change C4.
In cell C4, put in the following formula.
=MAX(ReturnData!A:A)- (MIN(ReturnData!A:A)-1)

[jewillia]

When will the NASDAQ100 strategy be added to the Customer Portfolio Builder (Excel version)? I would like to evaluate performance/drawdown with different strategies and asset allocations.

Thanks,

Jeff

[jewillia]

The Excel Portfolio Builder is a very nice tool that I look forward to using, but I ran into couple issues. I am running Office 2010, but when I enable editing I get error message “Compile Error: Can’t Find Project or Library”.

Also, I compared the Excel version to a custom allocation I came up with and the results for CAR/MDD/etc are different between the Excel version and the Google Spreadsheet version. I can send screen shots if you want them.

[Author]

Posts