Mean-Variance Portfolio Optimisation

Hey guys,

Below is the link to download my MV Portfolio Optimisation spreadsheet.

MV Portfolio Optimisation

Basically, this spreadsheet determines how much of each stock an investor should hold for the next investment period (year/month/week/day) to minimise the variance (risk) of the portfolios returns for that period given a target return. The spreadsheet also plots the Efficient Frontier for the portfolio, and the required weight of each asset against portfolio target return.

Check out the instructional video below for a brief demonstration on how to use the spreadsheet.

Once the user has input the ticker symbol for each stock within their portfolio on the “Assets” sheet, they can initiate the download of all historical pricing data from Yahoo Finance. This data is then used to calculate the Variance-Covariance matrix based on a user-defined Holding Period (Day, Week, Month or Year). The inverse of the Variance-Covariance matrix can then be estimated, and the expected returns of each asset calculated. The expected return for each asset is the arithmetic mean of the historical returns for that asset based on the previously defined Holding Period, i.e. the Holding Period used to calculate the Variance-Covariance matrix. Now the user can optimise their portfolio, i.e. calculate the asset weights to minimise portfolio variance for a given target return, based on the theory below. The user will be prompted to input their desired target return as an annual figure when they click the “Optimise” button on the “Results” sheet. The target return will be appropriately scaled to coincide with the previously defined Holding Period. Finally, now the ‘a’ and ‘b’ vectors (refer below theory) have been calculated, the user can plot the Efficient Frontier of the portfolio, and each asset weight against target return.

The spreadsheet adopts Mean-Variance Portfolio theory, which is a mathematical technique that determines asset weights within a portfolio to minimise the variance (risk) of the portfolios returns given a target return for that portfolio. This spreadsheet assumes the portfolio is composed of only risky assets, specifically stocks listed on the Australian Stock Exchange (ASX). To minimise the variance for a given target return, MV Portfolio theory adopts the Method of Lagrange Multipliers, and after a bunch of tedious matrix algebra, the following relationship for portfolio weights is derived:

Where represents the Variance-Covariance matrix of the portfolio and µ represents a vector of each assets expected return. The ‘a’ and ‘b’ vectors shown in the above relationship are calculated and displayed in the “Results” sheet.

As can be seen from the above formula, the inverse of the Variance-Covariance matrix is required to compute the asset weights vector. Since the Variance-Covariance matrix is always square and generally non-singular, MS Excel’s “MInverse” function can normally calculate the inverse without issue. In the instance where there exists near-singular entries (values smaller than 1e-16) in the Variance-Covariance matrix, or if “MInverse” returns an error for some reason, the Moore-Penrose Pseudoinverse of the matrix will be estimated. Refer to my previous blog post, Moore-Penrose Pseudoinverse for more information on the algorithm used in this scenario.

So the reason why I went to all the trouble of building a VBA algorithm for calculating the Moore-Penrose Pseudoinverse was because the first Variance-Covariance matrix I tried to invert was singular (non-invertible). I stupidly assumed that this would be the case for all Variance-Covariance matrices… it isn’t. The Variance-Covariance matrix was only singular in that instance because I input the same stock twice (to represent the purchase of shares of the same company on two separate occasions) leading to multiple entries of the same value within the matrix, and the matrix to be non-invertible. In any case, the “Calculate Inverse” button on the “Results” sheet will produce at least an estimate of the inverse of the Variance-Covariance matrix. So the user can input the same ticker symbol any number of times and the portfolio can still be optimised but I would recommend the user only input each ticker symbol once, the calculation will be quicker this way and the result more simple.

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.

Enjoy!

Cheers,

Nick

Portfolio Risk

Hey guys,

Below is the link to download my Portfolio Risk spreadsheet.

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:

  1. Previous Close: automatically downloads the previous closing prices for each asset in the portfolio from Yahoo Finance;
  2. 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);
  3. ASX List: automatically downloads the latest list of ASX listed companies from asx.com;
  4. 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;
  5. Sigma-All: initiates calculations for all four of the above portfolio variance-covariance matrices;
  6. 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:

  1. Daily Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on daily holding period returns;
  2. Weekly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on weekly holding period returns;
  3. Monthly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on monthly holding period returns;
  4. Yearly Statistics: calculates each asset’s as well as the portfolio’s expected returns and standard deviations based on yearly holding period returns;
  5. All Statistics: initiates standard deviation and expected return calculations for all the above four holding period durations;
  6. 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;
  7. 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.

Enjoy!

Cheers,

Nick

%d bloggers like this: