Are you using Value at Risk to measure your portfolio?


Value at Risk (VaR) is an important tool to measure the market risk of any asset. Investors can use Value-at-Risk to estimate the worst expected loss from a share or a portfolio of shares in a given period of time under normal market conditions and a specified level of confidence, usually 95% or 99%. For example if a daily VaR is calculated as $1000 to a 95% confidence level, this means that there is only a 5% chance that the loss will be more than $1000 in the next day.

VaR is mostly used by investment banks and commercial banks to measure the market risk. There are various methods available to calculate the VaR, namely;

  • Variance-covariance method;
  • Historical Simulation Method;
  • Monte-Carlo Method.

It is not very difficult for an individual investor to work out VaR using the first method, variance-covariance method. It will need a little bit of statistical knowledge but it is not hard stuff.

First we need historical returns of our asset/s and then we can calculate the standard deviation or volatility of each asset. It is easy to work out this in excel using STDEV function

The next step is to create a covariance matrix and this is also can easily be done in excel using CORREL function

Then we can work out the value of the 95% confidence level in excel using NORM.S.INV function.

Finally using MMULT function we can work out the portfolio VaR.

I have worked out the VaR for a sample portfolio of Australian shares and see below for the link to the excel workings. (Please note that the shares in the workings have been selected randomly for study purposes only. One must not take any investment decision based on the workings alone.)

VaR Calculation Using Excel

An individual investor can use VaR to rank existing or potential shares and also measure the diversification effect of the portfolio.

The excel example is based on an investor invests $9,000 in 9 different companies from various sectors. The portfolio VaR is $159, i.e. the investor is 95% confident that he will not lose more than $159 from his portfolio of shares during the next day

The effect of diversification is clearly shown in the calculation as the sum of individual VaR comes to $ 259, i.e. $100 more than the portfolio VaR.

As illustrated in the calculation it’s not very hard to calculate VaR using excel and it is a great tool to measure the market risk of your portfolio. However an investor must do proper research on the company before making any investment decision.

Harvey Norman Financial Analysis

Harvey Norman Holdings Ltd (HVN.ASX) is one of the largest retailers in Australia which operates under a franchise system. The primary objective of this report is to analyse the publicly available past financial statements to gain an understanding on the performance of Harvey Norman.

Harvey Norman managed to record a healthy sales growth since 2013. One of the reason was the aggressive pricing policy as demonstrated by the GP Mark-up Index (Sales/Cost of Goods Sold). At the same time the company managed to keep the operational & admin expenses at a consistent level. However, it can be noticed that the sales growth of owned stores has decreased in 2015, which is not consistent with the franchise revenue growth.

The sales per owned store growth is look like a roller coaster – up & down year by year. However, franchise revenue per franchised complex growth has seen a consistent growth since 2012. One of the reasons for this growth is the decrease in the no of franchised complexes from 212 in the year 2012 to 194 in the year 2015.

The net profit margin has been increasing in the last couple of years. In the meantime, Depreciation per Revenue ratio declined from 6.8% in 2013 to 4.8% in 2015 even though the gross property, plant & equipment grew at a decreasing rate.

Harvey Norman has managed to maintain their working capital level consistent over the last 5 years. In fact, Harvey Norman managed to bring down the working capital cycle from 147 days in 2013 to 100 days in 2015.

Earnings before interest, tax, depreciation & amortisation (EBITDA) fell considerably from 2011 to 2014. However, we can see an increase in EBITDA in the year 2015. The net cash flow from operations has also increased from 2012.

Overall Harvey Norman performed well under uncertain economic environments and it continues to grow its sales as evident from the 2016 half yearly results.


NOTE: All the data extracted from Annual Reports available on Harvey Norman’s corporate website.


Waruna Abeysinghe CPA, ACMA is a chartered qualified Management Accountant with experience in providing management accounting solutions to small & medium businesses. Waruna is also an experience financial analyst and has worked in various industries from banking, government, education, retail to constructions.