Reference no: EM133059869

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

Step 1: Download historical data for Citigroup stock prices from Yahoo! Finance website.Visit the website finance.yahoo.com in a web browser. In the search box, enter ticker C for Citigroup stock. Click on tab for Historical Data. Change time period to January 1, 2016to January 2, 2021. Change Frequency to Monthly. Click "Apply" button. Then click link labeled "Download Data" to download a csv 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-termTreasury rate, a measure of risk-free rate (note the ˆ sign in front of IRX).

Step 2: You will use only two columns from each csv 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 C and SPY by dividing each price by the pricein the previous month and subtracting one. For example, return in January 2016 is (Price at the beginning of February 2016 dividend by Price at the beginning of January 2016) - 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 0.6 for ˆIRX is converted to 0.6% = 0.006 per year = 0.006/12 =0.0005 per month.

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

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

(b) Estimate beta of Citigroup. You can use Excel's SLOPE function or LINEST functionor any other method or software.

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