FINA5521 Quantitative Methods in Finance 2021
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FINA5521 Quantitative Methods in Finance
Deferred Final Exam, Semester 2, 2021
Instructions
This is a take-home open-book final exam, to be completed within 2 hours.
All your answers are to be included in the downloaded Excel file “5521 Template Name_StudentID.xlsx. Upon downloading the template, please
• Immediately update the file name with your name and student number;
• In the Excel sheet “Student Info and TOC”, enter your full name and student number;
• In the Excel sheet “Student Info and TOC”, enter the ticker symbol ofyour stock (please see Question 1).
You are reminded to save the Excel file frequently and backup the intermediate versions while working on the questions. Please include all Excel formulas wherever applicable. You are required to submit ONLY your Excel file on LMS by the conclusion of the exam. Late submission will incur penalties per School Policy.
This exam has two questions and is worth 55 marks in total. You are required to answer all questions in the Excel template renamed to include your name and Student ID.
Question 1 (27 marks): The Capital Asset Pricing Model (CAPM), although imperfect, is the cornerstone of modern finance. You are required to carry out the following tasks related to the CAPM. Please input the answers in Excel sheets 1.1-1.2.
1.1. (12 marks) Preliminary analysis of the monthly return data of the manufacturing sector over the sample period Jan 2016 to Dec 2020. Please use the Excel sheet “1.1 Downloaded returns” .
a. From Kenneth French’s data library https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html, locate “Industry Portfolios”, “5 Industry Portfolios”, and download the monthly returns for the manufacturing sector portfolio.
b. From the same website, download the monthly data of the Fama French three factors, including the riskfree rate.
c. Suppose there are two investors, each invested $1,000 in the manufacturing portfolio and the market portfolio respectively, at the beginning of 2016. Work out each investor’s balance at the end of Dec 2020. Plot the two time series of the monthly portfolio values in one time series plot.
d. For each investor, compute the annual simple net return for each year from 2016 to
2020. Work out the annual geometric mean return over the sample period.
1.2. (15 marks) Use Excel to estimate the CAPM regression for the manufacturing sector portfolio, and report the regression results in the Excel sheet “1.2 CAPM results”.
a. Comment on the R2 and adjusted R2 in no more than three sentences.
b. Comment on the intercept estimate and its statistical significance in no more than three
sentences.
c. Use the Excel formula to show the computation of the t-test statistic for testing H0 : β= 1 against H1 : β ≠ 1 . What is the conclusion based on the test result? Provide the financial implication in one sentence.
d. Enter the Excel formula to work out the p-value of the beta estimate using the corresponding t-statistic calculated from part c.
Question 2 (28 marks) Suppose you are going to invest in three assets (i) the manufacturing sector portfolio you downloaded for Question 1, called asset A; (ii) asset B with annual mean return of 8% and standard deviation of 15%; and (iii) the riskfree asset with a rate ofreturn of 2% p.a. Assume that the correlation between assets A and B is 0.3. Please use Excel Sheets 2.1-2.2 for the following tasks.
2.1. (18 marks) Approximate the tangency portfolio. Please use Excel Sheet “2.1 Tangency
pf”.
a. Use your results in task 1.1d to work out the arithmetic annual mean return and the annual standard deviation based on the 6 annual simple net returns of the manufacturing sector portfolio.
b. Denote the proportions invested in stocks A and B as pA and 1- pA, respectively. To prepare for the risk-return diagram, compute the risky portfolio returns and standard deviations for the 11 cases when pA = 0, 0.1, 0.2, …., 0.9, 1, respectively. Plot the risk return diagram and compute the Sharpe ratio for each for 11 cases.
c. Using trial-and-error, estimate the range of pA for the tangency portfolio based on the Sharpe ratio. Please specify up to two decimal places, e.g., pA is in the range of (0.22, 0.23).
d. Compute the mean and standard deviation the approximate tangency portfolio based on the results in c, using the mid-point of the estimated range, e.g., pA =0.225. Connect the riskfree asset and the approximate tangency portfolio using a straight line in the same graph ofthe risk-return diagram.
2.2 (10 marks) Assume that the utility ofyour investment strategy is given by U = µ(w) − 3σ2 (w) ,
where w is the proportion invested in the risky portfolio that consists of assets A and B, µ(w) is the expected return and σ2 (w) is the variance of the optimal portfolio. Use Excel Solver to work out the overall efficient portfolio with the maximum Sharpe ratio, and insert a screenshot ofyour Excel Solver pop-up window with the relevant inputs in Excel Sheet
“2.2 Excel Solver” .
2022-02-07