Assignment 3

Operationalizing CAPM model

Assignment No.3            

Calculation of Beta         

MARKS = 3         

DUE ON: November 24, 2008( MBA A,B,C )and November 19, 2008 (MBA D, and E Groups)

Note: The following assignment must be done in group of 2.

A.    The due date is November 24, 2008. Try to submit the assignment on due date otherwise 0.5 mark will be deducted per day.

B.     You are advised to comprehend each step thoroughly so that you answer any question at the time of submission. Those who fail to answer the questions will face penalty of 2 marks.

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

You are required to calculate beta for any security with two years monthly data. Then use CAPM model to calculate required of return on the security, compare that return with actual return of the security and decide whether the security should be purchased, sold or held. You can follow the following steps for doing this assignment:

1.       Select any one security and collect 2 years monthly data.

2.       Obtain KSE 100 points data for the corresponding  months

3.       Obtain risk free rates on T-bills for corresponding period

4.       Calculate monthly return from the security prices (Ri)

5.       Deduct the monthly risk free rate from Ri = (Ri-Rf)

6.       Calculate monthly returns from the index points (Rm)

7.       Deduct the monthly risk free rate from Rm = (Rm-Rf)

8.       Ri-Rf is your dependent variable and Rm-Rf is your independent variable

9.       Use regression technique to find the coefficient of Ri-Rf, this coefficient is your beta

10.   After finding beta, use the CAPM equation to find out the required rate of return on your stock

11.   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

How to use regression in Excel

1.       Go to Tools  >> Data Analysis>>Regression>>Input Y range>> Select your (Ri-Rf) Column>>come back to regression window and select X range>>select your (Rm-Rf) range

2.       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 r