Question: Use Excel to conduct your analysis and plot figures. Copy and paste any generated tables or figures in a word document that will be your
Use Excel to conduct your analysis and plot figures. Copy and paste any generated tables or figures in a word document that will be your assignment submission paper. Do not submit data or printouts of data, however keep the excel files in your records.Please make your assignment handout organized, clear and provide any written explanation for the numbers submitted in an easy to follow format.Create document that reads like a report that was requested by your employer and needs to be understood by people that are not as familiar with finance theory as you are.
Question 1 (65 points)
The tab "Data_Q1" contains monthly close prices for Barrick Gold Corporation (GOLD) and Manulife Financial Corp (MFC) for the period November 2000 through October 2020. Assume the risk-free rate stays constant during the period of analysis and it is 1.2%.
a). Calculate monthly returns for both series of prices. Estimate the expected return, variance of GOLD and MFC and the covariance between the two return series.
We need to calculate the return first,
Return = (Current year price - Previous year price)/ Previous year price
Then we need to find return data same way with MFC,
For MFC = first return = (15.6875-12.9375)/12.9375
So, 21.26%
For example, second column would be -11.71% and third column -3.75%
Now we know return from share price, afterwards we find, (P1-P0)/P0
Where P1= Current share price and P0=Previous period share price
b). Create table that calculates expected returns and standard deviations for portfolios formed by combining the 2 stocks while changing weights in 10 percent increments (i.e. start with 100% in MFC and 0% in GOLD, then next line is 90% in MFC and 10% in GOLD, and so on, until 100% is invested in MFC and 0% in GOLD).Plot the opportunity set of risky portfolio formed by the two stocks, in excel (return, st.dev. graph), by inserting a graph in excel and selecting the right variables. Copy and paste the generated graph in the word document, as well as the table used to generate the graph.
c). What are the expected return and standard deviation of the optimal risky portfolio?
Assume you have only the 2 risky stocks to invest in and the given risk free rate.What is the Sharpe ratio? Enter the right formulas in excel and create table with the output and copy and paste it in your word assignment document.
d). Assume an investor's utility function is U = E(r) - ()A2 , where A = 2
Plot utility scores while changing the weight on the optimal risky portfolio in 10 percent increment (i.e. 100% in risk-free asset, 0% in risky portfolio, 90% in risk-free and 10% in risky portfolio, and so on). Insert the excel table in your assignment.
e). Find the optimal complete portfolio for the investor with A = 2. Create pie chart of the weights of the three assets (risk-free asset, GOLD and MFC), as resulting from your calculation, in investor's optimal complete portfolio. What is the weight in the risky portfolio and what are the expected return and standard deviation of the optimal complete portfolio?
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help