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.




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;
  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.




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.

Download the applicable spreadsheets at these links: ASX Stock Screener and Stock Valuation spreadsheet.

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.

In addition to the above, you might also like to check the compatibility of your new stock picks with the rest of your portfolio. I’ll be soon to upload a couple of spreadsheets which will do just that. One will estimate the variance of your portfolio, and whether adding a stock adequately reduces the overall portfolio risk. The other is slightly different but more exciting in my opinion, after inputting the ticker symbols of your portfolio and clicking a few buttons, the spreadsheet will calculate and plot your portfolio’s efficient frontier. It will also calculate the optimal weights of your portfolios assets for a given target return by minimising portfolio variance.

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.



ASX Stock Screener

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

Hey guys,

Below is the link to download my ASX Stock Screener spreadsheet.

Download Spreadsheet here!

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 [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.

Next the user has the option of downloading the pre-set array of information for each company or customising the information that will be downloaded from Yahoo Finance via the websites API. To download the pre-set information, the user simply clicks button 2 on the “Screener” sheet. Alternatively, the user can add to or delete items shown in the list on the “Tags” sheet. The full list of headings available for download via Yahoo Finance API is shown on the “Yahoo API” page. The user can simply copy the desire headings along with their tags and paste them at the bottom of the list on the “Tags” sheet. Clicking button 2 now will download the user defined headings.

The user may need to apply a filter to the top row once all downloads have completed otherwise the screener is good to go. The user is free to use all the convenient features of Excel as they wish. I have already applied some conditional formatting to the first two columns, Price-EPS ratio and Price-Book Value ratio, which help to filter out possibly overpriced stocks. The user should keep in mind that a number of columns have already been formatted based on the pre-set headings which means the user may need to re-format some columns if the list of tags is changed substantially.

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.

Happy screening!



Stock valuation via DDM and CAPM with Beta Calculation

Hey guys,

Below is the link to download my Stock valuation via DDM and CAPM with Beta Calculation spreadsheet.

Stock Valuation spreadsheet

In brief, the spreadsheet automatically downloads stock, market, sector and rates data from various websites before performing a number of calculations to produce an estimate of the stocks intrinsic value. The spreadsheet calculates the stocks systematic risk (beta), cost of equity via the Capital Asset Pricing Model (CAPM), and intrinsic value via the Dividend Discount Model (DDM) and minor variations of.

The following instructional video runs through the steps for analysing an example stock, Transurban (TCL) in this instance, and provides a high-level description of the calculations which are carried out.

The second instructional video below runs through the VBA subroutines which perform most of the calculations in the background. This is not necessary viewing for people who simply want to use the spreadsheet without knowing the mechanics of the all calculations.

A more detailed explanation of the mechanics behind the spreadsheet follows…

Firstly, the spreadsheet, at the command of the user, downloads the following data from various websites:

  • Stocks previous closing price (among other key statistics) from Yahoo Finance;
  • Current Australian Government Bond yields from Bloomberg;
  • Current list of all the company’s listed on the ASX;
  • Stocks complete historical pricing data from Yahoo Finance;
  • Market (e.g. ASX200) complete historical index data from Yahoo Finance; and
  • GICS Sector (e.g. Industrials) complete historical index data from Yahoo Finance.

The spreadsheet can be updated on a daily basis simply by repeating the steps outlined in the above instructional videos, however, any new historical pricing or index data will be inserted above existing the data, i.e. the original data set won’t be replaced but added to thus improving accuracy of estimates over time.

After all the necessary data has been downloaded/updated, the spreadsheet can calculate the company’s systematic risk or Beta. This is done using daily, weekly, monthly and yearly Holding Period Returns (HPRs) allowing the user make a choice based on personal preference. In my opinion, the shorter the holding period, the more accurate the results, for example, there are more data points available for daily HPRs compared with yearly HPR.

For each holding period duration, Beta is calculated two ways for additional comparison. The company’s returns are compared with the markets returns (e.g. ASX200 returns) to produce a typically more accurate Beta estimate. Alternatively, the company’s returns are compared with the returns of the relevant GICS Sector Index (e.g. Industrials). I would typically only use Beta estimates produced via Market comparisons but sometimes it might be useful to review estimates produced via Sector comparisons.

Once Beta has been calculated, the cost of equity can be calculated via the Capital Asset Pricing Model (CAPM). The cost of equity is calculated for daily, weekly, monthly and yearly compounding intervals. For example, the weekly cost of equity value represents the rate of return an investor requires to invest in this company for 1 week given the company’s current systematic risk (beta).

The CAPM uses the current Australian government bond yield as the risk-free rate. The particular yield (2, 5, 10 or 15 year maturity) used is based on the user defined investment horizon. For long-term investors this will likely be 15 years, but 2 years might be more suitable for short-term traders. The selected risk-free rate adopted is appropriately scaled according to each holding period duration.

The Dividend Discount Model (DDM) is then applied for both constant growth and zero growth scenarios, where the growth factor is calculated based on the company’s Return on Equity (ROE) and Dividend Payout Ratio.

An alternative method to the DDM is also presented on the first CAPM sheet. This method compounds the user defined, previous years’ annual dividend forward by the growth rate for one to forty years. The present value of each of the expected future dividend payments is then calculated by discounting the cash flow with the appropriate cost of equity given the holding period duration. The intrinsic value is the sum of all discounted future cash flows up to the end of the user’s investment horizon. This is the same concept as the DDM except this method assumes a finite investment horizon (as opposed to the infinite investment horizon assumed by the classic DDM) and might prove more suitable if the growth rate used is greater than the cost of equity calculated.

There is a secondary method presented for calculating the company’s cost of equity and, ultimately, intrinsic value. This method is presented in the CAPM2 sheet and is fundamentally based on how the company’s average yearly returns compare with the markets or sectors average yearly returns. These average yearly returns are a compound average calculated with daily, weekly and monthly compounding periods. Rather than using yearly holding period returns which consider only one data point per year, and perhaps overlook large spikes and/or dips in stock price, this method considers the following:

  • 12 data points for monthly compounding yearly averages;
  • approximately 52 data points for weekly compounding; and
  • approximately 252 data points for daily compounding;

while still considering the covariance between yearly returns. This might be the user preferred method but in my opinion produces less accurate results than those obtained via first CAPM method.

I hope you find this spreadsheet handy and feel free to leave any comments you might have.

Stay tuned for the next spreadsheet which will calculate the variance of your portfolio with only a few clicks and strokes.



%d bloggers like this: