1 / 31

CAPM in EXCEL

CAPM in EXCEL. The Capital Asset Pricing Model. Where: = the return required by investors on a stock = the average return on the market = the return on risk free investments = the stock’s market risk (how sensitive the stock is to fluctuating in the market ).

africa
Download Presentation

CAPM in EXCEL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CAPM in EXCEL

  2. The Capital Asset Pricing Model Where: = the return required by investors on a stock = the average return on the market = the return on risk free investments = the stock’s market risk (how sensitive the stock is to fluctuating in the market )

  3. Calculating the Required Return on Riyad Bank using CAPM Data Needed: • Share prices of Riyad Bank to calculate the company return ( ) • Share prices of Tadawul All Shares Index (TASI)to calculate the market return ( ) • The return on risk free investments ( ) The data in this example were acquired from Bloomberg

  4. Daily Closing Prices of Riyad Bank and TASI in 2008

  5. Calculating the Stock Return

  6. Calculating the Market Return

  7. Riyad Bank and TASI Daily Returns

  8. Calculating the Average Market Return

  9. The Average Market Return • We simply take the sum of the returns and divide them by the number of observations. • TASI average daily return= -0.003 = -0.3%

  10. Calculating Beta Method 1

  11. Calculating Beta Method 1

  12. Calculating Beta Method 1

  13. Calculating Beta Method 1 However, this method does not tell if β is statically significant or not.

  14. Calculating Beta method 2 • You need to make sure you have Excel Add- Ins already installed. In particular, Analysis Tool Pack. • You can find Excel Add-Ins in by clicking on file. Then, at the bottom, choose Excel Add-Ins. • Then choose Analysis Tool Pack and press ok.

  15. Calculating Beta method 2

  16. Calculating Beta method 2

  17. Calculating Beta method 2

  18. Calculating Beta method 2

  19. Statistical Significance • Null Hypothesis ( ): There is no difference between groups. We assume the null hypothesis is correct until we have enough evidence to reject it. Regarding Beta, the stock is not affected by changes (fluctuations ) in the Market. • Alternative Hypothesis( ): There is a difference between groups. Regarding Beta, the stock is affected by changes (fluctuations ) in the Market.

  20. Statistical Significance • The null hypothesis ( ) in this example: : Riyad Bank has no relationship with the market return. Its returns do not get affected by the market fluctuation. The alternative hypothesis ( ) in this example: : Riyad Bank has a relationship with the market. Its returns fluctuate as the market fluctuates.

  21. P- Value We use the P- value to determine if our Beta is statically significant. • If P- value < 0.01 is statically significant at the 99% level (strong). • If P- value < 0.05 is statically significant at the 95% level. • If P- value < 0.1 is statically significant at the 90% level (weak).

  22. Beta and the P-Value of Riyad Bank • β = 0.881 P-Value= 3.77E-45 • P-value< 0.01 β is statically significant at the level 99%. • We reject the . • We are 99% confident that Riyad returns are affected by the market. • The Coefficient sign is positive Riyad Bank’s returns has a positive relationship with the market returns. If market return increases, Riyad Bank’s returns increases and vise versa. .

  23. Interpreting Beta • If increase by 1% increase by 0.881% • If increase by 2% increase by 1.761 % (2 X 0.881 %) • If decrease by 5% decrease by 4.403% (5 X 0.881 %)

  24. Interpreting Beta • Beta of the market is always = 1.0 • If the stock beta > 1.0, the stock is more risky than average • If stock beta < 1.0, the stock is less risky than average • Thus, Riyad Bank is less risky than average: 0.8177 < 1.0 • Thus, when the average return on TASI is negative -0.003, the return on Riyad Bank must be negative as well. However, lower in magnitude.

  25. Compare Beta with Analysts’ report

  26. Get the Risk Free Return

  27. Get the Risk Free Return

  28. Calculating the Required Return on Riyad Bank using CAPM

  29. Required Return on Riyad Bank using CAPM

  30. Results Interpretation • The required return on Riyad is in negative because the market return is in negative. This is because Riyad Bank is has a positive relationship with the market returns. However, the decrease is lower in magnitude because β is lower than 1.0 which means Riyad Bank is less risky.

  31. Thanks for Listening Prepared by: RawaaMuhandes Rawaa_86@hotmail.com

More Related