penninecity is a partner site to Finance with Finesse, and aims to give finance tools to all interested in global markets.
More information on Options & Bond Yield & Prices Option Pricing Models, coupon rates, rates to maturity, yield curves etc
Risk and Return: Variance, CoVariance and Portfolio Variance [For econometrics see Technical Analysis]
Maximising the value of a portfolio, (or minimising the losses) is a fundamental building block for investors large or small. Here we take a first look at a portfolio comprised of 5 ftse 100 stocks. To analyse theis portfolio we will bring in the concepts of Variance, CoVariance and portfolio Variance. Briefly Variance is the deviation of a stock’s return with its own average returns, Co variance on the other hand is the variance of a stock’s return with respect to another stocks’ return. We then go on to calculate Portfolio Variance which gives us the amount of risk that an investor is exposed to when holding a particular portfolio.
Following the arguments set out earlier when we manually calculated the
Portfolio Variance for a 2 stock portfolio, it is clear that when one
gets above two stocks, and certainly when one gets into 10s or even 100s
of stocks, the calculations can become extremely difficult. Fortunately
we have the magic of Matrix Algebra to help.
Image One
As can be seen from Image One our portfolio consists of 5 ftse 100 stocks - BP, Vodafone, UU.L, Tesco and Morrison, The column separating each company column is the daily returns matrix. For example, taking BP's first daily return, this is calculated by =B5/B4-1 which gives -0.87%. Once the formula is known it is easily entered and copied. The Average Daily Return (AvDailyRt) is calculated for each company, using the Excel function =AVERAGE(K5:K29).
The Excess Returns Matrix is calculated using the formula =C5-$C$30 givesw
-0.90% (This is the first entry in the BP column. Once this is known it
can be copied using Excel.
Now we move on to calculate the X^T multiplied by X matrix, which reads
X transpose multiplied by X This involves matrix concepts but is reasonably
easy to accomplish. The theory does not have to get in the way of the
accomplishment. For those interested please see the Technical Analysis.
Image Two
The next step is to create the Variance CoVariance Matrix. This is shown in Image Three
Image Three
The Variance CoVariance Matrix divides each element of the X TRANSPOSE MULTIPLIED BY X matrix by the total number of elements in the matrix, which is 25 ( in cell S14). The Variance Covariance matrix gives us some interesting findings. Say we wish to find the CoVariance between Vodafone and UU.L. Consulting the matrix we find that this figure is 0.000106. The CoVariance between Tesco and Morrison is 4.21E-06 and so on.
Image Four
I
mage Five
These calculations give us a Portfolio Variance of 0.78%. Remember this is a measure of how the aggregate returns of this portfolio move over time. Adjusting the weights would move us forward to further calculations to iterate towards the least risk and highest return portfolio.