you have observed the following returns over time: Year Stock X Stock Y Market 2011 13 % 12 % 13 % 2012 18 7 10 2013 -12 -6 -10 2014 2 1 2 2015 19 9 16 Assume that the risk-free rate is 5% and the market risk premium is 6%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.
you have observed the following returns over time:
| Year | Stock X | Stock Y | Market | |||
| 2011 | 13 | % | 12 | % | 13 | % |
| 2012 | 18 | 7 | 10 | |||
| 2013 | -12 | -6 | -10 | |||
| 2014 | 2 | 1 | 2 | |||
| 2015 | 19 | 9 | 16 |
Assume that the risk-free rate is 5% and the market risk premium is 6%. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below.
-
What is the beta of Stock X? Do not round intermediate calculations. Round your answer to two decimal places.
fill in the blank 2
What is the beta of Stock Y? Do not round intermediate calculations. Round your answer to two decimal places.
fill in the blank 3
-
What is the required
rate of return on Stock X? Do not round intermediate calculations. Round your answer to one decimal place.fill in the blank 4 %
What is the required rate of return on Stock Y? Do not round intermediate calculations. Round your answer to one decimal place.
fill in the blank 5 %
-
What is the required rate of return on a portfolio consisting of 80% of Stock X and 20% of Stock Y? Do not round intermediate calculations. Round your answer to one decimal place.
fill in the blank 6 %
![File
1234567
5
B17
<>
Assignments - you can access...
template - Saved ✓
Home
Insert Draw
Historical Returns:
Year
2011
2012
2013
2014
2015
Arial
8
9
10
11 Risk-free rate, TRF
12 Market risk premium, RPM
13 % of Stock X in Portfolio
14 % of Stock Y in Portfolio
15
16
17 Beta, bi
Required rate of return, r¡
fx
Workbook Statistics
A
B
C
Historical Returns: Expected & Required Rates of Return
Sheet1 +
Page Layout
V 10
Cengage Digital Learning
Stock X
13.00%
18.00%
-12.00%
2.00%
19.00%
5.00%
6.00%
80%
20%
Stock X
Formulas Data
B
Stock Y
12.00%
7.00%
-6.00%
1.00%
9.00%
Stock Y
https://ng.cengage.com/activityservice/run/html5/handler.jsp?id=c1c04b1f4a487ae_912]
D
Market
13.00%
10.00%
-10.00%
18
19
20 Portfolio beta, bp
21 Required return on portfolio, rp
22
23 Alternate portfolio return calculation using individual stocks' required returns:
24 Required return on portfolio, rp
25
26
27
28
29
30
31
32
33
<
2.00%
16.00%
V
Review View
Search for tools, help, and more (Option + Q)
#N/A
#N/A
Mind Tap - Cengage Learning
#N/A
#N/A
#N/A
E
Help
ab
Formulas
#N/A
#N/A
Number
C Excel Online Student Work
F
G
v
←0
.00
H
.00
→.0
b Consider the following informat...
K
ST
NT
L
Σv ZPv
M
N
Start Page
Editing ✓
O
Give Feedback to Microsoft
+
Comments
T
P
100% +](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F7dc4ef70-ef30-445e-9bc3-fbd652f0f5db%2F5e344b4f-e280-4ed5-b20f-d4f6969b7476%2Fq6l31w_processed.png&w=3840&q=75)
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 2 images
