CFIN6
Spreadsheet Problem Solution
Chapter 8
1. a. – d.
INPUT DATA:
KEY OUTPUT:
Year
Stock A
Stock B
Stock C
Portfolio
-1
–18.00%
–14.50%
–16.25%
-2
33.00%
21.80%
27.40%
-3
15.00%
30.50%
22.75%
-4
–0.50%
–7.60%
–4.05%
-5
27.00%
26.30%
26.65%
11.30%
11.30%
11.30%
20.79%
20.78%
20.13%
1.84
1.84
1.78
Percent in:
Stock A
Stock B
Stock C
Portfolio
50.00%
50.00%
0.00%
100.00%
Based on the coefficients of variation, the portfolio that includes Stock A and Stock B has the best
(lowest) risk-return relationship.
e. – f.
INPUT DATA:
KEY OUTPUT:
Year
Stock A
Stock B
Stock C
Portfolio
-1
–18.00%
–14.50%
32.00%
–0.16%
-2
33.00%
21.80%
–11.75%
14.35%
-3
15.00%
30.50%
10.75%
18.75%
-4
–0.50%
–7.60%
32.25%
8.05%
-5
27.00%
26.30%
–6.75%
15.51%
11.30%
11.30%
11.30%
11.30%
20.79%
20.78%
20.76%
7.49%
1.84
1.84
1.84
0.66
Percent in:
Stock A
Stock B
Stock C
Portfolio
33.33%
33.33%
33.34%
100.00%
Correlation coefficients, ρ
Stocks A & B
0.88
Stocks A & C
Stocks B & C
CFIN6
Because the expected return for each stock is the same, the return on the three- stock portfolio is the
same as it was when only Stock A and Stock B were included in the portfolio. But, the standard
g.
INPUT DATA:
KEY OUTPUT:
Year
Stock A
Stock B
Stock C
Portfolio
-1
–18.00%
–14.50%
32.00%
7.88%
-2
33.00%
21.80%
–11.75%
7.83%
-3
15.00%
30.50%
10.75%
16.75%
-4
32.25%
-5
27.00%
26.30%
9.95%
11.30%
11.30%
11.30%
11.30%
20.79%
20.78%
20.77%
3.97%
Percent in:
Stock A
Stock B
Stock C
25.00%
25.00%
50.00%
Stocks A & B
Stocks A & C
Stocks B & C
Because Stock C is negatively related to both Stock A and Stock B, the risk of the three-stock portfolio
will decrease to a certain point as more funds are used to buy Stock C. However, purchasing too much
of Stock C will increase the riskiness of the portfolio. For example, if 15 percent is invested in Stock A,
25 percent is invested in Stock B, and 60 percent is invested in Stock C, the portfolio’s standard
deviation increases to 6.13 percent.
j. Clearly, it is better to hold Stock C in the portfolio rather than holding only Stocks A and B, because
including Stock C helps reduce risk. If other investors discover the same relationship, buying pressure