CAPM in EXCEL The Capital Asset Pricing Model

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 )

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

Daily Closing Prices of Riyad Bank and TASI in 2008

Calculating the Stock Return

Calculating the Market Return

Riyad Bank and TASI Daily Returns

Calculating the Average Market Return

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%

Calculating Beta Method 1

Calculating Beta Method 1

Calculating Beta Method 1

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

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.

Calculating Beta method 2

Calculating Beta method 2

Calculating Beta method 2

Calculating Beta method 2

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.

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.

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 the 95% level. is statically significant at �If P- value < 0. 1 the 90% level (weak). is statically significant at

Beta and the P-Value of Riyad Bank � β = 0. 881 P-Value= 3. 77 E-45 � P-value< 0. 01 � We reject the β is statically significant at the level 99%. . � 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. .

Interpreting Beta �If increase by 1% increase by 0. 881% �If increase by 2% (2 X 0. 881 %) increase by 1. 761 % �If decrease by 5% (5 X 0. 881 %) decrease by 4. 403%

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.

Compare Beta with Analysts’ report

Get the Risk Free Return

Get the Risk Free Return

Calculating the Required Return on Riyad Bank using CAPM

Required Return on Riyad Bank using CAPM

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.

Thanks for Listening Prepared by: Rawaa Muhandes Rawaa_86@hotmail. com