# Financial Modeling BMME036

Financial Modeling BMME036 Assignment 3
Instructions
Please prepare this assignment individually. Every student prepares one pdf file that includes
relevant Excel computations. The aim of this assignment is to test your understanding of conducting
finance research and using excel to perform data analysis to evaluate facts and figures, and test
hypotheses. Cooperation between students is not allowed and will be considered as fraudulent
behavior that will be reported to the Examination Board.
Question 1
For the first question please use the excel file “Assigment by Huij.xls” containing the time series of
monthly excess returns of 240 funds that have a complete return history over the period 1986-1999.
and value HML) from the data library of the webpage of Kenneth French:
http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html for the relevant time
period.
We are now going to test if funds that follow value/momentum styles earn outperformance over the
market. During the lecture the graph below was presented. You are asked to generate a similar
In the first step you are asked to create decile portfolios (= ten portfolios) of funds based on their 36-
month rolling correlations with the HML. To compute fund correlations, please follow the approach
described during the lecture and make a tab “HML correl” and compute the funds’ 36-month rolling
HML correlations. Show a screenshot of the tab “HML correl”.
In the second step, create a similar tab with the funds’ 12-month momentum. Show a screenshot of
this tab.
Now, create a new tab “deciles”. This tab is slightly different from the “rank” tab we discussed
during class. Now instead of the funds’ ranks on betas, compute their deciles based on
value/momentum. To this end, you may use a combination of the Excel functions ROUNDUP and
RANK. Show a screenshot of the tab “deciles”.
Finally, create a tab “portfolios” and compute the decile returns. Show a screenshot of the tab
“portfolios”. For the decile portfolios, compute their betas and returns and generate a plot as listed
above. Comment on the hypothesis that funds following value/momentum styles are fully described
by the CAPM.
Question 2
For this question you need to create a sector volatility strategy using the excel file “Financial
Modeling Case Study — Solved.xlsx”. For that purpose:
Download monthly returns of 12 industry portfolios from Kenneth French Data Library
Download Fama/French 3 Factors (FF3) factor model and Fama/French 5 Factors (FF5) model
http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html
Create the following investment strategy:
Strategy: Past 12 month volatility
Each month:
– Calculate the beta to the market portfolio of each sector over the past 12 months
– Rank all sectors based on this measure
– Split stocks in 4 portfolios (3 sectors in each portfolio)
– Calculate the performance of the Top (low beta) portfolio over the next month
Go to the following website:
https://www.msci.com/end-of-day-data-search
MSCI USA index
MSCI USA Minimum Volatility (USD) index
MSCI USA Enhanced Value index
MSCI USA Quality index
MSCI USA Momentum index
*All in USD Gross monthly returns
Conduct Return Based Style Analysis (RBSA) of your Top (Low beta) portfolio to the 5 MSCI indices
over the sample period January 2000 – December 2018 (Use long-only total returns for indices and