# Financial Modelling Coursework Solutions

v:* {behavior:url(#default#VML);}
o:* {behavior:url(#default#VML);}
w:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}

Normal
0

false
false
false

EN-GB
ZH-CN
AR-SA

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:Calibri;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}

Financial Modelling

Answer all following questions both coursework 1 and 2

Coursework c1

Q1. Bond price, yield and duration exercises

Go to the London Stock Exchange’s bonds’ website

http://www.londonstockexchange.com/exchange/prices-and-markets/retail-bonds/retail-bonds-search.html

Select Corporates then click Search

Choose 3-4 bonds. Fixed coupon rate bonds only.

Required:

(a)  Verify that the price is right with the given yield and coupon.

(b)  Calculate their durations.

(c)  Using your cases to support the statements that:

Bonds with the coupon rate < YTM will be priced at a discount;

Bonds with the coupon rate > YTM will be priced at a premium;

Bonds with the coupon rate = YTM will be priced at the face value.

(d)  Verify that:

Other things being equal, bonds with a higher coupon rate have a shorter duration;

Other things being equal, bonds with a higher YTM have a shorter duration;

Other things being equal, bonds with a longer time to maturity have a longer duration.

(35 marks)

Note:  if you have difficulties in finding a pair of two bonds that are equal in maturity and YTM or coupon rate, it is acceptable that one of the bonds is real while the other is hypothetical.

Q2. You are required to construct stock portfolios with returns on two stocks or two stock market indices. You can download stock return data from yahoo finance or other sources. The data can be of any frequencies and from any markets. Additionally, you need to find out a risk-free interest rate appropriate for the market you have chosen.

Required:

(a)  Estimate the expected returns on the two stocks or stock indices.

(b)  Estimate the variances and standard deviations of the two returns.

(c)  What is the covariance between the two returns?

(d)  What is the correlation coefficient between the two returns?

(e)  Plot the efficient frontier of the portfolios constructed with these two stocks or stock indices.

(f)    Find out the minimum variance portfolio constructed with these two stocks or stock indices, solving for the weights of the two stocks or stock indices in the portfolio.

(g)  What are the expected return and risk of this minimum variance portfolio?

(h)  Construct an optimal portfolio with these two stocks or stock indices and solve for the weights of the two stocks in the portfolio.

(i)    What are the expected return and risk of the optimal portfolio?

(40 marks)

Q3. VaR exercises

Download returns on two or more stocks from yahoo finance or other sources. The data can be from any markets.

Required:

(a)  Estimate their VaR with different criteria and different investment horizons.

(b)  Using your cases to verify that:

Higher VaR losses with higher volatilities, other things being equal;

Higher VaR losses with tighter criteria, other things being equal.

(25 marks)

Requirements:

Provide all the data and workings with excel. Write in word a narrative for each of the questions that summarises your results with pertinent analysis. The guideline for the length of a narrative is 200 words ± 25%. Printing/reporting numbers only without analysis, interpretation and explanation will earn no marks.

Submit both excel and word documents on Moodle.

Coursework c2

Section A

A1. Outline the theoretical assumptions and implications of the Capital Asset Pricing Model (CAPM).

(10 marks)

A2. How valid are the assumptions, to what extent does a breach of the assumptions invalidate the CAPM model?

(5 marks)

A3. Carry out an empirical test of the CAPM using the excel file ‘Fama French’ on Moodle, downloaded from Ken French’s website, as follows:

Using the excess return model: RptRft= α+ β*RmtRft+ εt

Test: H0 :α=0

(20 marks)

Monthly stock returns for 10 industry portfolios for 30 years to the end of 2009 are included in the Fama French file. You need to separately test only 2 industries (look on Moodle to see which industries you have been given).

1.    Copy the regression output into the appendices.

2.    Prepare tables that summarise the regression output and put these tables in the body of your report, assess if these results agree with the CAPM.

A4. Briefly outline the background and important features of the Fama French three factor model.

(5 Marks)

A5. Carry out an empirical test of the Fama French three factor model using the data file ‘Fama French’ on Moodle, as follows:

(20 marks)

Using the excess return model:

RptRft= α+ βpt*RmtRft+spt*SMBt+hpt*HMLt+ εt

Test the following hypotheses:

H0 : α=0  , H0 : β=0  , H0 : s=0  , H0 :h=0

Monthly stock returns for 10 industry portfolios for 30 years to the end of 2009 are included in the Fama French file. You only need to test 2 industries (look on Moodle to see which industries you have been given).

You need to do two runs of this model, one for each of the industries you have been allocated.

1.   Copy the regression output into the appendices.

2.   Prepare tables that summarise the regression output and put these tables in the body of your report, assess if these results agree with the Fama French model.

3.   Compare the results from the Fama French model to the CAPM.

NOTES for Section A

Read each part carefully, make sure that your answer is focussed on and clearly answers the requirement of each section; marks will be mainly allocated to understanding of theory and interpretation of empirical results in the light of theory.

Use Arial 12 point font with 1.5 line spacing. Ensure the main results are put into tables in the main body of the report.

This section should have a maximum of 1,800 words; this does not include tables, appendices or references.

It is expected that you will have referred to a number of academic papers and other sources. These should be referenced using the Harvard system.

The section will be marked on the following criteria:

• All relevant parts of the section covered
• Logically organised
• Good presentation and easily understandable
• Correct use of statistical techniques using SPSS software
• Good analysis and interpretation of results
• Discussion of link between theory and empirical results
• Good use of academic references

Section B

One of the reasons students go to university is in order to earn higher wages in their future career. This section asks you to investigate the ‘return to education’, the causes of wage differentials in general and the important factors in designing a good financial model.

To answer this section use the excel file on Moodle called ‘Wages in the USA’; a sample of 534 real people giving details of their hourly wages and other variables.

B1. Prepare a table of ‘descriptive statistics’. This will include the minimum, maximum, mean and standard deviation of each of the variables.

(3 marks)

B2. Regression model 1 is:

Wagesi= α+
β1Educationi+ εi

Run regression model 1 and prepare a table of the most important results including coefficients, significance and model fit. Also prepare a scatter chart showing the line of best fit. How much on average do wages rise for each additional year of education?

(6 marks)

B3. Regression model 2 is:

Wagesi=
α+
β1Educationi+
β2Experiencei+
εi

Run regression model 2 and prepare a table of the most important results including the coefficients, significance and model fit. How much on average do wages rise for an additional year of education or experience in this model? Why are these results different to model 1?

(6 marks)

B4. Regression model 3 is:

Wagesi=
α+
β1Educationi+
β2Experiencei+β3Genderi+
εi

Run regression model 3 and prepare a table of the most important results including the coefficients, significance and model fit. Gender is a dummy variable; explain dummy variables and discuss the differences in wages between men and women using these results.

(6 marks)

B5. Prepare a correlation matrix of all of the variables. Use the matrix to discuss the importance of each variable for wages as well as the relationships between the variables.

(3 marks)

B6. Regression model 4 is:

Wagesi= α+
β1Educationi+ β2Experiencei+β3Genderi+ β4Agei+ β5Marriedi+β6Unioni+ εi

Run regression model 4 and prepare a table of the most important results including the coefficients, significance and model fit and discuss these results. Discuss possible problems with this model and suggest ways it could be improved.

(6 marks)

B7. Researchers design models to provide empirical results relating to questions in finance. Discuss the most important factors and problems that need to be considered when designing a financial model, (include the concept of parsimony/ Ockham’s razor).

(10 marks)

NOTES for section B

Use Arial 12 point font with 1.5 line spacing. Ensure the main results are put into tables in the main body of the report.

This section should have a maximum of 1,500 words; this does not include tables, and appendices.

Referencing to academic papers is not important in this section.

The section will be marked on the following criteria:

• All relevant parts of the section covered
• Logically organised
• Good presentation and easily understandable
• Correct use of statistical techniques using SPSS software
• Good analysis and interpretation of results

## FLAT 20% OFF

Coupon Code - STAY20
ORDER NOW
* Terms & Conditions Apply
psst...10% Off on your order today with the code NEW10.