Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Complete the analytical portion of the case assignment in the excel template which can be found in Canvas. Formulas must reference parameters in other cells
Complete the analytical portion of the case assignment in the excel template which can be
found in Canvas. Formulas must reference parameters in other cells using absolute or
relative cell references. DO NOT HARD CODE ANY NUMBERS IN YOUR EXCEL FORMULAS.
In the space provided in the Excel spreadsheet, plot the opportunity set for Portfolios A &
B the CAL & the indifference curve. To do this you will need to calculate the missing
information in the table found in the Excel spreadsheet that accompanies the case using
weights of portfolio A & B in percentage point increments. This requires knowing how
to program formulas in Excel using absolute and relative cell references from the data
provided. The table below already exists in the Excel file
Determine the optimal risky portfolio eg the optimal allocation of A & B using the
concepts from Modern Portfolio Theory. The approximate optimal allocation can be
determined using the table in Excel like the one shown above.
You do not need to plot the CAL rather you can manually draw the CAL on the Excel
graph using Insert Shapes window in Excel starting at the riskfree rate to the tangent
point.
Find the optimal complete portfolio graphically using the clients indifference curve.
Plot an indifference curve on the same graph you just created using the utility function
formula from Chapter To make things easier, you can use the same portfolio risk
numbers from the table above. However, you will first need to calculate the utility of
the optimal complete portfolio in the space provided in the Excel spreadsheet. The
risk aversion coefficient A has been provided. Plot the indifference curve AND the
opportunity set of risky assets on the same graph.
Determine the optimal complete portfolio. You did this graphically above, but you
need to calculate the precise composition of the optimal risky portfolio recall ymaxU
and TBills ymaxU
In the space provided in the Excel spreadsheet, plot the CAPM regressions of Portfolio
A and Portfolio B separate graphs The market portfolio is represented by the S&P
and the riskfree rate is represented by day TBill. Calculate the following for
each portfolio using the following methods in the spaces provided in the spreadsheet:
a Beta: using the Excel slope function
b Alpha: using the Excel yintercept function. The return data is monthly, so the alpha
via yintercept function of the CAPM regression represents a MONTHLY alpha.
c Annualized alpha: using the CAPM formula and inputting annualized returns of each
portfolio A & B S&P risk free rate TBills and the beta calculated in part a
above. Note that the annualized alphas of A & B are NOT the same as the monthly
alphas multiplied by The two may be close, but not exactly equal.
d Beta: using the beta formula. Recall the formula for beta which we derived and can
be found in the Module ppt lecture, Module & Notes, & the textbook. If you
did parts a and d correctly, the two beta calculations will be EXACTLY the same.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started