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. 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 |
Portfolio Management > Assignments >