Year Market A B C D E 1 16 23 45 8 19 23 2 12 18 30 3 45 6 3 17 24 32 6 20 14 4
ID: 2786347 • Letter: Y
Question
Year
Market
A
B
C
D
E
1
16
23
45
8
19
23
2
12
18
30
3
45
6
3
17
24
32
6
20
14
4
14
17
38
12
8
6
5
8
12
4
14
11
11
6
-6
4
15
17
8
9
7
3
1
-15
16
-4
3
8
-7
0
-45
13
2
-1
9
-5
-2
5
7
6
9
10
8
18
27
2
11
7
Allocation
0.22
0.32
0.12
0.09
0.25
Rm
6%
US T-Bill
2.50%
US T-Bond
4.75%
Above are the 10-year returns on the stock market and the 10-year returns for five stocks that you are assembling in a portfolio based on the given allocation. Also given are the Market return (the average return for the 10-year period) and returns on the US T-Bill and US T-Bond.
Your assignment is to (1) determine the Beta for each stock and (2) the required return for investing in each stock. Then, (3) determine the Beta for the portfolio given the allocation. The requirements should be (4) presented in the discussion group (copy and paste your worksheet) and (5) attach the Excel file showing your calculations on a single worksheet.
Successful completion of this assignment requires use of the techniques illustrated in Chapter 8 of the text. You must read the chapter with an emphasis on pages 281 through 297. You must demonstrate your understanding of Beta Coefficient, Beta in a portfolio, systemic or non-diversifiable risk, and the Capital Asset Pricing Model (CAPM).
Note: Posting blank so as to “peek” at other students responses before posting your own response will result in a 50% reduction in the grade for this assignment. As always, you are permitted to revise your submission after reviews and discussions with other classmates.
This is a 200-point assignment. The assignment will not be extended under any circumstances.
Year
Market
A
B
C
D
E
1
16
23
45
8
19
23
2
12
18
30
3
45
6
3
17
24
32
6
20
14
4
14
17
38
12
8
6
5
8
12
4
14
11
11
6
-6
4
15
17
8
9
7
3
1
-15
16
-4
3
8
-7
0
-45
13
2
-1
9
-5
-2
5
7
6
9
10
8
18
27
2
11
7
Allocation
0.22
0.32
0.12
0.09
0.25
Rm
6%
US T-Bill
2.50%
US T-Bond
4.75%
Explanation / Answer
Hi,
As you are supposed to show it on an excel sheet, to start with, please copy the above table starting from cell A1.
Part 1 - calculation of beta
We will calculate the beta in cells E21 to I21
Please copy formula =COVARIANCE.P(E3:E12,$C$3:$C$12)/VAR.P($C$3:$C$12) in cell E21 and drag it till cell I21. This will give you the betas which will come out to be 1.00 for stock A, 2.26 for stock B, -0.27 for stock C, 0.79 for stock D and 0.39 for stock E.
Part 2 - Calculation of required return
As per capital asset pricing model, the required rate of return of a stock is calculated as,
Rf + Beta * (Rm - Rf) where, Rf = risk free rate of the bonds, Rm = Banchmark market return
We will calculate required return of the stocks in cells E22 to I22
Please copy formula =$C$19+E21*($C$17-$C$19) in cell E22 and drag upto cell I22. This will give you the returns which will come out to be 6.0% for stock A, 7.6% for stock B, 4.4% for stock C, 5.7% for stock D and 5.2% for stock E.
Part 3 - Calculation of beta of portfolio
Beta of the portfolio is simply the sum of the product of beta and their weights.
We will calculate this in cell E23. Simply put formula =SUMPRODUCT(E21:I21,E15:I15) . This will give you the portfolio beta, that is, 1.08.
Good Luck!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.