EMFPS: How Can We Obtain “Beta” to Analyze CAPM? (CON)

Referring to my previous article of "EMFPS: How Can We Obtain “Beta” to Analyze CAPM?" on link: http://emfps.blogspot.com/2011/09/emfps-how-can-we-obtain-beta-to-analyze.html, let me explain you the next steps by an example as follows:

1) Without any dividends:

Assume the data of Company “M” from Jan 3, 2005 to Dec 1, 2010 is below cited:

Date	Open	High	Low	Close	Avg Vol	ADj Close*
Jan 3, 2005	4.74	4.92	4.7	4.88	792,600	4.21

--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

Dec 1,           2010

5.74

6.52

5.74

6.23

4,193,000

6.23

The data of the related Stock Index of the company is as follows:

Date	Open	High	Low	Close	Avg Vol	Adj Close*
Jan 3, 2005	907.02	940.94	897.13	916.27	54,441,400	916.27

--------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------

Dec 1, 2010

1,482.69

1,529.95

1,477.57

1,518.91

141,888,300

1,518.91

-To calculate the return rate for both Company and Stock Index, we can use below formula because there is not any dividends:

Ra = (Pt – Pt-1) / Pt-1

Ra = the return rate for Stock Index and Company

P t-1 = Close price on Jan 3, 2005

Pt = Close price in the next month

-On excel spreadsheet, calculate one cell by using of above formula and copy on all cells related to next months until Dec 1, 2010.

-Now, you have all rate of return for each month (Company and Stock Index). Calculate the average for Stock Index only

-Multiply the average of Stock Index by 12 to obtain the rate of return annually (Rm) which is utilized to analyze CAPM

-Use from excel formula to calculate covariance of the return rate for both Company and Stock Index:    =COVAR (Array1, Array2)

 Where:

Array 1 = all return rate of Company

Array 2 = all return rate of Stock Index

-Use from excel formula to variance of return rate for Stock Index:  

= VAR (number1, [number2]…..)

-Calculate the Beta of Company: COVAR (Ri,Rm) / VAR (Rm)

According to my spreadsheet, I obtained the Beta to be equal 1.501137

2) With dividends:

Now, we assume that the company had paid the dividends as follows:

Date			Dividends
17-Jan-07	0.05
27-Jul- 07	0.05
29-Oct-08	0.25
29-Jul-09	0.05
28-Jul-10	0.11

We should look at the date of dividend and improve the company’s return rate accordingly on our spreadsheet by using of below formula:

Ra = [D + (Pt – Pt-1)] / Pt-1

Where:

Ra = the return rate of Company

D = dividend

Pt = Close price for month after dividend date

Pt-1 = Close price for month before dividend date

I calculate the Beta of the Company which is equal to 1.499519

Why are they approximately the same (without and with dividend)?

Because when the company paid the dividend accordingly its share price fell down which shows us the dividend policy of the company. I can say to you this dividend policy is not wrong because the Beta of with dividend is not more than the beta of without dividend.

Conclusion

As the result of this article, I would like to compare the market return of this company with historical market return of the world (see sheet 4 of my spreadsheet).

I calculated annually return rate of Company “M” by adding all monthly return rate (with dividends) then I used from historical returns mentioned on below link:

 http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/histret.html  (By: Aswath Damodaran)

To analyze the Beta, I got the gradient as follows:

Beta = Delta (company’s return) / Delta (Market return)

Here is my data:

Year		Rc	Rm
2005		-7.67%	4.83%
2006		64.12%	15.61%
2007		-3.31%	5.48%
2008		-49.42%	-36.58%
2009		39.61%	25.92%
2010		41.53%	14.86%

Where:

Rc = the return rate of Company

Rm= the return rate the whole of the market

Year	Beta
2006	6.659839
2007	6.656976
2008	1.096235
2009	1.424453
2010	-0.17352
B (av)	3.132797

As we can see, in this case, the average of Beta is not important but the trend of the Beta is very contemplative. I remember that I wrote an article of “ New Economic System” on below link:   http://emfps.blogspot.com/2010/10/new-economic-system-part-1.html Maybe this method to analyze the Beta will lead us toward the better  perception and analysis of economic systems.   Note:  “All spreadsheets and calculation notes are available.  The people, who are interested in having my spreadsheets  of this method as a template for further practice, do not  hesitate to ask me by sending an email to : soleimani_gh@hotmail.com or call me on my cellphone:  +989109250225. Please be informed these spreadsheets are not free of charge.”

EMFPS: How Can We Obtain the “Beta” to Analyze CAPM?

We need to CAPM analysis not only to evaluate the risk of company’s assets portfolio but also to assess the feasibility of the projects by using of capital budgeting method. As you know, the formula of Capital Asset Pricing Model (CAPM) is as follow:

CAPM = Rf + [B * (Rm – Rf)]

In this article, I am willing to expand my debate on “B”.

At the first, we should divide the companies in two groups:

                      ·                      Private companies: Where the expected return of the company has been calculated in accordance with EBITDA multiplier’s method.

                      ·                      Public companies: Where the expected return of the company has been calculated in accordance with the share price.

Here, I am willing to depict the Beta of public companies. Then I will bring you an example as the practice and finally I will compare the market return of a Stock Index with historic market return of the world in which the result will be referred to the Economic Systems.

Before everything, we should clear our target as follows:

1) Do we want to anticipate the Beta for next several years?

2) Do we want to calculate the Beta by using of historical information? In this case, we should classify the companies in accordance with their dividend policy as follows:

Ø  The company has paid the dividend in the period of our limited time.

Ø  The company has not paid any dividend in the period of our limited time.

If we are expected to anticipate “B” for next several years, we should use from behavioral approach for instance, simulation method such as the Monte Carlo simulation programs in which we have to define several possible alternative outcomes just like to Scenario Analysis and then we should try to determine probability distribution and random numbers to estimate the percentage of probability which is matched to each required return and market return for each one of the outcomes. For example, we can extract some economic data forecasts from IMF (International Monetary Fund) such as GDP Growth (Constant Prices, National Currency) for revenue growth, Inflation (Average Consumer Price Change %) for COGS, Unemployment Rate (% of Labour Force) and government interest rate for Selling and Administrative Expenses and so on. Then we need to build an Excel spreadsheet such as Kimi Ford’s sensitivity analysis included in Exhibit (2) of Case analysis of Nike, Inc: Cost of Capital (see the link of http://emfps.blogspot.com/2011/06/case-analysis-of-nike-inc-cost-of.html). Finally we will obtain something like below example by using of the Monte Carlo simulation program:

Assume we want to calculate the Beta of Company “M” for the period of next ten years. According to above mentioned, we have obtained below data:

Outcomes of the Economy  Probability  Market Return  Company’s Return

  Stagnant                                  15%                    7%                        9%

  Slow growth                            25%                    11%                     13%

  Medium growth                       30%                    14%                     18%

  Rapid growth                          30 %                    21%                     27%

Now, we are able to calculate the expected return of Market and Company by multiplying the percentage of probability by them as follows:

Expected return of Market = SUM [(probability)*(Market Return)] = 14.30 %

Expected return of Company = SUM [(probability)*(Company’s Return)] = 18.10 %

Regarding to the formula of the Beta, we have:

B = Cov (ra, rm) / Qm^2

Where:

Cov (ra, rm) = SUM [(probability) * (Market return - Expected return of Market) * (Company's return -Expected return of Company)]

Qm^2 = variance of the return on the market portfolio = SUM [(probability)* ((Market return - Expected return of Market)^2)]

Cov (ra, rm) = 0.003207

Qm^2 = 0.00242

B = 0.003207 / 0.00242 = 1.325

Now, let us focus on historical data to analyze the Beta of Company “M”. In this case, we do not need to have any probability distribution because all events have been already occurred and we can consider the related probabilities to be equal. Therefore, the expected return is the same the average of Company’s returns during the period of our chosen time.

The method of the Beta analysis will be done step by step as follows:

-Go to one of the financial websites such as: http://finance.yahoo.com

-On “GET QUOTES” search the name of your chosen Company

-Click on “Historical prices”

-I usually search for monthly but you can also search daily or weekly (you should remember that finally the return rate should be changed to annually).

-Copy and Paste all data on your Excel spreadsheet

- On “GET QUOTES” search the related Stock Index of the company to obtain the market return

-Click on “Historical prices”

-Search your chosen time just like to the time extracted for the company

-Copy and Paste all data on your Excel spreadsheet

To be continued …….

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this method as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225.   Please be informed these spreadsheets are not free of charge.”

Case Analysis of Nike, Inc.: Cost of Capital (CON)

Referring to my previous article (http://emfps.blogspot.com/2011/06/case-analysis-of-nike-inc-cost-of.html), someone who had received my spreadsheet, had the problem to calculate the Cost of Debt by using of IRR method (Method 2). As the matter of fact, this method can be utilized to obtain the required return rate on Bonds in which you can use it even in your own business.

How can we use from this spreadsheet to calculate the required return rate of Bonds?

Here I am willing to explain it step by step as follows:

Please look at the spreadsheet.

-Enter your new Coupon rate (annually) on Cell C9 and Price of Bond based on par value equal $100 on Cell C10

-You should use the try and error method by changing on required return rate speculated by you on Cell C21 and C22 in which finally Cell C23 will be equal to Cell C17 (C23 = C17).

How can you guess the required return rate?

I have already depicted it on below link:

 http://emfps.blogspot.com/2011/06/case-analysis-of-nike-inc-cost-of.html

Where I stated: “Which method is to calculate cost of debt better than others?  It is important to find the relationship between the required return and the coupon interest rate. When the required return is greater than the coupon interest rate, the bond value ……”

Let me bring you an example as follows:

Assume the company has issued the $500,000 nominal amount of 8% rate of Bonds from 1999 to 2009 was issued by a subsidiary at $95.068 per $100 par value. How can we calculate the required return rate by using of my spreadsheet?

-Enter on Cell C6: 1999

-Enter on Cell C6: 2009

-Enter on Cell C9: 8%

-Enter on Cell C10: 95.068

-Since the price of Bond is less than Par value, our speculate on required return rate should be more than Coupon rate (annually)

-Click on Cell C21where we can see this formula: = - PV (rate, number of years, Coupon payment). Of course, the previous numbers on Cell C21 are: = - PV (0.1415, 25,135)

-Replace the amount for required return more than Coupon rate, for instance, 8.5%. Where we have: = - PV (0.085, 10, 80) or = - PV (0.085, C19, C18)

-Click on Cell C22 where we can see this formula: = Par Value / (1+required return) ^n. Of course, the previous numbers on Cell C22 are: = 1000/ ((1+0.1415) ^ 25)

-Replace the same amount of required return (8.5%) and power equal to 10. Where we have: = 1000 / ((1+0.085) ^ 10 or = C20 / ((1+0.085) ^ C19

-We can see that Cell C23 is more than Cell C17 (967.19 > 950.68). Therefore, we should increase again the required return rate to 8.8%

-All previous steps should be repeated. Now, we have: C23 = 948.20 which is less than C17= 950.68

-In this case, we should decrease the required return for instance to 8.76%. Finally we will have C23 approximately equal to C17 (C23 = 950.71 and C17 = 950.68)

In the result, the required return rate will be equal to 8.76%.

 

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this case analysis as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225. Please be informed these spreadsheets are not free of charge.”

Risk of Assets

Let us look at the price of Soybeans as one of the agriculture commodities and price of Gold during the period of one year (From Aug 31, 2010 to Aug 31, 2011) as follows:

Date                                          Aug 31, 2010                            Aug 31, 2011   

Price of Soybeans                   1,045.25(¢ / bushel)                    1,457.00 (¢ / bushel)

Price of Gold                          1247.29 ($ / troy ounce)              1,829.80 ($ / troy ounce)   

                   

If you have stored one troy ounce of Gold, your real rate of return will be: ra = 46.7%

But if you have invested on Soybeans during the period of one year, definitely you can earn minimum 8% of  your initial investment which is equal to 9978.32 cent (1247.29 * 8%  * 100 ) as your cash earning or cash dividend.  To calculate the rate of return for Soybeans, we have:

V(t-1) = 124729  cent 

Amount of bushel = 124729 / 1045.25 = 119.33 bushel

V t = 119.33 * 1,457 = 173862.86 cent

ra = 9978.32+ (173862.86 -124729) / 124729  =  47.4%

As you can see, the risk of two assets is approximately the same.

Of course, soybean oil alone accounts for about ninety percent of all fuel stocks in the US.

Referring to the link of http://www.nytimes.com/2006/07/13/business/13ethanol.html, it stated:

“Biodiesel produced from soybeans produces more usable energy and reduces greenhouse gases more than corn-based ethanol, making it more deserving of subsidies, according to a study being published this month in The Proceedings of the National Academy of Sciences.”

Now, I willing to know if above mentioned is a good news or bad news.