### Combined Assignment of CAPM and Fama and French

1. Calculating Required Rate of Return (Expected Return) with CAPM ;

2. Calculating Required Rate of Return (Expected Return) with Fama And French model

3. Comparing FF model with CAPM

Marks = 8

Due Date = February 20, 2012

Note:

A.  The following assignment must be done in the already-formed groups

B.  The due date is  Feburuary 20, 2012 . Try to submit the assignment on due date otherwise 0.5 mark will be deducted per day.

C.  Each group has been assigned a unique set of companies, called universe, for this assignment. You are required to use
data only of the specific universe for the specific year.

D.  Submit the assignment to this email: ims.assign@gmail.com

E.  Assignments similar in contents, format, and explanation, will not get any mark

Student Groups, Assigned companies [MBA 3r Group A +B], and Universes are here.
Student Groups, Assigned companies [MBA 3r Group D+E], and Universes are here.

DATA:
Index point and T-bill rates are here
MS Excel files from d2000.xls to d2008.xls at the bottom of  contain key financial data of all KSE listed firms from the year 2000 to 2008.

Requirements:

1. Calculating Required Rate of Return (Expected Return) with CAPM
2. Calculate required rate of return (RRR) on the stock of your assigned companies by using Fama and French (FF) model
3. Compare CAPM with FF model and decide which model can better predict the required rate of return on your selected security. In other words, based on your analysis tell which model is more realistic.

Steps to Follow:

A. Calculating Expected return with Fama and French

To calculate expected return with Fama and French Model, we need to calculate betas and values of three independent variables through the following equation:

To operationalize the above equation, follow these steps:

A.      Obtain end of month share prices of the two companies assigned to your group for a period of 12 months for your assigned year
B.      Calculate Ri = (P1-Po)/Po, for both companies individually
C.      Take interest-free rate in your assigned year and divided that rate by 12 to make it a monthly rate
D.      Deduct the monthly interest free rate (Rf) from the Ri and you will obtain Ri-Rf

A.      Obtain end of month index points for KSE 100 index for a period of 12 months for your assigned year
B.      Calculate Rm = (Index1-Indexo)/Indexo
C.      Take interest-free rate (T-bill rates) of your assigned year and divide that rate by 12 to make it a monthly rate
D.      Deduct the monthly interest free rates (Rf) from the Rm values and you will obtain Rm-Rf

A.      Obtain total assets figure of your UNIVERSE companies for you assigned year
B.      write the total assets figures in column next to the companies names
C.      Sort the UNIVERSE companies from large to small by using the A    Z function on the assets figures
D.      Select the top 5 companies and name this group of 5 companies as BIG
E.       Select the bottom 5 companies and name this group as SMALL companies
F.       Obtain monthly (end-of-month) closing share prices for 12 months for the BIG companies
G.     Calculate monthly returns for each company in the BIG group
H.      Calculate average return for the BIG in each month by averaging the returns of the 5 companies
I.        Repeat the same process for SMALL group to calculate average monthly return
J.        Subtract the average returns of BIG from the average returns of SMALL in each of the 12 months. These monthly differences are your SMB

1.       Calculate Book value per share for your UNIVERSE companies for your assigned year
2.       Book per share = Total Equity / No. Shares outstanding
3.       Obtain Market value per share for you Universe Companies in the given year
4.       Market value per share = Share price at the end of the given year
5.       Calculate B/M ratio = Book value / Share price
6.       Place the B/M ratio values in columns next to the UNIVERSE companies
7.       Sort the UNIVERSE companies from HIGH to LOW B/M ratio by using the A   Z function of MS-Excel on the B/M ratio
8.       Select the top 5 companies and name this group of 5 companies as HIGH
9.       Select the bottom 5 companies and name this group as LOW companies
10.   Obtain monthly (end-of-month) closing share prices for 12 months for the HIGH companies
11.   Calculate monthly returns for each company in the HIGH group
12.   Calculate average return for the HIGH in each month by averaging the returns of the 5 companies
13.   Repeat the same process for LOW group to calculate average monthly return
14.   Subtract the average returns of LOW from the average returns of HIGH in each of the 12 months. These monthly differences are your HML

1.       Go to Tools Menu and check whether Data Analysis is installed, if not, follow instructions at the bottom of this page for installation
2.       Copy the (Ri-Rf),( Rm-Rf), SMB, and HML values to a 4 columns adjacent to each other
3.       Remember to give the above headings at the top of relevant column
4.       If you have MS-Excel 2003, go to Tools Menu > Data Analysis > Regression > Input Y Range > Select Ri-Rf column, (including the first cell wherein you have given the heading of Ri-Rf > Come back to regression window [  If you CANNOT SEE the Data Analysis in tools, then perhaps it is not installed yet, for installation, go to Tools menu>>Add ins>>select Data Analysis Toolpack>> the installation will ask for Office CD, so insert Office CD in CD room]
5.       Click on Input X Range > Select all the three independent variables with their headings i.e. Rm-Rf, SMB and HML by dragging the mouse on all the three variables from start to bottom of data range> come back to regression window
6.       Click the “Labels” checkbox  and leave other options as without touching them
7.       Click Ok and you find regression output in a new sheet

Suppose we have the following output from the regression analysis

 SUMMARY OUTPUT Regression Statistics Multiple R 0.858827001 R Square 0.737583818 Adjusted R Square 0.63917775 Standard Error 0.060728886 Observations 12 ANOVA df SS MS F Significance F Regression 3 0.082928037 0.027643 7.495308 0.010369059 Residual 8 0.029503981 0.003688 Total 11 0.112432018 Coefficients Standard Error t Stat P-value Lower 95% Intercept 0.035 0.0202 1.741549 0.119762 -0.01143538 SMB 0.012 0.0311 3.30466 0.010784 -0.174728046 HML 0.084 0.0364 2.327802 0.048327 0.000795263 (Rm-Rf) 0.494 0.31861 1.5516 0.159358 -1.22909265

The above output shows that SMB has beta of .012, HML has beta of .084 and RM-Rf has beta of .494.

As you know the equation used for calculating expected return in in Fama and French is as under:

(Rm-Rf)* This is the annual risk premium on market portfolio. Simply add up all the monthly Rm-Rf value and you will get the annual Rm-Rf

(SMB)** This is the annual size premium which you can obtain by adding up all the SMB values in all of the 12 months

(HML)*** This is B/M ratio premium and cab be obtained by adding up the HML values of all of the twelve months

Once you calculate the expected return, compare it with actual return, if actual return is greater than expected return, then security is under-valued or vice versa.

B. Calculating Expected return with CAPM

The Beta formula under CAPM  is given by:

As you have already calculated the (Ri-Rf) and (Rm-Rf) variable in Fama and French model, simply apply regression to only

these two variables in the following way:

A. (Ri-Rf) is your dependent variable and (Rm-Rf) is your independent variable
B.  Use regression technique to find the coefficient of (Rm-Rf), this coefficient is your beta
C. After finding beta, use the CAPM equation to find out the required rate of return on your stock
D. Compare this required rate of return with actual rate of return of the stock to find out whether the stock is overvalued, undervalued or fairly valued

Ĉ
Attaullah Shah,
Jan 10, 2010, 7:09 AM
Ĉ
Attaullah Shah,
May 22, 2010, 6:32 AM
ċ
Groups.xls
(48k)
attaullah shah,
May 14, 2010, 6:12 AM
Ĉ
Attaullah Shah,
Apr 13, 2010, 12:17 AM