Below is the link to download my Portfolio Risk spreadsheet.
In summary, this spreadsheet calculates the current risk, i.e. standard deviation, and expected return of a user-defined portfolio. The spreadsheet also calculates the constituent assets standard deviations and expected returns. Additionally, the spreadsheet can be used to assess the suitability of adding or removing assets from/to the portfolio. For example, the user can add additional assets, run the calculations again, save the results and compare with earlier portfolio configurations. If it is observed that the additional assets increase the portfolio risk, then perhaps the user will be less inclined to invest. The data used within the calculations is automatically downloaded from the Yahoo Finance website, the asset selection is restricted to ASX listed stocks whose historical pricing data can be found on this website.
Check out the instructional video below for a brief demonstration on how to use the spreadsheet.
In more detail, once the user has input the asset information of their portfolio, i.e. Ticker Symbol, Purchase Date (not mandatory), Number of Shares Purchased, Purchase Price (not mandatory), and Fees (not mandatory), information downloads and calculations can commence. The non-mandatory items aren’t required to calculate the portfolio risk but are useful to assess the progress of the user’s portfolio.
The following downloads and calculations are initiated by the user on the “portfolio” sheet:
- Previous Close: automatically downloads the previous closing prices for each asset in the portfolio from Yahoo Finance;
- Historical Price: automatically downloads the complete historical pricing data set for each asset from Yahoo Finance (this button will also add new data to each assets’ data sheet if clicked any number of days after initialising the portfolio);
- ASX List: automatically downloads the latest list of ASX listed companies from asx.com;
- Sigma-Day/Week/Month/Year: calculation which determines the portfolio variance-covariance matrix based on varying holding period durations, i.e. daily, weekly, monthly or yearly;
- Sigma-All: initiates calculations for all four of the above portfolio variance-covariance matrices;
- Sigma-Custom: initiates variance-covariance matrix calculation based on asset yearly returns, where the yearly returns are compound averages based on user defined holding period durations, i.e. daily, weekly or monthly.
Once the user has download all the relevant data and calculated the desired variance-covariance matrices, they can initiate the expected return and standard deviation calculations for each asset and the portfolio. Expected returns in this case are arithmetic mean returns. The following calculations are initiated by the user on the “results” sheet:
- Daily Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on daily holding period returns;
- Weekly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on weekly holding period returns;
- Monthly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on monthly holding period returns;
- Yearly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on yearly holding period returns;
- All Statistics: initiates standard deviation and expected return calculations for all the above four holding period durations;
- Custom Statistics: calculates each asset’s as well as the portfolio’s expected yearly returns and yearly standard deviations based on compound average returns with duration corresponding to that defined by the user for Sigma-Custom;
- Save Portfolio: copies the current portfolio configuration as well as all the calculated statistics to the “saves” sheet, the data is catalogued so it can be filtered and compared with other portfolio configurations.
It should be noted here that the spreadsheet uses only historical pricing data available from Yahoo Finance, specifically the adjusted closing price. Dividend payments are not included in holding period return calculations.
The way I use the ‘Save Portfolio’ feature is by first inputting my portfolio in its current state and then calculating each variance-covariance matrix, expected return and standard deviation. Then I save the configuration with a suitable name, say “myPortfolio”, for later use. Next I add any additional (or remove unwanted) assets I’m interested in investing in and repeat all calculations. After saving the new portfolio configuration I can filter by “PORTFOLIO” under the “Company Name” heading on the “saves” sheet and compare the results of the two configurations. If the new configuration shows a reduction in risk (standard deviation) and/or an increase in expected return I would be more inclined to make the changes to my portfolio. Just remember to consider all fees which might accompany such an amendment to the users portfolio.
I hope you find this spreadsheet useful. Let me know if you have any trouble or if you come up with any improvements I might be able to incorporate.