## Mean-Variance Portfolio Optimisation

Hey guys,

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,

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

## Example ASX stock screen and valuation

Hey guys,

Today we’re going to run through a couple of examples using my Stock Screener spreadsheet to hone in on some cheap stocks, and then my Stock Valuation spreadsheet to estimate the value of those stocks to us as investors.

At the risk of ruining the surprise, the stocks I value in this post are the following:

• Metcash Limited (MTS);
• St George Mining (SGQ); and
• Crown Resort Limited (CWN).

Check out the demonstration video below to see how the three company’s performed using the Stock Screener and Stock Valuation spreadsheets.

To hone in on cheap stocks I use the Price-EPS ratio and Price-Book Value ratio. For the former, I filter out all stocks with a ratio greater than 15. For the latter, I look for stocks with a ratio between 0.6 and 1.5. This screen yields about 150 cheap stocks, some of which worthy of further investigation. I personally like to go for large Market-Cap stocks so that is the next search criteria I use. From there, I wanted to analyse stocks not in the “Real Estate” GICS Industry Sector (personal preference really, large Market-Cap Real Estate stocks tend to dominate the low P/E universe of ASX stocks and I like to choose from the minority).

Once I’d narrowed my stock list down to MTS, SGQ and CWN, I used my Stock Valuation spreadsheet to estimate the value of these companies by calculating their systematic risk – beta, and their cost of equity via the Capital Asset Pricing Model, and finally the sum of their discounted, expected future cash flows via the Dividend Discount Model.

A note I made in the demonstration video which I’ll make again here is that my Stock Valuation spreadsheet will only tell you if the company is presently undervalued or not. There’s a bunch more research and analysis the user/investor should carry out before investing in an undervalued stock. These include the following:

• Assessing the competency of the managers of the company;
• Ensuring the company has long term prospects (e.g. a product which will always be in demand);
• Ascertaining the stability and understandability of the company (look for company’s with ROE figures which consistently exceed 10% with little fluctuation).

If the above sounded familiar, you’re not crazy – I’m not trying to reinvent the wheel here. I believe these are the criteria Warren Buffett uses to select the stocks he invests in.

As covered in the demonstration video, there are a couple of circumstances where my Stock Valuation spreadsheet is unsuitable for estimating the current value of a company. This was the case for St George Mining (SGQ). According to the Morningstar data available to free members, SGQ had consistently and significantly negative ROE values for the past three years. This in combination with no dividend payments and thus dividend payout ratios of zero over the same time-frame meant the company growth rate could not be calculated and the Dividend Discount Model could not be used.

During the analysis of Crown Resort Limited (CWN) a separate issue was encountered. Given CWN has only be listed on the ASX since December 2007, shortly before the GFC hit, and the sample statistics presented on the “CAPM” sheet are calculated based on common time horizons (i.e. only market data since December 2007 is included), the expected value for the market (ASX200 in this case) is negative for three out of four holding period durations. The negative expected value produces spurious cost-of-equity values when used in the Capital Asset Pricing Model, and thus the results presented on this sheet, in this instance should not be utilised. Fortunately, the results presented in the “CAPM2” sheet are sound. This is because the expected value used here is based on market returns over the life of the index (since the ASX200 began in 1992). This longer time horizon is less affected by the large, negative returns of the market in 2008 and 2009 following the GFC.

As mentioned above in the recommended additional analysis points, stability and understandability of a company is important. This is usually assessed by a fundamental analysis of the company’s financial statements. I presently have a spreadsheet in my portfolio which analyses this information but it is quite convoluted and it requires the user has access to Morningstar’s DatAnalysis service which few individual investors do. I will work on improving this spreadsheet and upload it when its ready.

As usual, let me know if you have any issues or recommendations. Happy to assist where possible.

Cheers,

Nick

## ASX Stock Screener

*** Yahoo Finance discontinued their API featuring meaning this spreadsheet can no longer download stock data. Sorry guys ***

Hey guys,

The spreadsheet allows the user to screen for ASX listed stocks based on company data obtained from Yahoo Finance using the websites API. The “tags” I used in my spreadsheet were gathered from a blog by a lad named Ido Green at the following website:

Yahoo Finance (hidden) API

More information can be found here if the user desires.

The following instructional video briefly explains how to use the Screener.

After downloading and opening the spreadsheet the user first updates the list of ASX companies using button 1 on the “Screener” sheet. This extracts a .csv file from www.asx.com [full link here] and populates the “Screener” sheet with a complete and current list of ASX listed companies along with their ticker symbols and GICS Industry Group.