Excel Project Instructions
Suggested data source: Yahoo! Finance, Bloomberg, Fact set, FINRA.
Estimate the firm’s cost of capital:
1) Estimate the market value of common equity and debt. Explain how you get your estimates.
a. Equity: stock price times shares outstanding; often know as market cap.
i. You can use the close price of last trading day, times the shares outstanding
(found in Yahoo Finance à Statistics à Share Statistics section).
ii. Or simply the Market Cap on top of the Statistics page.
b. Use book value of debt as an approximation of market value of debt. Simply go to
Yahoo Finance à Financials à Balance Sheet, click the Quarterly tab, use the most
recent Total Debt.
Or go to Yahoo Finance à Statistics à Balance Sheet section, find Total Debt of the
most recent quarter.
2) Estimate the cost of equity using CAPM model.
a. What is the firm’s equity beta? Use the most recent 5 years of monthly return data
to estimate the firm beta.
See separate instructions at the end of the file to estimate the equity beta.
b. Assume market risk premium is 6%.
c. For risk-free rate, use the current 10-year US Treasury rates on the FINRA
website.
Go to www.finra.org/marketdata, click Bonds on the left panel. The 10-year
Treasury Yield is found under Bond Yield and Performance At-A-Glance section.
3) Estimate the cost of debt.
a. Go to FINRA (www.finra.org/marketdata), find the yield-to-maturity (YTM) of
the firm’s long-term debt.
Go to FIN RAà Bonds à Search, search by symbol, type in the firm ticker in the
symbol box, click show results. Choose a long-term bond with maturity around 30
years (or the next longest if there is none with 30 years to maturity). The Yield to
Maturity (YTM) on this bond is the cost of debt for the firm.
If you do not find any long-term debt with this firm, try to pick a different firm.
Estimate the firm beta:
1. Go to Yahoo! Finance.
2. Type in your stock’s ticker symbol or company name in the search box.
3. Select “Historical Data”.
4. For Frequency, select “Monthly”.
5. Change the Time Period so that you have the latest 5 years of monthly data, then click
“Apply”.
6. At the top right corner select “Download” and save the file to your computer.
7. Market data on S&P500 (i.e., market return) is provided in the sample Excel file in sheet
“S&P500_ending Apr 2023”. No need to search and download for S&P500 data again.
8. Bring up both files in Excel and combine into a single spreadsheet.
Be careful to match the time period between your firm returns and the S&P500 returns:
the most recent monthly return should be Apr 1, 2023.
9. The “Adj Close” heading is the closing price for the stock, after it’s been adjusted for
dividends and splits. Therefore, you can calculate monthly returns as:
�!”# %&%& = �!”# %&%&
‘() *+,-” − �./0 %&%&
‘() *+,-”
�./0 %&%&
‘() *+,-” = �!”# %&%&
‘() *+,-”
�./0 %&%&
‘() *+,-” − 1
Do this return calculation for each month (except the earliest one, because you don’t have
a “previous price” for it).
Repeat this for the S&P500 index to get the returns on the S&P500.
10. Estimate the beta in Excel:
a. Method 1: Calculate the covariance between the company’s stock return and the
S&P500 (market) return using the function COVARIANCE.S. This will create the
numerator of the beta formula shown below. Use the VAR.S function to get the
variance of the S&P500 (market) return, which is what you plug into the
denominator of the beta formula shown below. Finally,
�1 = ���1,3
���3
b. Method 2: You may run a regression in Excel. First, you need to add the data
analysis packet into Excel if you don’t have it (https://support.microsoft.com/enus/office/load-the-analysis-toolpak-in-excel-6a63e598-cd6d-42e3-9317-
6b40ba1a66b4). You then go back to the data tab within Excel. The far-right box
should see Data Analysis in it. Click it. Now you have a bunch of analysis tool
choices available, and you should choose the regression tool. Select the individual
stock’s returns as the y-range. Select the market’s returns as the x-range. Make
sure the output is directed to someplace “away” from the raw data. Click ok. The
beta is the coefficient on the x variable.
