# How to Calculate a Standard Deviation from a P Chart C DocstocWorkingpdf0113361a 19de 4ab8 bffd efbfde3c5acb

Document Sample

```					                 C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.1/14

Part 3: Portfolio models (chapters 11-16)
ch 11: What is risk? Read section 11.1 & skim the rest.
ch 12: Statistics for portfolios. Read.
ch 13: Efficient frontiers. Skip first 18 pages & read.
ch 15: SML & performance measurement. Read lecture note first. If you need
more details, then read relevant sections in this chapter.
ch 16: SML & cost of capital: skip.

Ch 12: Statistics for portfolio
12.1 Basic statistics for asset returns (sections 12.1 and 12.3)

Calculation of rate of return:

r(t) = [P(t) + d(t)]/P(t-1) – 1.

Open ch12.xls:GM calculate returns, average, variance and standard deviation.

A            B           C       D                  E
1           Price and dividend data for General Motors (GM)
Closing              Annual
2      Date        Price   Dividend return
3    29-Dec-89    42.2500       -
4    31-Dec-90    34.3750      3.00    -11.54% <-- =(C4+B4)/B3-1
5    31-Dec-91    28.8750      1.60    -11.35% <-- =(C5+B5)/B4-1
6    31-Dec-92    32.2500      1.40     16.54%
7    31-Dec-93    54.8750      0.80     72.64%
8    30-Dec-94    42.1250      0.80    -21.78%
9    29-Dec-95    52.8750      1.10     28.13%
10   31-Dec-96    55.7500      1.60      8.46%
11   31-Dec-97    60.7500      5.59     19.00%
12   31-Dec-98    71.5625      2.00     21.09%
13   31-Dec-99    72.6875     14.15     21.34%
14
15 Average return                       14.25% <-- =AVERAGE(D4:D13)
16 Variance of return                   0.0638 <-- =VARP(D4:D13)
17 Standard deviation of return         25.25% <-- =STDEVP(D4:D13)

Note: 1. Which one to use: varp( ) or var( )? For practical purposes, it does not
matter. Following the textbook, we use varp( ) and stdevp( ).

2. Of course, you can use the definition of variance to calculate it:

var(x) = [ (x – E(x))2 ] / N
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.2/14

Adjusting returns for stock-splits:           (section 12.2)

Example: You bought a share of Microsoft for \$87 on 12/29/89. Microsoft split its
stock 2 for 1 in 1990, meaning your one share became two shares. You sold your
shares on 12/31/90 for \$75.25 per share. What was your rate of return?

\$75.25*2/\$87 – 1 = 72.99%

Open ch12.xls:MSFT. See how to adjust returns for a stock-split.

A        B                  C            D            E      F                  G
1 PRICE AND STOCK SPLIT            DATA FOR MICROSOFT (MSFT)
Closing      Stock
2       Date           Price       split
3     29-Dec-89        87.0000
4     31-Dec-90        75.2500    2.0 for 1
5     31-Dec-91       111.2500    1.5 for 1
6     31-Dec-92        85.3750    1.5 for 1
7     31-Dec-93        80.6250       no
8     30-Dec-94        61.1250    2.0 for 1
9     29-Dec-95        87.7500       no
10    31-Dec-96        82.6250    2.0 for 1
11    31-Dec-97       129.2500       no
12    31-Dec-98       138.6875    2.0 for 1
13    31-Dec-99       116.7500    2.0 for 1
Cumulative
14      Date           Price       split        factor     price    return
15    29-Dec-89        87.0000                     1         87.00
16    31-Dec-90        75.2500 2.0 for 1           2        150.50 72.99% <-- =E16/E15-1
17    31-Dec-91       111.2500 1.5 for 1           3        333.75 121.76% <-- =E17/E16-1
18    31-Dec-92        85.3750 1.5 for 1          4.5       384.19 15.11%
19    31-Dec-93        80.6250      no            4.5       362.81   -5.56%
20    30-Dec-94        61.1250 2.0 for 1           9        550.13 51.63%
21    29-Dec-95        87.7500      no             9        789.75 43.56%
22    31-Dec-96        82.6250 2.0 for 1          18      1,487.25 88.32%
23    31-Dec-97       129.2500      no            18      2,326.50 56.43%
24    31-Dec-98       138.6875 2.0 for 1          36      4,992.75 114.60%
25    31-Dec-99       116.7500 2.0 for 1          72      8,406.00 68.36%
26
27 Average return                                                   62.72% <-- =AVERAGE(F16:F25)
the product of all the splits:
28 Variance of return         72 = 2*1.5*1.5*2*2*2*2                14.43% <-- =VARP(F16:F25)
29 Standard deviation of return                                     37.99% <-- =SQRT(F28)

Note:
percentage difference of prices will be the rate of return between two dates.
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.3/14

So, prices in Yahoo are not actual trade prices. CRSP prices are actual trade prices,
and CRSP returns are adjusted for splits and other corporate events.

12.3 Covariance and correlation

Open ch12.xls: GM&MSFT and calculate mean, variance, standard deviations,
covariance and correlation.

A                   B            C                      D
1                             GM and MSFT, annual return data

2             Date              GM return MSFT return
3           31-Dec-90            -11.54%      72.99%
4           31-Dec-91            -11.35%     121.76%
5           31-Dec-92             16.54%      15.11%
6           31-Dec-93             72.64%      -5.56%
7           30-Dec-94            -21.78%      51.63%
8           29-Dec-95             28.13%      43.56%
9           31-Dec-96              8.46%      88.32%
10          31-Dec-97             19.00%      56.43%
11          31-Dec-98             21.09%     114.60%
12          31-Dec-99             21.34%      68.36%
13
14   Average return                 14.25%        62.72% <-- =AVERAGE(C3:C12)
15   Variance of return              6.38%        14.43% <-- =VARP(C3:C12)
16   Standard deviation of return   25.25%        37.99% <-- =STDEVP(C3:C12)
17   Covariance of returns          -0.0552              <-- =COVAR(B3:B12,C3:C12)
18   Correlation of returns         -0.5755              <-- =CORREL(B3:B12,C3:C12)
19   Correlation using formula      -0.5755              <-- =B17/(B16*C16)

Note:
1. You can use the definition of covariance to calculate it:

cov (x,y) =  [ (x – E(x))(y – E(y)) ] / N

2. Correlation () is always between –1 (perfectly negative) and +1 (perfectly
positive). When  = -1 or 1, x and y has a linear relation: y = a + bx. (Details are
in p. 17-21 of ch. 12.)

12.4 Portfolio mean and variance

Mean and variance of returns of a portfolio

We consider a portfolio of two risky assets, GM and Microsoft. Portfolio’s return in
month t is equal to the weighted average of returns of GM and Microsoft in month t.

rp = xGM rGM + (1 - xGM)rMSFT.
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.4/14

Once you find out portfolio’s return for each month in your sample, you can easily
calculate the mean and variance of portfolio’s return.

Open ch12.xls: 2-asset port. Calculate portfolio’s return each month, and mean
and variance of a portfolio’s returns.

A         B          C    D       E                F
1            CALCULATING PORTFOLIO RETURNS AND THEIR STATISTICS
2 Proportion of GM     0.5
3 Proportion of MSFT   0.5 <-- =1-B2
Portfolio
4         Date           GM        MSFT              return
5        Dec-90        -11.54%     72.99%                  30.73% <-- =\$B\$2*B5+\$B\$3*C5
6        Dec-91        -11.35%    121.76%                  55.21%
7        Dec-92         16.54%     15.11%                  15.82%
8        Dec-93         72.64%      -5.56%                 33.54%
9        Dec-94        -21.78%     51.63%                  14.93%
10        Dec-95         28.13%     43.56%                  35.84%
11        Dec-96          8.46%     88.32%                  48.39%
12        Dec-97         19.00%     56.43%                  37.71%
13        Dec-98         21.09%    114.60%                  67.85%
14        Dec-99         21.34%     68.36%                  44.85%
15
16   Mean                14.25%     62.72%                  38.49% <-- =AVERAGE(E5:E14)
17   Variance             6.38%     14.43%                   2.44% <-- =VARP(E5:E14)
18   St. dev.            25.25%     37.99%                  15.62% <-- =STDEVP(E5:E14)
19   Covariance                     -0.0552
20   Correlation                    -0.5755
21
22   Direct calculation of portfolio mean and variance
23   Portfolio mean      38.49% <-- =B2*B16+B3*C16
24   Portfolio variance    2.44% <-- =B2^2*B17+B3^2*C17+2*B2*B3*C19
25   Portfolio st. dev.  15.62% <-- =SQRT(B24)

Note:
(1) This is the long-way.
(2) You can use the following formula to calculate directly portfolio mean and
variance: Do this calculation now.

E(rp) = xGM E(rGM) + (1 - xGM) E(rMSFT), [xMSFT = 1 - xGM ]

var(rp) = x2GM var(rGM) + x2MSFT var(rMSFT) + 2 xGMxMSFT cov(rGM, rMSFT).

Portfolio frontier: You just calculated the mean and variance of a portfolio. The
calculation is based on a portfolio weight, i.e., 50% for GM, and 50% for MSFT. Let
us change the portfolio weight for GM from 0% to 100%, and calculate the mean
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc                                   11/22/2010 p.5/14

and variance of each portfolio. When you plot the mean (on Y-axis) and standard
deviation (on x-axis) of all portfolios, you get the portfolio frontier.

Open ch12.xls:port frontier. (i) Fill up the table of portfolio variance, standard
deviation and mean. (ii) Plot portfolio mean and standard deviation.

A           B            C        D                      E                                F                      G           H
1            Portfolio frontier
2                   GM        MSFT                                                     Portfolio Mean and Standard Deviation
3   Mean           14.25%     62.72%
4   Variance        6.38%     14.43%                                             70%

p)
Portfolio return mean, E(r
5   St. dev.       25.25%     37.99%                                             60%
6   correlation               -0.5755                                            50%
7                              0.0000
40%
portfolio              Portfolio
weight of Portfolio    standard Portfolio                                   30%

8      GM      Variance     deviation mean                                       20%

9      0%       14.43%        37.99%   62.72%                                    10%
10      10%      10.76%        32.80%   57.87%                                    0%
11      20%        7.72%       27.79%   53.03%                                      12%     17%     22%      27%     32%       37%    42%
12      30%        5.33%       23.08%   48.18%                                              Portfolio return standard deviation, p
13      40%        3.56%       18.88%   43.33%
14      50%        2.44%       15.62%   38.49%
15      60%        1.95%       13.98%   33.64%
16      70%        2.11%       14.51%   28.79%
17      80%        2.89%       17.01%   23.95%
18      90%        4.32%       20.78%   19.10%
19     100%        6.38%       25.25%   14.25%
20                                                                                  =A19*\$B\$3+(1-A19)*\$C\$3
=SQRT(C19)
21
=A19^2*\$B\$4+(1-A19)^2*\$C\$4+2*A19*(1-A19)*B\$5*C\$5*\$C\$6
22

Note: In filling up the table in above workbook (ch11.xls:port frontier), you can
use data|table function.

Open ch12.xls: data table. Fill up the table by using the data|table function.

Steps:
(i)   Input portfolio weights of GM in column A (from A13-A23).
(ii)  Input “=B9” in cell B12;
Input “=C9” in cell C12;
Input “=D9” in cell D12.
(iii) Highlight entire table (A12:D23).
(iv) Click data|table.
(v)   Input “=B6” in column input cell box, and click ctrl+Alt+Enter.
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.6/14

A            B           C          D
1    Using data|table: Portfolio frontier
2                       GM         MSFT
3    Mean              14.25%       62.72%
4    St. dev.          25.25%       37.99%
5    Correlation                    -0.5755
6    portfolio weight       0.1     90.00%
7
8                     port var    port std port mean
9                       0.1076      0.3280    0.5787
10
portfolio     Portfolio Portfolio Portfolio
11    weight: GM     Variance   st dev    mean
12                     0.1076     0.3280    0.5787
13        0%          14.43%     37.99%    62.72%
14       10%          10.76%     32.80%    57.87%
15       20%            7.72%    27.79%    53.03%
16       30%            5.33%    23.08%    48.18%
17       40%            3.56%    18.88%    43.33%
18       50%            2.44%    15.62%    38.49%
19       60%            1.95%    13.98%    33.64%
20       70%            2.11%    14.51%    28.79%
21       80%            2.89%    17.01%    23.95%
22       90%            4.32%    20.78%    19.10%
23       100%           6.38%    25.25%    14.25%
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc                                        11/22/2010 p.7/14

Ch. 13 Efficient frontier
13.1 The advantage of diversification (13.1 (skip) and 13.5)

Open ch13.xls:port frontier. Change correlation between GM and MSFT from
+1, 0.5, 0.0, -1.0. What happens to the portfolio frontier?

corr=1                        corr=0.5                     corr=0                      corr=-1
70%

60%

50%

40%

30%

20%

10%

0%

0.0000      0.0500      0.1000      0.1500    0.2000     0.2500    0.3000      0.3500    0.4000

Summary: The lower the correlation, the greater the diversification benefits.

13.2 Efficient frontier and minimum variance portfolio (p.20-21, and 13.4)
Expected Return and Standard Deviation of Portfolio Return

0.70
The portfolios on the top
0.60                                         are the efficient frontier--
portfolios with a positive
Expected portfolio return, E(r p)

0.50

0.40                                                                   This curve is called
The minimum                                     portfolio frontier-- portfolios
variance portfolio                              with the lowerest risk
0.30                                                                   among portfolios that give
the same expected return.
0.20

0.10

0.00
0.00         0.05         0.10             0.15         0.20         0.25            0.30        0.35       0.40
Standard deviation of portfolio return, p

Note: We have considered only portfolios of two risky assets, but the portfolio can
contain any number of risky assets.
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.8/14

Finding the minimum variance portfolio: Solver or algebra

(1) Algebra (Appendix 1 of ch. 13)

Let x denote portfolio weight for GM. Choose x to minimize the portfolio variance.

Min (1/2) [ x2var(rGM) + (1-x)2 var(rMSFT) + 2 x(1-x) cov(rGM, rMSFT) ]
{x}

The first order condition for this optimization problem is:

x var(rGM) + (1-x) var(rMSFT) + (1-2x) cov(rGM, rMSFT) = 0.

Solving the first order condition for x yields,

x = [var(rMSFT) - cov(rGM, rMSFT)] / [var(rGM) + var(rMSFT) -2 cov(rGM, rMSFT)].

(2) Using Excel Solver

Open ch13.xls:Min-var solver.

A                   B        C        D         E           F
1    CALCULATING THE MINIMUM-VARIANCE PORTFOLIO WITH SOLVER
2                                   GM    MSFT
3    Average                       14.25% 62.72%
4    Variance                       6.38% 14.43%
5    Sigma                         25.25% 37.99%
6    Covariance of returns         -5.52%
7
8    Portfolio return and risk
9    Percentage in GM               62.64%
10   Percentage in MSFT             37.36% <-- =1-B9
11
12   Expected portfolio return      32.36% <-- =B9*C3+B10*D3
13   Portfolio variance              0.0193 <-- =B9^2*C4+B10^2*D4+2*B9*B10*C6
14   Portfolio standard deviation   13.90% <-- =SQRT(B13)

Solver: Target cell (=B13); By changing cell (=B9).

Open ch13.xls:Min-var formula. Calculate mean and variance of minimum
variance portfolio, using formula.
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.9/14

Ch. 14 CAPM

14.1 Combining a portfolio of risky assets with a riskless asset

Once we know the means, variances and correlations among assets, we can draw
the portfolio frontier of risky assets. You pick a portfolio from the portfolio frontier,
and call it k. Suppose there is a riskless asset, and you want to combine the
riskless asset with portfolio k. What are the mean and variance of the portfolio of
riskless and portfolio k?

k: portfolio of risky asset      riskless asset
mean                               E(rk)                         rf
standard deviation                 (rk)                        0.0
correlation (rk, rf)                          0.0

Let rp denote return on the portfolio of k and riskless asset. Let x denote portfolio
weight of k. Then we have

E(rp) = x E(rk) + (1-x) rf, and

var(rp) = x2 var(rk) + (1-x)2 var(rf) + 2 x (1-x) cov(rk, rf)

= x2 var(rk) + (1-x)2 0.0         + 2 x (1-x) 0.0, so

(rp) = x k.

When you plot the mean and standard deviation of portfolio p, you will get a
straight line connecting the riskless rate and the portfolio p.

Open ch14.xls:2stocks, 1.5: Calculate the mean and standard deviation of the
portfolio of a risky portfolio and riskless asset.

Fill up the shaded area, and see what you get on the chart. You should get a
straight line.

[Optional material] Proof for getting a straight line.
Since (rp) = x k, x =(rp)/k. Substitute this into E(rp):

E(rp) = x E(rk) + (1-x) rf = rf + x [E(rk) - rf ] = rf + [(rp)/k] [E(rk) - rf ].

Recall Y = a + b*X plots a straight line with slope=b, and intercept=a. Treat E(rp)
as Y variable, (rp) as X variable. Then, we have a straight line with slope=[E(rk) -
rf ]/k, and intercept= rf.                    [End of optional material].
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc        11/22/2010 p.10/14

A                   B            C               D    E      F            G        H
1    TWO STOCKS AND A RISK-FREE ASSET                                                                       % in k       st dev   mean
2                        Stock 1 Stock 2                                                                          0.0       0.00% 2.00%
3    Average return        7.00% 15.00%                                                                           0.1       0.75% 2.58%
4    Sigma                 8.00% 14.00%                                                                           0.2       1.49% 3.16%
5    Correlation             0.10                                                                                 0.3       2.24% 3.74%
6    Risk-free rate           2%                                                                                  0.4       2.99% 4.32%
7                                                                                                                 0.5       3.74% 4.90%
8    weight in portfolio k                                            0.9             0.1                         0.6       4.48% 5.48%
9    mean of portfolio k                                           0.0780                                         0.7       5.23% 6.06%
10   st dev of portfolio k                                         0.0747                                         0.8       5.98% 6.64%
11                                                                                                                0.9       6.72% 7.22%
12                                                                                                                1.0       7.47% 7.80%
13                                           Expected Return and Standard Deviation of
14                                                       Portfolio Return                                   % in k       st dev   mean
15                                                                                                                -0.3     0.1812 0.1740
20%
16                                                                                                                -0.2     0.1672 0.1660
18%
17                                                                                                                -0.1     0.1534 0.1580
Expected portfolio return, E(r
p)

16%
18                                                                                                                 0.0     0.1400 0.1500
19                                         14%                                                                     0.1     0.1270 0.1420
20                                         12%                                                                     0.2     0.1147 0.1340
g
21                                         10%                                                                     0.3     0.1032 0.1260
22                                         8%                                                                      0.4     0.0928 0.1180
23                                         6%                                                                      0.5     0.0840 0.1100
24                                         4%                                                                      0.6     0.0773 0.1020
25                                         2%              h                                                       0.7     0.0733 0.0940
26                                         0%                                                                      0.8     0.0724 0.0860
27                                               0%           5%          10%               15%       20%          0.9     0.0747 0.0780
28                                                      Standard deviation of portfolio return, p                 1.0     0.0800 0.0700
29                                                                                                                 1.1     0.0877 0.0620
30                                                                                                                 1.2     0.0973 0.0540
31                                                                                                                 1.3     0.1082 0.0460
32                                                                                                                 1.4     0.1201 0.0380

Note:  A point on the blue line represents a particular portfolio of portfolio k and
riskless asset. Portfolios g and h are indicated on the blue line.

 For the blue line, note that
slope = [E(rp)-rf]/p, and intercept = rf.

[E(rp)-rf]/p is known as the Sharpe ratio, which is the reward-risk measure.

14.2 and 14.3 Finding out the tangency portfolio: Sharpe ratio

 Can you have a better combination of a risky asset portfolio and riskless asset
than the ones on blue line? *Open ch13.xls:2stocks,2-4. Yes. The best line is
called the Capital Market Line (CML).
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.11/14

Note:  The Capital Market Line is tangent to the market portfolio, M. Why? In
equilibrium, supply equals to demand. So, the tangency portfolio should contain all
risky assets in the economy in a proportion to a stock’s market value.

 Now, the efficient frontier is equal to the Capital Market Line, whereas previously
it was top part of the curve.

 Open ch14.xls:CML portfolios. If portfolio weight on market portfolio is: (This
is section 14.2.)
zero: you invest all of your money in riskless asset,
one: you invest all of your money in market portfolio,
0-1: your portfolio is somewhere between riskfree rate and point M,
> 1: you borrow money at riskfree rate and invest in market portfolio.

 How do you find out tangency portfolio? It has the highest Sharpe ratio. Use
Solver to find out the tangency portfolio. Open ch14.xls: solver Sharpe.

A             B         C         D           E               F

PORTFOLIO RETURNS WITH A RISK-FREE ASSET
1        THE SHARPE RATIO: Using Solver to get the tangency portfolio
2                 Stock A Stock B Risk-free
3    mean return    7.00% 15.00%     2.00%
4    st dev         8.00% 14.00%
5    covariance    0.0011
6
7    Portfolio        % in A   % in B
8                     24.00%   76.00%
9
10   mean return      13.08% <-- =B8*B3+C8*C3
11   st dev           11.00% <-- =SQRT(B8^2*B4^2+C8^2*C4^2+2*B8*C8*B5)
12
13   Sharpe ratio     1.0073 <-- =(B10-D3)/B11
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.12/14

Note:  You can get the following Answer Report from Solver. After clicking
“Solve” button, check “Restore original values” box, and click “Answers”.

Worksheet: [ch13.xls]Sharpe ratio
Report Created: 2/28/2004 11:33:16 AM

Target Cell (Max)
Cell         Name       Original Value Final Value
\$B\$13 Sharpe ratio % in A         1.0073      1.0716

Cell         Name        Original Value Final Value
\$B\$8 % in A                       24.00%      51.81%

Constraints
NONE

14.4 Security Market Line (SML)

The Capital Market Line describes risk-return relation for a portfolio on the straight
line, which represents the efficient frontier. This risk-return relation holds only
for portfolios on the efficient frontier.

Question: What is risk-return relation for any individual stock or any portfolios
(of risky assets, or of risky assets and riskless asset)?

Answer: The security market line, or the Capital Asset Pricing Model (CAPM).

The CAPM states that the expected return of any individual asset or any portfolio is
determined by the asset’s risk (called  ), the risk-free rate, and the market
portfolio (or, the portfolio which maximizes the Sharpe ratio). The CAPM states:

E(rp) = rf + p [E(rM) – rf], where p  cov(rp, rM)/var(rM).

Note:  The portfolio p can be a combination of any number of risky assets, so it
can be a single risky asset. The portfolio p can also be a combination of any
number of risky assets and the risk-free asset.

 Note that beta of a portfolio is equal to weighted average of beta of individual
stock in the portfolio. For example, beta of a portfolio of GM and MSFT is given by

p = xGM GM + xMSFT MSFT.

 In next chapter, we use the CAPM relation to evaluate investment performance of
a fund manager.
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.13/14

Appendix: Multiple assets

So far, we have only considered portfolios of two risky assets. How can we deal
with more than two assets? It is most convenient to use matrices in this case. We
will briefly look at basics in matrix operation.

Refer to the hand-out on matrix: definition, matrix addition, transpose,
multiplication, inverse, solving a linear equation system.

Open Matrix.xls: solver 4 assets. Find out the tangency portfolio

Recall how we find out the Capital Market Line: we use Excel Solver to find out
portfolio weights, which maximize the Sharpe ratio. We use the same approach
here. The only difference is that we use matrix operation to calculate the mean and
variance of portfolio returns.

A                 B           C           D            E              F              G
1    Finding tangency portfolio: Multiple assets
2    Variance-covariance matrix                     mean returns
3         0.40          0.03        0.02       0.00    0.06
4         0.03          0.20        0.00      -0.06    0.05
5         0.02          0.00        0.30       0.03    0.07
6         0.00         -0.06        0.03       0.10    0.08
7                    riskfree    riskfree
8                       0.02        0.05
9                  portfolio A portfolio B
10                    0.0506      0.0314 weight #1
11                    0.2857      0.2059 weight #2
12                    0.0604      0.0597 weight #3
13                    0.6034      0.7031 weight #4
14   portfolio          1.00        1.00    sum
15   mean             0.0698      0.0726 <--=MMULT(TRANSPOSE(C10:C13),\$E3:\$E6)
<-- =MMULT(TRANSPOSE(C10:C13),
16   variance               0.0374      0.0450   MMULT(\$A\$3:\$D\$6,C10:C13))
17   st dev                 0.1933      0.2121
18   Sharpe ratio           0.2577      0.1065   <-- =(C15-C8)/C17
19
20   cov(r(a), r(b))        0.0395   <-- =MMULT(TRANSPOSE(B10:B13), MMULT(A3:D6,C10:C13))

Note:
C:\Docstoc\Working\pdf\0113361a-19de-4ab8-bffd-efbfde3c5acb.doc   11/22/2010 p.14/14

 Let xA and xB denote portfolio weights for portfolio A and B. Let V denote
variance-covariance matrix in A3::D6. Then variance of returns of portfolio A, and
covariance of returns of portfolio A and B are given by

var(rA) = xAT V xA
cov(rA, rB) = xAT V xB

 Portfolio A maximizes the Sharpe ratio when risk-free rate is 2%, and Portfolio B
maximizes the Sharpe ratio when risk-free rate is 5%. Both are on efficient
frontier.

 Suppose you want to draw entire efficient frontier. How can we do this? We only
know two portfolios on efficient frontier.

There is a very convenient property of efficient frontier: linear combination of two
efficient portfolio generates entire efficient frontier. Remember how we draw
portfolio frontier in section 12.5. Follow exactly the same procedure.

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 164 posted: 11/22/2010 language: English pages: 14
Description: How to Calculate a Standard Deviation from a P Chart document sample
How are you planning on using Docstoc?