You will estimate Merck's equity beta in this problem. You must follow the instructions carefully.

Step 1: Download historical data for Merck stock prices from Yahoo! Finance website.

Visit the website finance.yahoo.com in a web browser. In the search box, enter ticker MRK for Merck stock. Click on tab for Historical Data. Change time period to January 1, 2018

to January 2, 2023. Change Frequency to Monthly. Click "Apply" button. Then click link labeled "Download" to download a cs file.

Similarly download historical prices for ticker SPY of an exchange traded fund on S&P 500, a proxy for market portfolio. Finally, download historical prices for ticker ^IRX for short-term Treasury rate, a measure of risk-free rate (note the " sign in front of IRX).

Step 2: You will use only two columns from each cs file: dates and adjusted close prices.

Combine data in a single spreadsheet to keep one column of dates and three columns of adjusted prices.

Calculate monthly returns for MRK and SPY by dividing the price at

the beginning of the next month by the price at the beginning of the current month and

subtracting one. For example, return in January 2018 is (Price at the beginning of February 2018 dividend by Price at the beginning of January 2018) - 1.

Step 3: Next calculate monthly risk-free rate. Divide each value of ^IRX by 12 to convert from annual rate to monthly rate. Then divide by 100 because it is reported as a percentage.

Specifically, a value of 1.8 for IRX is converted to 1.8% = 0.018 per year = 0.018/12 =

0.0015 per month.

Step 4: Then calculate excess returns for MRK and SPY. For this, subtract the same month's monthly risk-free rate that you just calculated. For example, the last monthly return is for the month of December 2022. It is calculated using the prices (for MRK or SPY) from Dec 1, 2022 and Jan 1, 2023. To get excess return from this return, subtract the risk-free rate that you calculated in Step 4 for Dec 1, 2022. At the end of this step, you have 60 excess returns for MRK and 60 excess returns for the market (represented by SPY).

Provide a scatter plot with Merck's monthly returns on vertical axis and market's (SPY's monthly returns on horizontal axis.

Estimate beta of Merck. You can use Excel's SLOPE function or LINEST function or any other method or sottware.

You must provide both to get credit. Do not provide any other calculations or spreadsheets.