Topic: Interest rate risk in a commercial bank
This project is a real-world analysis of interest rate risk in a commercial bank of your choosing.
You will apply your ability to acquire, analyze, and interpret data using Excel on a deep level.
REQUIREMENTS Use MS WORD to create a report with TWO sections that follow. Prepare a cover page stating your name, course #, date, and title. Your report should include at least one graph from section
1, and two tables of regression results from section
2. All of your descriptions & conclusions should be provided in your WORD document. Do not include a time-series list of your raw data in this report. All data & calculations are performed in Excel.
Professional standards for presentation, grammar, etc. are expected. SECTION 1: Background, Industry, & Market Overview 1)
Go to finance.yahoo.com and enter the ticker symbol of your bank located on the left-hand side of the screen.
Click on the chart portion that appears on the screen and select 5yr to review the stock price over the last 5 years.
Click on “compare” with the S&P 500 during that time period. Add the banking industry benchmark ticker to compare: DJUSBK. Now you have three series on your chart.
You can cut & paste this graph from the internet into your report.
2) Reference the chart and briefly describe the stock price performance of your bank against the S&P500 and the banking industry. Does your bank outperform or underperform?
3) Search the internet (e.g., Dow Jones, Yahoo, other) and briefly describe one or two articles that talk about your bank’s performance. Rider’s library databases can be used to search numerous publications. A business reference librarian can also assist you given some lead time.
4) Search the internet to identify some of your banks competitors and compare the size of your bank to the others. Elaborate. (Again, Rider’s library contains databases that can help.)
5) Feel free to add anything else that you discover about this bank, industry trends, or current challenges. SECTION 2: assess the impact of interest rate risk on your bank’s performance. (Excel Regression) 1) Go to finance.yahoo.com and enter the ticker symbol of your bank located on the left-hand side of the screen. Next, click on “historical prices” and enter the dates for the most recent 5-year period and select the “monthly” data button. Look for the option to download the data to a spreadsheet. You should have 60 data points – keep the column with the heading “adj close” and delete the remaining columns. (You can also use FactSet to retrieve the data, but it is more complicated than yahoo). 2) Visit https://fred.stlouisfed.org to obtain interest rate data: 3-Month Treasury Bill: Secondary Market Rate and 10-Year Treasury Constant Maturity Rate. Extract the data for the same starting and ending period as the stock data. 3) Your Excel spreadsheet should have 4 columns: date by month, stock prices, T-bill, T-note.
You need to calculate the percentage change, by month, for each of the 3 series. It’s a little difficult to interpret the dates after taking the percentage change from month to month – be consistent across the 3 series and your data will be fine.
Now your spreadsheet will have 6 columns of data. [Instructions on calculating percentage change are in appendix B of your text.] After you take the % change your regression will have only 59 data points. 4) Run 2 separate regressions. Use the percentage change in the stock price as the dependent variable, and one of the interest rates (% change) as the independent variable. Repeat the regression using the stock price changes against the second interest rate (% change).
5) Address the following in your WORD report: a) Provide a table of results for each regression and interpret your findings within your WORD document. b) Comment on your output. Specifically, is the stock price of your bank significantly related to either interest rate? If yes, what is the direction (positive or negative).
Explain what it means to the stock price if it is positive or negative. Check the R-squared statistic – a value closer to one indicates a strong relationship between the stock price and interest rates. Report your findings on the strength of this relationship. Even a value of .20 indicates that interest rates are important. c) Provide some conclusions of your analysis. You are assessing the impact of interest rates on stock prices. Why or why not is there any effect? Relate your findings to this bank’s interest rate risk management – does your bank appear to control for interest rate risk based on your findings? d) Include the referenciques that you use at the end of the report.SHOW LESS