UN-5B

A
Month
0
1
2
3
4
5
6
7
8
9
10
11
12

B
C
Stock prices
Stock A
Stock B
25.00
45.00
24.12
44.85
23.37
46.88
24.75
45.25
26.62
50.87
26.50
53.25
28.00
53.25
28.88
62.75
29.75
65.50
31.38
66.87
36.25
78.50
37.13
78.00
36.88
68.23

CALCULATING THE RETURNS

Month
0
1
2
3
4
5
6
7
8
9
10
11
12

Stock A
Price
Return
25.00
24.12
-3.58%
23.37
-3.16%
24.75
5.74%
26.62
7.28%
26.50
-0.45%
28.00
5.51%
28.88
3.09%
29.75
2.97%
31.38
5.33%
36.25
14.43%
37.13
2.40%
36.88
-0.68%

Monthly mean
Monthly variance
Monthly stand. dev.

3.24%
0.23%
4.78%

Annual mean
Annual variance
Annual stand. dev.

38.88%
2.75%
16.57%

Stock B
Price
Return
45.00
44.85
-0.33%
46.88
4.43% <-- =LN(E23/E22)
45.25
-3.54%
50.87
11.71%
53.25
4.57%
53.25
0.00%
62.75
16.42%
65.50
4.29%
66.87
2.07%
78.50
16.03%
78.00
-0.64%
68.23 -13.38%
3.47% <-- =AVERAGE(F22:F33)
0.65% <-- =VARP(F22:F33)
8.03% <-- =STDEVP(F22:F33)
41.62% <-- =12*F35
7.75% <-- =12*F36
27.83% <-- =SQRT(F40)

COVARIANCE AND VARIANCE CALCULATION

Stock A
Stock B
Return Return-mean
Return Return-mean
-0.0358
-0.0316
0.0574
0.0728
-0.0045
0.0551
0.0309
0.0297
0.0533
0.1443
0.0240
-0.0068

-0.0682
-0.0640
0.0250
0.0404
-0.0369
0.0227
-0.0015
-0.0027
0.0209
0.1119
-0.0084
-0.0392

-0.0033
0.0443
-0.0354
0.1171
0.0457
0.0000
0.1642
0.0429
0.0207
0.1603
-0.0064
-0.1338

-0.0380
0.0096
-0.0701
0.0824
0.0110
-0.0347
0.1295
0.0082
-0.0140
0.1257
-0.0411
-0.1685

=D48-$F$35

Product
0.00259 <-- =E48*B48
-0.00061
-0.00175
0.00333
-0.00041
-0.00079
-0.00019
-0.00002
-0.00029
0.01406
0.00035
0.00660

Covariance

0.00191
0.00191
0.49589
0.49589

Correlation

<-- =AVERAGE(G48:G59)
<-- =COVAR(A48:A59,D48:D59)
<-- =G62/(F37*C37)
<-- =CORREL(A48:A59,D48:D59)

The Efficient Frontier

Portfolio mean return

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

3.20%
2.80%
2.40%
2.00%

1.60%
1.20%
0.80%
0.40%
0.00%
0.00%1.00%2.00%3.00%
4.00%
5.00%
6.00%
7.00%
Portfolio sigma

Graphing Returns of Stock A and Stock B

20%

15%

y = 0.833x + 0.0077
R = 0.2459

Stock B returns

10%

5%

0%
-6%

-4%

-2%

0%

2%

4%

6%

8%

10%
Stock A returns

-5%

-10%

-15%

12%

14%

16%

Page 135-136

A
B
C
1 CALCULATING THE MEAN AND
2
3 Proportion of A
0.5
4
5
Month
R At
6
1
-3.58%
7
2
-3.16%
8
3
5.74%
9
4
7.28%
10
5
-0.45%
11
6
5.51%
12
7
3.09%
13
8
2.97%
14
9
5.33%
15
10
14.43%
16
11
2.40%
17
12
-0.68%
18
19
20
21
22
23
Proportion
Sigma
Mean
24
5.60%
3.35%
25
0
8.03%
3.47%
26
0.075
7.62%
3.45%
27
0.15
7.21%
3.43%
28
0.225
6.82%
3.42%
29
0.3
6.46%
3.40%
30
0.375
6.11%
3.38%
31
0.45
5.80%
3.37%
32
0.525
5.51%
3.35%
33
0.6
5.26%
3.33%
34
0.675
5.06%
3.31%
35
0.75
4.90%
3.30%
36
0.825
4.80%
3.28%
37
0.9
4.75%
3.26%
38
0.975
4.77%
3.25%
39
1.05
4.84%
3.23%
40
1.125
4.96%
3.21%
41
1.2
5.14%
3.19%
42
1.275
5.36%
3.18%
43
1.35
5.62%
3.16%
44
1.425
5.92%
3.14%
45
1.5
6.25%
3.13%
46
1.575
6.60%
3.11%

SIGMA OF A PORTFOLIO

RBt
-0.33%
4.43%
-3.54%
11.71%
4.57%
0.00%
16.42%
4.29%
2.07%
16.03%
-0.64%
-13.38%
Mean
Variance
St. dev.

Rpt
-1.96%
0.63%
1.10%
9.50%
2.06%
2.75%
9.76%
3.63%
3.70%
15.23%
0.88%
-7.03%
3.35%
0.31%
5.60%

<-- =C6*$B$3+(1-$B$3)*D6

<-- =AVERAGE(E6:E17)
<-- =VARP(E6:E17)
<-- =STDEVP(E6:E17)

<-- Table headers: =SQRT(E19) and =E18 respectively

For the technique of building data tables,
see Chapter 26.

Page 3

Page 137

The Efficient Frontier

3.50%

3.45%

3.40%

Portfolio mean return

3.35%

3.30%

3.25%

3.20%

3.15%

3.10%

3.05%

3.00%
3.50%

4.00%

4.50%

5.00%

5.50%

6.00%

6.50%

Portfolio sigma

Page 4

7.00%

7.50%

8.00%

8.50%

UN-5G

A FOUR-ASSET PORTFOLIO PROBLEM

Variance-covariance
0.10
0.01
0.01
0.30
0.03
0.06
0.05
-0.04
Portfolio 1
Portfolio 2

0.2
0.2

Portfolio 1
Mean
Variance

9.10%
12.16%

Covariance
Correlation

0.0714
0.4540

Transposes
Portfolio 1
0.2
0.3
0.4
0.1

0.03
0.06
0.40
0.02

0.05
-0.04
0.02
0.50

0.3
0.1

0.4
0.1

Mean returns
6%
8%
10%
15%
0.1
0.6

Portfolio 2
Mean
12.00% <-- =MMULT(C10:F10,$G$4:$G$7)
Variance
20.34% <-- =MMULT(C10:F10,MMULT(B4:E7,D21:D24))
<-- =MMULT(C9:F9,MMULT(B4:E7,D21:D24))
<-- =C16/SQRT(C14*F14)

Portfolio 2
0.2
0.1
0.1
0.6

Calculating returns of combinations of Portfolio 1 and Portfolio 2

Proportion of Portfolio 1
Mean return
Variance of return
Stand. dev. of return

0.3
11.13% <-- =B27*C13+(1-B27)*F13
14.06% <-- =B27^2*C14+(1-B27)^2*F14+2*B27*(1-B27)*C16
37.50% <-- =SQRT(B29)

Table of returns (uses this example and Data|Table)

Proportion Stand. dev.
37.50%
0
45.10%
0.1
42.29%
0.2
39.74%
0.3
37.50%
0.4
35.63%
0.5
34.20%
0.6
33.26%
0.7
32.84%
0.8
32.99%
0.9
33.67%
1
34.87%
1.1
36.53%
1.2
38.60%

Mean
11.13% <--the content of these cells is given below:
12.00%
<-- =B30
11.71%
<-- =B28
11.42%
11.13%
Four-Asset Portfolio Returns
10.84%
10.55%
12.5%
10.26%
12.0%
11.5%
9.97%
11.0%
9.68%
10.5%
9.39%
10.0%
9.5%
9.10%
9.0%
8.81%
8.5%
8.52%
8.0%

Mean return

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

30.0%

35.0%

40.0%

45.0%

Standard deviation

50.0%

A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

A FOUR-ASSET PORTFOLIO PROBLEM

Variance-covariance
0.10
0.01
0.03
0.05
Portfolio 1
Portfolio 2

0.01
0.30
0.06
-0.04

0.03
0.06
0.40
0.02

0.05
-0.04
0.02
0.50

0.2
0.2

0.3
0.1

0.4
0.1

Portfolio 1
Mean
Variance

9.10%
12.16%

Covariance
Correlation

0.0714
0.4540

Transposes
Portfolio 1
0.2
0.3
0.4
0.1

Mean returns
6%
8%
10%
15%
0.1
0.6

Portfolio 2
Mean
12.00% <-- =MMULT(C10:F10,$G$4:$G$7)
Variance
20.34% <-- =MMULT(C10:F10,MMULT(B4:
<-- =MMULT(C9:F9,MMULT(B4:E7,D21:D24))
<-- =C16/SQRT(C14*F14)

Portfolio 2
0.2
0.1
0.1
0.6

Calculating returns of combinations of Portfolio 1 and Portfolio 2

Proportion of Portfolio 1
Mean return
Variance of return
Stand. dev. of return

0.3
11.13% <-- =B27*C13+(1-B27)*F13
14.06% <-- =B27^2*C14+(1-B27)^2*F14+2*B27*(1-B27)*C16
37.50% <-- =SQRT(B29)

Table of returns (uses this example and Data|Table)

Proportion
-0.8
-0.65
-0.5
-0.35
-0.2
-0.05
0.1
0.25
0.4
0.55
0.7
0.85
1
1.15

Stand. dev.
37.50%
72.88%
67.23%
61.72%
56.40%
51.33%
46.59%
42.29%
38.57%
35.63%
33.66%
32.84%
33.26%
34.87%
37.52%

Mean
11.13%
14.32%
13.89%
13.45%
13.02%
12.58%
12.15%
11.71%
11.28%
10.84%
10.41%
9.97%
9.54%
9.10%
8.67%

16%
15%
14%
13%
12%
11%
10%
9%
8%
7%
6%

stock A

A
51
52
53
54
55
56
57
58

B
1.3
1.45
1.6
1.75
stock A
stock B
stock C
stock D

C
41.00%
45.13%
49.74%
54.72%
31.62%
54.77%
63.25%
70.71%

D
8.23%
7.80%
7.36%
6.93%

F
6%
20%

6%
8%
10%
15%

G
30%

40%

PROBLEM1
2
3 St. dev.
4
31.62%
5
54.77%
6
63.25%
7
70.71%
8
9
10
11
12
<-- =MMULT(C10:F10,$G$4:$G$7)
13
<-- =MMULT(C10:F10,MMULT(B4:E7,D21:D24))
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
stock D
38
39
40
41
42
43
stock C
44
45
46
stock B
47
48
49
50

H
51
40%
52
53
54
55
56
57
58

I
50%

J
60%

70%

K
80%

A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

ADJUSTING RETURNS FOR DIVIDENDS

General Motors (GM)
Year-end Dividend
Discretely
Continously
stock
per
compounded compounded
Year
price
share
return
return
1986
33.00
1987
30.69
2.50
0.568%
0.567%
1988
41.75
2.50
44.196%
36.600%
1989
42.25
3.00
8.383%
8.050%
1990
34.38
3.00
-11.538%
-12.260%
1991
28.88
1.60
-11.345%
-12.042%
1992
32.25
1.40
16.537%
15.304%
1993
54.88
0.80
72.636%
54.601%
1994
42.13
0.80
-21.777%
-24.560%
1995
52.88
1.10
28.131%
24.788%
1996
55.75
1.60
8.463%
8.124%
Arithmetic annual return
Standard deviation of returns

13.425%
27.148%

=LN((D18+C18)/C17)

9.92% <-- =AVERAGE(F9:F18)

22.838% <-- =STDEVP(F9:F18)

=(C18+D18)/C17-1

The column "effective shares held" assumes that dividends are reinvested
in shares at the end of year price. Thus, in 1987, the dividend of $2.50
is invested in shares costing 30.69, so that the holder of 1 share at the
end of 1986 could buy another 2.50/30.69 =
0.08146
shares. At the end of 1987, the owner of 1.08 shares at the end
of 1986 would have received a dividend of 1.08*2.50 =
2.7
This dividend would be reinvested in shares, 2.7/41.75 =
0.064671
so that the owner of 1.08 shares at the end of 1986 would now own
1.144671 shares.
The "compound geometric return" is defined as:
[Value of ending investment/Value of beginning investment]^(1/#years) -1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
=LN((D18+C18)/C17)
19
AVERAGE(F9:F18)
20
STDEVP(F9:F18)
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
vestment]^(1/#years)
39
-1

REINVESTED DIVIDENDS
Effective
Share
Dividend
Total
Number Value of
shares
price
per share dividends of shares shares
held at
at end
received
at end of end of
Year
beg. year
year
year
year
1986
33.00
33.000
1987
1.00
30.69
2.500
2.500
1.081
33.188
1988
1.08
41.75
2.500
2.704
1.146
47.855
1989
1.15
42.25
3.000
3.439
1.228
51.867
1990
1.23
34.38
3.000
3.683
1.335
45.882
1991
1.33
28.88
1.600
2.136
1.409
40.677
1992
1.41
32.25
1.400
1.972
1.470
47.403
1993
1.47
54.88
0.800
1.176
1.491
81.835
1994
1.49
42.13
0.800
1.193
1.520
64.014
1995
1.52
52.88
1.100
1.672
1.551
82.021
1996
1.55
55.75
1.600
2.482
1.596
88.963
Annualized continous return
Compound geometric return
=K10*M10

=K10+N10/L10
=O10*L10

9.92% <-- =LN(P18/P8)/10

10.43% <-- =(P18/P8)^(1/10)-1

R
1
2
3
4
5
6
7
=K10+N10/L10
8
9
10
=O10*L10
11
12
13
14
15
16
17
18
19
<-- =LN(P18/P8)/10
20
<-- =(P18/P8)^(1/10)-1
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

Page 149

A
B
C
D
1 Comparing continuous returns to discrete returns
2
3
Stock A
4
Continuous discrete
5
Month
Price
return
return
6
0
25.00
7
1
24.12
-3.58%
-3.52%
8
2
23.37
-3.16%
-3.11%
9
3
24.75
5.74%
5.91%
10
4
26.62
7.28%
7.56%
11
5
26.50
-0.45%
-0.45%
12
6
28.00
5.51%
5.66%
13
7
28.88
3.09%
3.14%
14
8
29.75
2.97%
3.01%
15
9
31.38
5.33%
5.48%
16
10
36.25
14.43%
15.52%
17
11
37.13
2.40%
2.43%
18
12
36.88
-0.68%
-0.67%
19
20
Mean
3.24%
3.41%
21
Sigma
4.78%
5.02%
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

Page 13

Stock B
Continuous Discrete
price
return
return
45.00
44.85
-0.33%
-0.33%
46.88
4.43%
4.53%
45.25
-3.54%
-3.48%
50.87
11.71%
12.42%
53.25
4.57%
4.68%
53.25
0.00%
0.00%
62.75
16.42%
17.84%
65.50
4.29%
4.38%
66.87
2.07%
2.09%
78.50
16.03%
17.39%
78.00
-0.64%
-0.64%
68.23 -13.38%
-12.53%
3.47%
8.03%

3.86%
8.32%

Page 149

52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

The Efficient Frontier

0.032
0.028
0.024
0.02
0.016
0.012
0.008
0.004
0
0

Page 14

0.5

Page 149

I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 <-- =AVERAGE(H7:H18)
21 <-- =STDEVP(H7:H18)
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

Page 15

Page 149

I
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

1.5

Page 16

Page 150

A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

MONTHLY AND ANNUAL MEANS AND VARIANCES

Month
0
1
2
3
4
5
6
7
8
9
10
11
12

Stock A
Continuous
Price
return
25.00
24.12
-3.58%
23.37
-3.16%
24.75
5.74%
26.62
7.28%
26.50
-0.45%
28.00
5.51%
28.88
3.09%
29.75
2.97%
31.38
5.33%
36.25
14.43%
37.13
2.40%
36.88
-0.68%

Monthly mean
Monthly variance
Monthly stand. dev.

3.24%
0.23%
4.78%

Annual mean
Annual variance
Annual stand. dev.

38.88%
2.75%
16.57%

Stock B
Continuous
Price
return
45.00
44.85
-0.33%
46.88
4.43%
45.25
-3.54%
50.87
11.71%
53.25
4.57%
53.25
0.00%
62.75
16.42%
65.50
4.29%
66.87
2.07%
78.50
16.03%
78.00
-0.64%
68.23
-13.38%
3.47% <-- =AVERAGE(F7:F18)
0.65% <-- =VARP(F7:F18)
8.03% <-- =STDEVP(F7:F18)
41.62% <-- =12*F20
7.75% <-- =12*F21
27.83% <-- =SQRT(12)*F22

Page 17

Page 150

A
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

The Efficient Frontier

0.032
0.028
0.024
0.02
0.016
0.012
0.008
0.004
0
0

Page 18