Investment projects involve varying degrees of risk. Investments in the form of bank deposits and government bonds normally involve no risk, while equity investments can involve a significant risk. Investments with higher risk will normally provide higher returns over time than investments with little or no risk: investors demand higher returns on risky investments than on investments with no risk. Risk can be measured by registering how much the price of a share fluctuates over time. An investor can, to some extent, control investment risks by diversifying. That is, he invests in a portfolio of securities in which prices fluctuate out of step with each other.
8.1 Risk, variance and standard deviation
Fluctuation in share prices over time says something about the risks of investing in the stock. Such fluctuations can be measured with the variance and standard deviation of share returns.
If you can buy a share at time t for Pt and sell the same stock a month later at time t + 1 for Pt+1, the return for the period is calculated as:
From this it follows that Pt grows to Pt+1 = Pt(1 + r) over one time period. We would, however, like to calculate the average return over several periods, and to operate with different period lengths. The length of a calendar month varies from 28 to 31 days. It is therefore more accurate to use the following expression for the calculation of returns in a period:
This means that Pt grows to 
in a period when we assume a continuous recalculation of the return. This is the correct approach when we consider that stocks and other securities can be bought and sold virtually continuously.
If you have registered a monthly return r1, …, rn during n months for the stock, an estimate of the return variance is calculated by applying the following expression.1
Standard deviation is defined as the square root of the variance:
Variance and standard deviation are thus measures of how much a share’s return fluctuates, and thus a measure of the stock’s risk. A standard deviation always uses the same unit as the observed object. This is not true with variance. Therefore, it is often most appropriate to use standard deviation as a measure of an equity’s risk.
The mean of observed returns is used as an estimate of expected returns:
If you know the expected return E(r), the standard deviation can be calculated as:
In this case, the data are not used to calculate the average value 
. Thus, there are n degrees of freedom.
A stock has the following monthly return rates during a given year.
a) Calculate the average monthly returns and accompanying standard deviation.
b) Assume that the average monthly return from a) is equal to the expected monthly return for the stock. Calculate the standard deviation of stock returns.
a) The average monthly return, calculated with Excel using the AVERAGE function, is 1.33 per cent. The empirical standard deviation is calculated using equation 8.4. In Excel this is done using the function STDEV as shown in figure 8.1. The standard deviation is 1.70 per cent.
b) The empirical standard deviation based on the known expected return should be calculated using equation 8.6. In Excel this is done with the function STDEVP. This standard deviation is 1.63 per cent.
Note that the difference between the answers in a) and b) is negligible. If data sets are of a certain size, it has no practical significance if one uses equation 8.4 or 8.6 for the calculation of standard deviation.
Figure 8.1 Calculation of standard deviation.
In figure 8.1, cell addresses (B5:B16) are used to define an area on the worksheet. Alternatively, the area can be given a name. To do so, first mark the appropriate area. Then click on the right mouse button, choose Name a Range, and enter a suitable name in the dialogue box that appears. In Excel functions the name of an area can be used, e.g. = STDEV (A).
A share has had the following average monthly trading value on the last day of the month throughout the year. Calculate the average monthly return during the year and the standard deviation. Assume dividends have not been paid on the stock.
The monthly return is calculated using equation 8.2. In Excel this is done by creating a separate formula. Average monthly returns are calculated with the function AVERAGE, which results in 0.51 per cent. The standard deviation is calculated with the function STDEV, giving 8.38 per cent.
Return on shares (and other investments with risk) will normally vary and be partially out of step. Imagine two different stocks with the same expected return and standard deviation. If the return on these two stocks swings out of step, the standard deviation of return on a portfolio of two stocks will be lower than the standard deviation of individual shares. This is because the price movements of the two individual stocks partially compensate each other. One reduces risk by diversifying, i.e. investing in a portfolio of stocks rather than investing everything in a single stock.
Stocks A and B give the following returns for the months of January to December (per cent).
Calculate the average returns per month and the standard deviations of both the individual stocks and for a portfolio in which 60 per cent is invested in stock A and the rest in stock B.
Figure 8.2 Calculation of average returns and standard deviations.
In this case both individual stocks show an average return of 2.2 per cent per month, and a standard deviation of 5.4 per cent. (It’s obvious that the example is constructed.)
The monthly returns on the portfolio are calculated as weighted averages. For January we get:
0.6 · 15.9% + 0.4 · 1.8% = 10.2%
The average return on the portfolio must be 2.2 per cent, the same as the individual stocks. The standard deviation is calculated as 4.2 per cent, which is significantly less than what we got for the individual stocks. The reason for this is that the prices of the stocks to some degree move independently. The price movement of the two individual stocks partially compensate each other, and this reduces the fluctuations on the portfolio.
The correlation between the returns on two different stocks A and B can be measured by the covariance:
The covariance has the same dimensions as the variance, and measures to what degree two variables (here rA and rB) change together. When an increase/decrease in rA always gives an increase/decrease in rB, the covariance gets a high positive value. On the contrary, if an increase/decrease in rA gives a decrease/increase in rB, the covariance gets a high negative value. If rA and rB move independently, the covariance gets a value close to zero.
Another measure of correlation between two variables is the correlation coefficient ρ, which is defined as the covariance between the variables divided by the standard deviations of the variables (cf. equations 8.4 and 8.7):
The correlation coefficient is dimensionless and can also be considered as a measure of the linear relationship between the two variables. ρAB = 1 means a perfect linear relationship where rA increases when rB increases. ρAB = – 1 also means a perfect linear relationship, but then rA decreases when rB increases. If ρAB is close to 0, it means that the two variables are not linearly dependent on each other.
Calculate the covariance and the correlation coefficient between rA and rB in example 8.3.
The covariance can be calculated by the Excel function COVAR. Be aware that the COVAR function divides the sum in equation 8.7 by n instead of n – 1. To get the correct covariance this function should be multiplied by n/(n – 1). This is illustrated in figure 8.3, where we calculate the covariance as 0.0006. The correlation coefficient can be calculated by the Excel function CORREL. In this case the correlation coefficient is 0.22. These results indicate that rA and rB fluctuate independently.
Figure 8.3 Calculation of covariance and correlation coefficient.
The covariance and the correlation coefficient can also be calculated by Excel’s Data Analysis tool. First we click the Data tab and then Data Analysis in the Analysis group. In the dialogue box that appears, we choose Covariance or Correlation. If we choose Covariance, the dialogue box in figure 8.4 appears.
Figure 8.4 Calculation of covariance.
In this case the data is grouped by columns. Thus we make a mark on Columns. As Input Range we choose the two columns with data in the cells B5:C16. As Output Range we choose, for instance, cell A25. When we click OK, the results in figure 8.5 appear (in the cells A25:C27).
Figure 8.5 Results from a covariance calculation.
The three values are covariances between columns 1–1, 1–2 and 2–2 respectively. The interesting one is of course 0.00056872 (the others are variances). These values are calculated by a division with n instead of n – 1. To get the correct covariance we make the following correction:
(12/11)0.00056872 = 0.0006204
Notice that we get approximately the same value whether we divide by n or n – 1 in this case.
The correlation coefficient can also be calculated from a plot of rA against rB in Excel. Then we draw a regression line by pressing the right mouse button while standing on one of the dots in the diagram and choosing Add Trendline. Under Trendline Options in the Format Trendline dialogue box that appears, we choose Linear, and Display R-squared value on chart. Then the R2-value appears in the diagram as illustrated in figure 8.6. R is identical to the correlation coefficient. In this case R2 = 0.0463 and thus R = 0.2.
Figure 8.6 Regression line.
8.3 A portfolio’s variance and standard deviation
Prices and returns on stocks usually fluctuate over time, and small fluctuations are more likely than large fluctuations. Thus it is reasonable to assume that a stock’s return follows a normal distribution described by the stock’s expected return and standard deviation.
A portfolio’s expected return is calculated as the weighted average of the stock’s expected returns. As an estimate of a stock’s expected return E(r) we use the average return . If a portfolio contains a share xA of stock A and a share xB of stock B, then the portfolio’s expected return is estimated as:
Example 8.3 included a calculation of the variance for a portfolio of two stocks. It’s more likely that a portfolio includes several different stocks. For such purposes we need a more useful method.
The variance for a portfolio of the two stocks A and B can be calculated as:
Replacing σAB with σABσAσB gives (cf. equation 8.8):
We see that if the correlation between the stocks reduces, then the portfolio’s variance reduces.
The right side of equation 8.10 includes four terms of the form (share)(share)(covariance). This is illustrated by the covariance matrix in figure 8.7. In the cells for the combinations A–B and B–A we find the term (share A)(share B)(covariance A–B). In the cell for the combination A–A we find the term (share A)(share A)(covariance A–A). The covariance a variable shows with itself is equal to the variable’s variance. Thus the term (share A)(share A)(covariance A–A) is expressed as 
. By the same reasoning we get 
in the cell for the combination B–B. The variance of a portfolio including the stocks A and B is then calculated as the sum of the four cells in the covariance matrix (cf. equation 8.10).
Figure 8.7 Covariance matrix for a portfolio of the two stocks A and B.
Quarterly returns for stock A and B are presented below (per cent). Estimate expected returns and standard deviations for different portfolios of A and B. Present your results graphically where you draw E(rp) as a function of σp.
First we calculate the average return and standard deviation for each stock, and the covariance between the stocks. Then expected returns and standard deviations for different portfolios of A and B are calculated by equations 8.9 and 8.10. The results are presented both in a data table and with formula calculations in the spreadsheet in figure 8.8.
Figure 8.8 Expected returns and standard deviations for different portfolios of A and B.
Let’s now expand the matrix in figure 8.7 and study a general case with N stocks constituting a portfolio. The portfolio’s variance can be calculated as the sum of all cells in a covariance matrix like the one illustrated in figure 8.9. The cells along the diagonal include values on the form 
while the rest of the cells include values of the form xixjσij (or xixjρijσiσj). The next example demonstrates how such a covariance matrix can be calculated by applying functions in Excel.
Figure 8.9 Covariance matrix for a portfolio of the stocks 1, 2, 3, …, N.
Returns per year have been observed for five different stocks over a period of eight years (per cent):
Estimate the following portfolio’s expected return and standard deviation:
In the spreadsheet in figure 8.10 average returns for the stocks are calculated in the cells B21:F21. The portfolio’s expected return is calculated as a weighted average of these returns to 6.42 per cent. The portfolio’s variance is calculated from a matrix as illustrated in the spreadsheet. The covariance matrix in the cells J5:N9 includes terms on the form xixjσij. An example of a formula is shown in the formula bar. The portfolio’s variance is calculated as the sum of the cells in this matrix.
Below row 14 we find an alternative calculation of the portfolio’s variance. The cells J18:N22 are a data table containing values for xixj, while the cells J26:N30 are a matrix containing values for σij. The portfolio’s variance is calculated by applying the function SUMPRODUCT on the two arrays of cells. We get the correct variance by multiplying the result with n/(n – 1). The portfolio’s standard deviation is calculated as the square root of the variance to 3.05 per cent.
Figure 8.10 Calculation of expected return and standard deviation.
The methods presented in the spreadsheet in figure 8.10 are a bit troublesome. Further down in the same spreadsheet, and in figure 8.11 (in the cells J39:N43), we find a data table with values for σij. The data table is calculated with the formula “=COVAR(OFFSET($B$39:$B$46;0;$H$49-1);OFFSET($B$39:$B$46;0; $J$47-1))”. The OFFSET function returns the reference to a range of data (here the returns on one of the stocks) as a number of rows and columns away from a given reference (here the returns on Axe). The offset measured as a number of rows and columns is read from the row and column headings in the data table. In order to make the OFFSET function work, we need to identify the stocks by numbers instead of names.
A data table with values for xixj is generated in the cells J18:N22. The portfolio’s variance is again calculated by applying the function SUMPRODUCT on the two arrays and correcting the result with n/(n – 1). The portfolio’s standard deviation is again calculated as the square root of the variance, as 3.05 per cent.
Figure 8.11 Calculation of expected return and standard deviation.
Matrix methods for calculating portfolios
Matrices can be used to calculate a stock portfolio’s expected returns and variance. Matrix models are part of the mathematical field of linear algebra.
A short note on matrix algebra. A matrix is a collection of numbers organized in m rows and n columns. An m × n matrix called A is shown below. Each number in the matrix can be represented by aij where the row number is i = 1, 2, …, m and the column number is j = 1, 2, …, n. If we swap rows and columns in an m × n matrix, we get the transposed matrix AT which will be an n × m matrix.
The following is an example of a transposition:
Two matrices can be multiplied if the number of columns in the first is identical to the number of rows in the second. The product of two matrices C = A · B, where the m × p matrix A is multiplied by the p × n matrix B, results in the m × n matrix C where every number in matrix C is calculated as:
An example:
The sequence in which multiplication is performed is important.
In Excel, two matrices can be multiplied using the MMULT function. The two matrices that are to be multiplied are indicated in the usual way in the accompanying dialogue boxes. The solution, which is normally also a matrix, must be defined as a matrix formula. This is done by marking the cells for holding the resulting matrix and pressing F2 and Ctrl+Shift+Enter.
Calculating expected portfolio returns and variance. Assume that one has a portfolio with portfolio weights x1, x2, …, xN of stocks 1, 2, …, N. The expected returns for the stocks are E(r1), E(r2), …, E(rN). These quantities can be presented in the matrices X and E(r), both shown by a single column. Matrices consisting of a single column are usually called a vector:
The expected portfolio yield is calculated as a weighted average of the expected returns:
This result is obtained by multiplying the transposed matrix XT with the matrix E(r):
Assume that the variances and covariances are given in the following N × N matrix (covariance σ11 is the same as the variance σ12). These can be calculated on the basis of the historical returns from the stocks over several periods.
Variance for the portfolio is calculated as the sum Σi Σjxixjσij of the cells in the matrix:
This sum is calculated as the product XT K X:
Covariance for the yield of two portfolios containing the same stocks can also be calculated effectively using matrix multiplication. Assume that portfolios X and Y contain the following proportions of stock 1, stock 2, …, stock N, given in the matrices:
Stock variances and covariances are found in the matrix K (equation 8.17). Covariance between the two portfolios can be calculated as X K YT.
Calculate the expected return and standard deviation for the portfolio in example 8.6 by matrix methods.
In the spreadsheet in figure 8.12, the expected return is calculated to 6.42 per cent in cell G27 by the matrix function {=MMULT(B17:F17,B24:B28)}. This function multiplies the matrix XT in the cells B17:F17 by the matrix E(r) in the cells B24:B28. The portfolio’s standard deviation is calculated to 3.05 per cent in cell K23. The variance in cell K20 is calculated as the product XT K X.
Figure 8.12 Calculation of expected return and standard deviation.
Calculating the matrix K. In example 8.7 the matrix K (including σij values) was calculated by a method introduced in example 8.6. We will now introduce an alternative method for calculating the matrix K.
Suppose we are dealing with a portfolio of N stocks and have N sets of observed returns for the periods 1, …, n available. The covariance between stock i and j can be defined as:
Here rt, i is the return on stock i during period t, and i the average return for stock i.
First we calculate the n × N matrix Q and its transposed N × n matrix QT. With Excel the matrix Q can be calculated by subtracting the 1 × N matrix rmid (including the values 1, …, N) from the n × N matrix r (including the values r1, 1, …, rnN).
The product QTQ is an N × N matrix. Each value in this matrix is calculated by multiplying a specified row in QT with a specified column in Q. As an example we see that the value located in the first column and the second row in the matrix QTQ is calculated as:
This expression is in correspondence with the right side of equation 8.21, and thus equal to σ12. We see that the value located in column i and row j in the matrix QTQ is calculated as:
By dividing this expression by n – 1, we obtain the covariance σij (cf. equation 8.21). Thus the matrix K can be calculated as:
The variance for the portfolio is calculated as the product XT KX where the matrix X includes the shares of the N different stocks in the portfolio (cf. equation 8.19). In the next example we will see that all this can be included in a single Excel function which calculates the portfolio’s standard deviation.
Calculate the expected return and standard deviation for the portfolio in example 8.6.
In the spreadsheet in figure 8.13 we find the matrix r in the cells $B$6:$F$13, the matrix rmid in the cells $B$16:$F$16, and the matrix X in the cells $J$6:$J$10. The variance for the portfolio can be calculated by the formula XT(r – rmid)T(r – rmid) X/(n–1), and the standard deviation as the square root of this expression. The function for the standard deviation is shown in the spreadsheet’s formula bar.
Figure 8.13 Calculation of standard deviation.
Minimizing a portfolio’s standard deviation
Excel’s Solver was demonstrated in section 4.2, and can be employed to find the composition of a portfolio that minimizes the portfolio’s standard deviation. Let’s take a look at an example.
Find the composition of the portfolio in example 8.6 that minimizes the portfolio’s standard deviation.
In an Excel spreadsheet the standard deviation for a given portfolio can be calculated by applying one of the methods presented in example 8.6 or 8.7. In the spreadsheet in figure 8.14 we have told the solver to change the parts in the cells B17:F17 so that the standard deviation in cell I15 is calculated to its lowest possible value. Two constraints are necessary: All parts must be positive values, and the sum of the parts must be equal to 1. In this case the solver calculates the parts 0.20 – 0.28 – 0.27 – 0.21 – 0.04 that minimize the portfolio’s standard deviation.
Figure 8.14 Minimization of a portfolio’s standard deviation.
8.4 Diversification reduces risk
In section 8.3 we saw that a portfolio’s standard deviation was less than the standard deviation for each stock constituting the portfolio. Thus we can reduce risk by investing in more than one stock, i.e. by diversification. In a stock market, the prices will to some degree fluctuate together. Such simultaneous fluctuations can for instance be caused by corrections in the national economy and represent the market risk (systematic risk). In addition the prices of different stocks will fluctuate independently. Such fluctuations represent the unique risk (unsystematic risk). When an investor chooses a well-diversified portfolio containing several different stocks, he reduces solely the unique risk. If he includes all available stocks in the portfolio, the unique risk will be reduced to a minimum.
A theoretical minimum for a portfolio’s risk is obtained when the number of different stocks constituting the portfolio approaches infinity. Assume we have a portfolio of N different stocks. The covariance matrix (cf. figure 8.9) then contains N values on the form xijσ2i along the diagonal. The entire matrix contains N2 values. Thus the number of values on the form xixjσij that lies off the diagonal, is N2 – N. Assume now that the portfolio contains equal amounts of each stock so that each share is 1/N. We also assume that each stock has a variance equal to the average variance Var, and that every covariance between different pairs of stocks is equal to the average covariance Cov. The portfolio’s variance is the sum of all values in the covariance matrix, and can be expressed as:
As the number of different stocks in the portfolio approaches infinity, i.e. N → ∞ and 1/N → 0, the portfolio’s variance will approach the average covariance Cov. Thus the average covariance represents the market risk in this case.
Suppose we have a stock market where all stocks show a standard deviation of 12 per cent and the correlation coefficient between all different pairs of stocks is equal to 0.2. Calculate the standard deviation of a portfolio containing:
a) two stocks, b) three stocks, c) four stocks, d) 10 stocks, e) an infinite number of stocks
Calculations with Excel give a) 9.30 per cent, b) 8.20 per cent, c) 7.59 per cent, d) 6.35 per cent, e) 5.37 per cent. The portfolio’s standard deviation reduces when the number of different stocks increases.
Figure 8.15 Standard deviation calculations.
We know from financial theory that an investor will demand a higher return on a stock investment if the risk on the stock increases. The same goes for stock portfolios. In example 8.5 we studied how the expected return and standard deviation vary with composition of a portfolio containing two stocks. Let’s take a look at a similar example including a portfolio with three stocks.
The following information is given about the three stocks A, B and C:
Figure 8.16 Expected returns and standard deviations for portfolios of the stocks A, B and C.
Correlation coefficients between the stock’s returns are:
Calculate expected returns and standard deviations for different portfolios of the stocks A, B and C. Present the results graphically.
In the spreadsheet in figure 8.16 expected returns and standard deviations for 15 different portfolios are calculated and illustrated as dots in a diagram. A curve defining an outer limit for the portfolios has also been drawn. This curve will be discussed later in this chapter.
Example 8.11 illustrated expected returns and standard deviations for different portfolios of three stocks. A general case with expected return E(rp) as a function of standard deviation σp is illustrated in figure 8.17. Here each dot represents a specific composition of a portfolio. It’s obvious that portfolio a is a poor choice. We see that other portfolios with higher expected returns and/or lower standard deviations can be chosen by moving upwards and/or to the left in the figure. Thus it follows that the only portfolios investors will choose are the efficient portfolios represented by the bold curve. This curve is also called the efficient frontier.
Figure 8.17 The efficient frontier.
Assume now that investors can invest in a risk-free asset with a return of rf and that money can be lent to the same interest rate. Figure 8.17 includes a solid ray that goes out from rf and touches the efficient frontier. This is the capital market line which shows all the possible combinations between investments in the risk-free asset and the risky portfolio c. In an efficient market all investors choose the combination of risky assets constituting portfolio c, the market portfolio.
If an investor constructs a total portfolio that gives him a risk–return combination somewhere between rf and c on the capital market line, he holds a combination of the risk-free asset and portfolio c. If his combination is somewhere beyond c on the capital market line, he will also borrow money at the risk-free rate and invest all his money in portfolio c.
Calculating efficient portfolios in Excel
In Excel the efficient frontier (figure 8.17) must be calculated from two efficient portfolios. Assume that different portfolios can be constructed from N different stocks. If the two portfolios
are efficient, any combination of them is also efficient. The following combination (with a share a of portfolio X) is called portfolio Z:
If the expected returns of the portfolios X and Y are E(rX) and E(rY) respectively, then the expected return of portfolio Z becomes:
If the standard deviations of portfolios X and Y are σX and σY respectively, then the standard deviation of portfolio Z becomes:
In previous chapters we have learned that the covariance between portfolios X and Y can be calculated as XTKY. The standard deviation of portfolio Z can be calculated as:
To draw the efficient frontier we need the two efficient portfolios X and Y. Assume the matrix W is defined by the solution of the following system of equations. We recognize the matrix E(r) from equation 8.15 and the matrix K from equation 8.17. The value r is some return.
The system of equations can be formulated as:
The solution is:
Here K–1 is the inverse matrix of K, which means that the product K–1 K is the identity matrix I:
In Excel the function MINVERSE can be applied to calculate the inverse of a matrix.
The matrix W in equation 8.35 leads us to an efficient portfolio. Anyway, since matrix W does not give us the share of each stock directly, these must be calculated as:
Thus we have a matrix X with the shares x1, x2, …, xn in an efficient portfolio. The expected return and variance for portfolio X can be calculated by equations 8.16 and 8.19 respectively. Let’s study an example where we first find two efficient portfolios and then draw the efficient frontier.
Draw the efficient frontier for the portfolios containing the stocks in example 8.6.
In the spreadsheet in figure 8.18 the two portfolios X and Y have been calculated. In the cells J5:N9 we find the variance–covariance matrix and in the cells Q5:Q9 the expected returns for the stocks. For portfolio X we choose r = 0 per cent in cell K13. In the cells J16:J20 we calculate the matrix E(r) – r. As an example we see that cell J16 contain E(rj)–r = Q5–$K$13 = 4.5 per cent – 0.0 per cent = 4.5 per cent for the stock Axe. The matrix W must be calculated by equation 8.35 as K−1 (E(r) – r). In Excel we use the formula {=MMULT(MINVERSE($J$5:$N$9);$J$J6:$J$20)} where K–1 = MINVERSE($J$5:$N$9), and E(r) – r = $J$16:$J$20. Then portfolio X (L16:L20) is calculated from W by equation 8.37. As an example we see that cell L16 contains the formula =K16/SUM($K$16:$K$20). Similar calculations are done for matrix Y with r = 5.0 per cent.
Expected return for portfolio X is calculated as the weighted average E(rX) = x1 · E(r1) + … + x5 · E(r5) by the function SUMPRODUCT in cell J23. The standard deviation is calculated as 
in cell J25 by the formula =SQRT(MMULT(TRANSPOSE($L$16:$L$20); MMULT($J$5:$N$9; SLS16:SLS20))). Similar calculations are performed for portfolio Y in the cells O23 and O25.
Figure 8.18 Calculations for the efficient frontier.
With expected returns and standard deviations ready for the efficient portfolios X and Y, it is straightforward to calculate expected returns and standard deviations for several efficient portfolios and to draw a curve that shows expected return as a function of standard deviation. This is illustrated in figure 8.19, which is a section from the same spreadsheet as we saw in figure 8.18.
In the cells B22:D57 expected returns and standard deviations are calculated for portfolios with different shares a of X. Expected returns are calculated by equation 8.30 and standard deviations by equation 8.31. To be sure to get a curve that is long enough to illustrate the shape, we need to indicate values for a below 0 and over 1. With 0 < a < 1 we usually get only a part of the curve. The extent of the curve is dependent on the r values used to calculate the portfolios X and Y. Keep in mind that all efficient portfolios are located on the upper part of the curve (cf. figure 8.17).
Figure 8.19 The efficient frontier.
When calculating the portfolios X and Y, the value of r does not matter. Anyway, when drawing the curve that includes the efficient frontier, a sufficient number of shares for both X and Y is necessary.
Calculating the market portfolio with Excel
In figure 8.17 the market portfolio is indicated by point c. If investors can invest in a risk-free asset with return rf, the market portfolio is defined by the point where a solid ray that goes out from rf touches the efficient frontier. In Excel the market portfolio can be calculated in the same way as X or Y by setting r equal to rf. This is illustrated in example 8.13.
Pursue example 8.12 and find the market portfolio when rf = 3.0 per cent.
In the spreadsheet the composition of the market portfolio M is calculated in the cells L34:L38. This is done by the method used when calculating portfolio X in example 8.12 (with r = rf = 3.0 per cent). The expected return and standard deviation for the market portfolio are calculated in cells L40 and L41 respectively. The dot in figure 8.20 indicates the market portfolio.
Figure 8.20 Market portfolio.
The capital market line drawn in example 8.13 indicates combinations of risk-free investments and investments in the market portfolio as we move from the point rf on the vertical axis to the point for the market portfolio. If we move further upwards on the capital market line, it means that the investor also borrows money (at interest rf) and invests everything in the market portfolio.
The share of risk-free investments (and the share borrowed) can be calculated applying the principle of leverage. Thus we find the total portfolio with 50 per cent invested in risk-free assets and 50 per cent in the market portfolio at the middle of the line between rf (on the vertical axis) and the market portfolio. Figure 8.21 illustrates a total portfolio with 75 per cent invested in risk-free assets and 25 per cent in the market portfolio.
If af is the share invested in risk-free assets, then the expected return for the total portfolio can be calculated as:
The standard deviation can be calculated as:
Figure 8.21 Shares of risk-free investment in a portfolio.
Example 8.14 demonstrates how the shares af and 1 – af can be calculated for a total portfolio with specified expected return and standard deviation.
Consider the capital market line in example 8.13. Calculate the share of risk-free investment and investment in the market portfolio for a total portfolio with σp = 2.0 per cent and E(rp) = 4.0 per cent. Then calculate the ratio between loan and equity for a portfolio with σp = 5.5 per cent and E(rp) = 8.5 per cent.
The solution is illustrated in the spreadsheet in figure 8.23. In cell M46 we calculate the slope for the capital market line from the two points (0, rf) and (σM, E(rM)). The intersection with the vertical line is given by rf. The shaded cells are input cells for σp and E(rp).
In cell M51 the expected return E(rp) for the total portfolio is calculated from σp and the equation for the capital market line: E(rp) = ασp + β = M46*M50+M47 = 1.36 · 2.0 per cent + 3.0 per cent = 5.7 per cent.
In cell M53 the share of risk-free investment is calculated for the total portfolio. Consider the small triangle abc and the large triangle ABC in figure 8.22.
According to the principle of leverage the share of risk-free investment is c/C. The length c can be calculated applying the Pythagorean theorem: 
, where a = σM – σp and b = E(rM) – E(rp). In the same way the length C can be calculated as: 
, where A = σM – 0 and B = E(rM) – rf. The values σM = 2.84%, σp = 2.0%, E(rM) = 6.86%, E(rp) = 5.72%, and rf = 3.0% are found in the cells L41, M50, L40, M51 and M40 respectively. Thus the share of risk-free investment, c/C, can be calculated to 0.30 by the formula in cell M53 (shown in the spreadsheet’s formula bar).
Figure 8.22 Geometrical considerations.
Figure 8.23 Calculation of a portfolio’s composition.
8.6 Capital Asset Pricing Model (CAPM)
In this chapter we will study the correlation between the risk of a stock i and the risk of the market portfolio m (point c in figure 8.17). The sensitivity for the return on stock i with respect to changes in the return on the market portfolio is measured by stock’s β–value (beta value). A stock’s β-value tells us how much the expected return on the stock increases when the expected return on the market portfolio increases by one percentage point. Thus the β-value equals the slope of the line in figure 8.24.
Figure 8.24 Expected return for stock i as a function of expected return for the market portfolio.
The β-value is also defined as the covariance between the return on the stock and the return on the market portfolio, divided by the variance of the market portfolio:
If stock i and the market portfolio move perfectly together, then σim = σ2m and βi = 1. If βi > 1 then the return on stock i fluctuates more than the return on the market portfolio, and if βi < 1 the return on stock i fluctuates less. This means that βi is a measure of the stock’s market risk.
Returns for the market portfolio and for stock A have been registered over a period of 10 years. Calculate the β-value for stock A.
According to equation 8.40, the value for βA can be calculated as βA = σA, m/σ2m. This is done in cell C15 in the spreadsheet in figure 8.25. An alternative method is to plot the stock’s return as a function of the market portfolio’s return, and then carry out a linear regression. The β-value for stock A is then given by the slope of the regression line.
Figure 8.25 Calculation of β-value.
Suppose we have a hypothetical stock market containing equal amounts of the five stocks A–E showing the following returns (per cent) over eight years. Calculate the β-value for each stock.
In the spreadsheet in figure 8.26, each yearly return for the market is calculated as the average return for the stocks. A stock’s β-value can be calculated as the covariance between the stock’s return and the market’s return, divided by the variance of the market’s return. This is done in the cells B14:F14 for stocks A–E respectively. A stock’s β-value can also be found as the slope of the regression line between the stock’s return and the market’s return. This is done in the cells B16:F16.
Figure 8.26 Calculations of β-values.
The hypothetical stock market in example 8.16 contained only five stocks. For a real stock market a market index can be used as an estimate for the market portfolio, although a market index will never be identical with the theoretical market portfolio. The next example illustrates how β-values for five different stocks can be calculated when the FTSE 100 index is used as an estimate for the market portfolio.
Monthly prices for five different stocks and the index FTSE 100 are listed in figure 8.27 for a five-year period. (Rows 11–60 are hidden.) Based on this information, calculate the β-values for the five stocks. Discuss the validity of these β-values.
Figure 8.27 Monthly prices for 5 different stocks and the index FTSE 100.
We start by calculating all monthly returns as illustrated in the spreadsheet in figure 8.28. A return for month t is calculated as rt = ln(Pt+1/Pt). For the Barclay stock, the return for September 2009 is calculated as In(370.00/375.10) = –0.0137 = – 1.37 per cent. The β–value for a stock i is calculated as 
. The R2 value for the Barclay stock is calculated by the formula CORREL(J5:J61;SOS5:SOS61)^2. The low R2 values indicate that the different stocks move independently of the FTSE 100 index.
Figure 8.28 Calculation of β-values.
When risk is measured, the risk-free return rf is used as a reference. The return is constant for a risk-free investment, such as a bank deposit or a treasury bond. Thus, β = 0 for a risk-free investment.
If the return of the market portfolio is E(rm), then the increase in expected return becomes E(rm) – rf when you invest in the market portfolio instead of the risk-free asset. E(rm) – rf is termed the market risk premium. The Capital Asset Pricing Model, CAPM, states that the expected return of stock i can be calculated as:
The Capital Asset Pricing Model is illustrated in figure 8.29 and tells us that a stock’s expected return depends on its β-value. A stock with βi = 1 has the same expected return as the market portfolio. If a stock has βi > 1, then E(ri) > E(rm), and if the stock has 0 < βi < 1, then rf < E(ri) < E(rm).
The Capital Asset Pricing Model and equation 8.41 can also be applied to calculate the required rate of return for an investment project. If a project’s risk is equal to the risk of a stock (and a company) with a known β-value, the same β-value can be used in the calculation of the required rate of return.
Financial scientists have for many years discussed whether the Capital Asset Pricing Model coincides with real data from stock markets. The next hypothetical example illustrates such a problem.
Figure 8.29 Expected return for stock i as a function of βi.
The following β-values and returns are given for 10 different stocks:
Discuss the validity of the Capital Asset Pricing Model in this hypothetical stock market. If we assume the CAPM to be valid, what is the risk-free return in this case?
The spreadsheet in figure 8.30 includes a plot of E(r) as a function of β. We obtain the regression line by pressing the right mouse button while standing on one of the dots in the diagram and choosing Add Trendline. Under Trendline Options in the Format Trendline dialogue box that appears, we choose Linear, Display Equation on chart, and Display R-squared value on chart. Then the R2-value and the equation for the regression line appear in the diagram.
The value R2 = 0.32 tells us that only 32 per cent of the changes in E(r) is due to changes in β when we go from one stock to another. Thus the CAPM corresponds poorly with these data. The equation E(r) = 0.0265β + 0.072 tells us that the intercept, and thus the risk-free return, is 7.2 per cent in this case.
Figure 8.30 The Capital Asset Pricing Model.
The amount Value at Risk, VaR, is a risk measure and defined as the maximum loss expected on a specific portfolio of financial assets at a given confidence level. When calculating VaR we assume that the return on the portfolio follows a normal distribution.
A pension fund may for instance have a VaR of $100 million in loss per year at a confidence level of 99 per cent as a target. This means that the possibility for the fund to lose more than $100 million per year should be 100 per cent – 99 per cent = 1 per cent. Remember that a 99 per cent confidence level corresponds to the 1 per cent quantile in the normal distribution.
Monthly returns on a stock portfolio have been observed for a long time. Based on these observations the monthly expected return is estimated at 5 per cent with a standard deviation of 15 per cent. The market value of the portfolio is $500 million. These values are written in the cells B3:B5 in the spreadsheet in figure 8.32.
The expected value of the portfolio in a month must be $500(1+0.05) million = $525 million as shown in cell B7. The standard deviation is $500 · 0.15 million = $75 million (cell B8). If we assume that the value of the portfolio in a month follows a normal distribution with an expected value and a standard deviation of $525 million and $75 million respectively, the normal distribution looks like figure 8.31.
Figure 8.31 Normal distribution.
Figure 8.31 is drawn as a scatter chart in Excel with the values $200–$900 million on the x-axes and the values on the y-axis calculated with the function NORMDIST. With an x-value in cell A22 the formula NORMDIST(A22;$B$7;$B$8;FALSE) calculates the according y-value. The argument FALSE must be included to get values from the probability density function.
In figure 8.31 we find a mark for the 1 per cent quantile in the left part of the normal distribution. This 1 per cent quantile is $350.52 million and tells us that there is a 1 per cent probability for obtaining a value on the portfolio less than $350.52 million after a month. In the spreadsheet this quantile is calculated in cell B11 with the function NORMINV(B9;B7;B8). A value of $350.52 million represents a loss of $525 million – $350.52 million = $174.48 million (in cell B12). This means that there is a 1 per cent probability of obtaining a loss greater than $174.48 million after a month.
Figure 8.32 The NORMINV function.
If we choose a 5 per cent quantile, the VaR is $123.36 million. Thus there is a 5 per cent probability of obtaining a loss greater than $123.36 million after a month.
Problems
8-1. The following prices for a stock have been observed at the end of each month. Calculate returns for the months February–June, the average return for this period, and the standard deviation. No dividends were paid on the stock in this period.
8-2. The following monthly returns (per cent) have been observed for two different stocks. Calculate the average return, variance, and standard deviation for each stock. Calculate also the covariance and the correlation coefficient between the returns of the stocks.
8-3. Returns per year for the stocks A and B are listed below (per cent). Calculate expected returns and standard deviations for different portfolios of A and B, and illustrate your results graphically.
8-4. The following expected returns and standard deviations (per cent) have been observed for three different stocks:
The correlation coefficients between the returns of the stocks are as follows:
Assume a risk-free return of 5 per cent and a market consisting only stocks A and B. Estimate the expected return and standard deviation for the market portfolio.
8-5. The following expected returns and standard deviations have been observed for stocks A–D (per cent):
Correlation coefficients between the stocks’ returns are as follows:
Assume a risk-free return of 4 per cent and a market consisting only the stocks A–D. Estimate the expected return and standard deviation for the market portfolio.
8–6. Returns per year (per cent) have been observed for seven different stocks:
Calculate expected return and standard deviation for a portfolio with the following shares of stocks 1–7:
8-7. Returns per month have been observed for 10 different stocks (per cent):
Calculate expected return and standard deviation for a portfolio with the following shares:
8-8. Returns per year have been observed for five different stocks (per cent):
Calculate expected return and standard deviation for a portfolio with the following shares:
Find the composition of the portfolio that minimizes the standard deviation.
8-9. The spreadsheet for this problem includes monthly prices from the London Stock Exchange for five different stocks over a period of five years. (The rows 8–60 are hidden.)
Figure 8.33 Monthly prices from the London Stock Exchange.
Calculate expected return and standard deviation for the following portfolio:
8-10. Returns per year have been observed for five different stocks (per cent):
Draw the curve for all efficient portfolios and identify the market portfolio. Assume a risk-free return of 5 per cent.
8-11. The spreadsheet for this problem includes monthly prices from the London Stock Exchange for five different stocks over a period of five years. (The rows 8–60 are hidden.) Draw the curve for all efficient portfolios and identify the market portfolio. Assume a risk-free return of 0.1 per cent.
Figure 8.34 Monthly prices from the London Stock Exchange.
8-12. Returns for the market portfolio and stock A have been observed over a 10-year period (per cent). Calculate the f-value for stock A.
8-13. Monthly returns have been observed for seven different stocks (A–G) and for the entire market (per cent). Calculate the β-value for each stock.
8-14. A hypothetical stock market consists of equal amounts of the five different stocks A–E with the following returns (per cent). Calculate the β-value for each stock.
8-15. Returns and β-values are given for nine different stocks. Discuss the validity of the Capital Asset Pricing Model in this case.
8-16. A portfolio of stocks has an expected return of 0.35 per cent per week and a standard deviation of 0.20 per cent. The present market value of the portfolio is $1,000,000. Calculate VaR at 1 per cent for a period of one week. Draw the curve for the normal distribution of the portfolio’s weekly return.
Note
1 The estimate for the variance in equation 8.3 may be indicated by s2. The symbol usually indicates the expected value of the variable. The right side of equation 8.3 is actually the expected value when the number of observations approaches infinity.