Reference no: EM13854587

Useful information will be found in the video, the sample Excel sheets, or your text's tool kit Excel sheets. For instance:

Bonds
• You enter the ticker symbol about half way down on the Morningstar bond page which you accessed by clicking on bonds on the first page.

• You will then see all the bonds for the company, in this case, Southwest Airlines.

• Copy and paste the information into an excel spreadsheet. You may adapt the bond spreadsheet given to you, or show your calculations in a new spreadsheet or show in another document

Calculating the Weighted Average Cost of Debt

1. Find the market value of each bond issue. To do this find the number of bonds and then multiply by the price of the bond (remember that bond prices are quoted in 100s, but are really 1000s).

2. If there is no bond price, assume $1000 par is the price.

3. For these bonds, the YTM =coupon rate

4. Calculate the total market value of bonds

5. Calculate the weights for each bond issue as market value of bond issue/ Total market value of debt. Make sure your weights sum to one.

6. For each bond issue, multiply the weight by that issue's YTM.

7. Sum the weighted YTMs, and you now have the weighted average RD,

Calculating the Weights for the WACC

1. MV of bonds has already been calculated. To that you will add in the value of leases from the Balance Sheet

2. For Preferred Stock, find the number of preferred shares in the annual report and the prices in the WSJ Market Center

3. For common equity, find the price and number of shares in Yahoo Finance.

4. Can use example found in bottom half of bond worksheet or develop your own.

Calculating the Required Return on Preferred Stock

1. To Calculate RPF, we use the constant dividend model, ie. the perpetuity model.

2. RPF = Dividend/P0

3. Check in your company's annual report to see if they have preferred stock and what the dividend is. Make sure you use the yearly dividend since we are calculating annual returns.

• Prices can be found in the WSJ Market Center:

Calculating the Required Return on Common Stock

CAPM

Determining Beta

1. Find the beta from Yahoo Finance and Value line for your firm.

2. Perform a regression using stock returns versus the appropriate market return. For most firms, the S&P 500 will be sufficient; if you chose a relatively small  firm, you might want to use the NASDAQ returns.

EXAMPLE:

The example that I show you uses IBM and 60 months of historical monthly price data. I also used the monthly price data for the S&P 500.

This information is downloaded first and then only the needed columns are cut and pasted into the spreadsheet and the monthly returns will be automatically calculated.

Data on stock prices and dividends can be downloaded from the web and used to make betas for real companies. I demonstrate the process for IBM in this section. I downloaded stock prices and dividends from https://finance.yahoo.com for IBM using its ticker symbol IBM. I also downloaded data for the S&P 500 Index, whose symbol is ^GSPC to represent the market. Here are the steps I followed:

This will yield the Regression input box. If Data Analysis is not an option in your Tools menu, you will have to load that program. Loading the Analysis ToolPak is different in each version of Office so you will have to determine how to find it. Once it is loaded, you will now be able to access Data Analysis. From this point, you must designate the Y input range (stock returns) and the X input range (market returns). You can have the summary output placed in a new worksheet, or you can have it shown directly in the worksheet, as I did, but be careful that you place it in a blank area of your spreadsheet.

You may decide that a years worth of weekly data would be better, perhaps if there were something unusual two years ago. You could also use daily data, generally 200 days worth. In these cases you will have to adjust the spreadsheet for fewer/more data points.

3. You must now decide if you want to average your betas or drop one of them. In our example , the calculated beta was .643 and we had two other estimates of .66 and .85. It would be your choice to either drop the .85 and average the other two, or average all three.

You must justify your choices and provide citations in the body of your report and references at the end.

You must now decide what value you will use for RF and either RM or RPM where
o CAPM: RE = RRF + (RM - RRF)b
= RRF + (RPM)b.

Again, You must justify your choices for RF and either RM or RPM and provide citations in the body of your report and references at the end.

The Own-Bond-Yield-Plus-Judgmental-Risk-Premium Method

• RE = RD + Judgmental risk premium

• This judgmental-risk premium ¹ the CAPM equity risk premium, RPM

• Produces ballpark estimate of RE

• Useful check, particularly if Dividend Growth and CAPM are significantly different

• Again, you must justify your answer

Dividend Growth Model

• RE = {[D0* (1+g)] /P0 } + g

• The challenge here is to estimate the growth rate. There are several suggestions in your text as well as in earlier videos.

Corporate Tax Rate

• You may be able to find the corporate tax rate directly in the annual report

• Or you can choose to use the formula

o Taxes = Tax Rate * Earnings Before Taxes

• Since both Taxes and Earnings before Taxes are in the Balance Sheet, you can then calculate the tax rate