Group: Leave all answers to 2-decimal places

Question # 00092748 Posted By: solutionshere Updated on: 08/15/2015 12:56 PM Due on: 09/14/2015
Subject Finance Topic Finance Tutorials:
Question
Dot Image
Name:
Group:

Leave all answers to 2-decimal places.
Show work: you answer cells should have
the embedded algebra or Excel function.

The following data applies to all questions in this assignment:
Rate of Return
Year
Asset A Asset B
1
20.00%
19.00%
2
-10.00%
15.00%
3
10.00%
-5.00%
4
-8.00% -13.00%
5
15.00%
25.00%
2015 Spring2

Market
10.00%
12.00%
8.00%
-5.00%
10.00%

1) Calculate the expected returns for Asset A, Asset B and the Market (use AVERAGE
function in Excel).
Asset A =
5.40%
Asset B =
8.20%
Market =
7.00%

2) Calculate the standard deviation of returns, ϭ, for each asset (use STDEVP function).
Asset A = 12.19%
Asset B = 14.62%
Market =
6.13%

3) Calculate the coefficient of correlation, ρ, between Asset A and Asset B (use CORREL function).
ρA,B =
0.49

4) Using the following asset weight combinations between Asset A and B [0%-100%, 25%-75%, etc.]
calculate the expected returns and their corresponding portfolio standard deviation of the portfolio
with these weight combos.
Use the following formula to calculate the portfolio standard deviation:

σ P=( w A σ A )2 +( wB σ B )2 +2 w A w B σ A σ B ρ A , B
= [(wA*σA)^2 + (wB*σB)^2 + (2 *wA * wB *σA * σB *ρ(A,B))] ^0.5
where

wA and wB
σA and σB
ρ(A,B)

%Asset A
0%
25%
50%
75%
100%

are the % of assets in Asset A and B respectively [i.e. A-B weight combo],
are the respective standard deviations of return [calculated from (2)] and
is the [coefficient of] correlation of returns between asset A and B
[calculated from (3)].
Portfolio
Portfolio
%Asset B Expected Return
Standard Deviation
100%
75%
50%
25%
0%

5) Using Portfolio Expected Returns on the Y axis and Portfolio Standard Deviation in the X
axis, draw the efficient frontier for possible portfolio combinations of Asset A and B [from
previous question]. (include 100% A and 100% B as two possibilities).

P o r t f o lio E x p e c t e d R e t u r n

Hint: Use the Excel Chart Wizard and select the XY (scatter) plot option.
Yes, I am helping you auto-plot the Efficient Frontier below once you calculate the Return-Stdev in Question4.
When you calculate and fill in the portfolio's Expected Returns and Standard Deviations in (4), it will
automatically plot below. It's probably a good idea to learn how to do it yourself, but for now it will save you
some time

0.09

Efficient Frontier
0.08

0.07

0.06

0.05
0.1

0.11

0.12
0.13
Port folio St andard De viat ion

0.14

0.15

6) Calculate Beta for Asset A (relative to the Market) and Asset B (relative to the Market). (use the
SLOPE function).
[PS: this function is also available on your TI BA II Plus calculator, and Excel facility may or may not be available (it is semesterand instructor-specific) during the Exam].

Beta Asset A =
Beta Asset B =

7) Assume that for next year the Risk Free Rate is expected to be 1% and that the overall
Market will realize a return of 6%. Using the CAPM / SML methodology, calculate the
required returns for Asset A and Asset B.
CAPM: ki = kRF + βi (kM - kRF)
kRF =

<risk-free rate>

kM =

<Market return>

Required Return for Asset A =
Required Return for Asset B =

Dot Image
Tutorials for this Question
  1. Tutorial # 00087153 Posted By: solutionshere Posted on: 08/15/2015 12:56 PM
    Puchased By: 2
    Tutorial Preview
    Standard Deviations in (4), it willautomatically plot below. It's probably a ...
    Attachments
    soln-new.zip (216.76 KB)

Great! We have found the solution of this question!

Whatsapp Lisa