Excel代写 | RSM 432: Spring 2020 Assignment 2

这个作业是用Excel统计债券投资回报率
RSM 432: Spring 2020
Assignment 2
Page 1 of 4
Internal
Assignment 2: Due date: April 5th, 2020 by 11:59 pm
This assignment is to be submitted in your team used for Assignment 1!
ALL NAMES ON ALL DOCUMENTS SUBMITTED!
Assignment is out of 100 points.
Question 1: Credit (20 points)
Green Triangle company, a Baa-rated company, has a market credit spread of 130 basis points on
a five-year bond. The recovery rate is 40%. Calculate:
a. the average hazard rate per year over five years using the data from table
19.1 at the end of this assignment
b. the average hazard rate per year over five years using credit spreads.
c. What is the expected risk premium earned by bond traders
d. What factors can help to explain the magnitude of the expected risk
premium calculated in part c. Please explain in detail with an example.
e. Assume next that the bond provides a coupon of 5% per year paid
semiannually, has a yield of 6% (with continuous compaunding) and the
risk free yield is flat at 3%(with continuous compounding). Assume that
default occurs at the end of the year (right before coupon payments).
Estimate the default probability assuming that it is the same each year.
Question 2: Regulation (15 points)
Great Lakes Bank’s balance sheet (in billions USD) is shown below. Under Basel III
requirements, bank management must ensure that the Net Stability Funding Ratio (NSFR)
exceeds the minimum threshold.
Cash 3 Retail Deposits (Stable) 25
Treasury Bonds (> 1 yr) 5 Retail Deposits (less Stable) 15
Corporate Bonds Rated A 4 Wholesale Deposits 44
Mortgages 18 Preferred Stock (> 1 yr) 4
Small Business Loans (< 1Yr) 60 Tier 2 Capital 3 Fixed Assets 10 Tier 1 Capital 9 100 100 a) What is the NSFR for Great Lakes Bank? b) If the ratio is below the threshold, consider the following two ideas to ensure that the NSFR threshold for the bank is acceptable. 1. raise more stable retail deposits and keep proceeds in corporate bonds (AA & higher) 2. raise more wholesale deposits and put the proceeds into GoldRSM 432: Spring 2020 Assignment 2 Page 2 of 4 Internal c) Do you have a preference of option 1 or 2 in part b? d) What would be the most efficient manner from an additional funding and investment point of view to achieve the NSFR threshold. Question 3: Regulation (15 points) The assets of a bank consist of $200 million of loans to A-rated corporations with the principals being repayable at maturity. The Probability of Default for the corporation is estimated as 0.4% per year. The loan maturities are three years and the LGD is 60%. a. What is the total risk-weighted assets for credit risk under the Basel II advanced IRB approach? b. How much Tier 1 and Tier 2 capital is required? c. How does this compare with the capital required under the Basel II standardized approach and under Basel I? d. How much equity capital (including that for the capital conservation buffer) will be required under Basel III if the advanced IRB approach is used. Question 4: Credit (10 points) The value of a company’s equity is $2.8 million and the volatility of the equity is 50%. The debt that will have to be paid in two years is $10 million. The risk-free interest rate is 2% per annum. a) What is the difference between risk-neutral versus real-world probabilities? b) Use Merton’s model to estimate the probability of a default on the debt in the next two years. Question 5: Scenario Analysis / Stress Testing (25 points) Use the historical stock data provided in Assigment2_Spring2020_data, create a portfolio using 3 stocks and your own determination of percentage allocation (to equal 100%). Clearly state your portfolio allocations. No short selling permitted. No justification of portfolio waits is required. a) Calculate one-day 99% VaR of your portfolio from January 3, 2012 to December 31, 2018. For the purpose of $VaR portfolio loss calculation, assume that the initial value of your portfolio on January 3, 2012 is worth $1 million dollars. b) Using a historical simulation approach with 500 scenarios, calculate one-day 99% VaR from January 3, 2012 to December 31, 2018. Remark: your historical simulation for VaR on January 3, 2012 would involve 500 past daily portfolio performances. c) Using the EWMA model with 0.94, calculate one-day 99% VaR of your portfolio from January 3, 2012 to December 31, 2018. d) Plot daily values ($) of your one-day 99% VaR from January 3, 2012 to December 31, 2018RSM 432: Spring 2020 Assignment 2 Page 3 of 4 Internal that you calculated using: historical simulation and the EWMA model in one figure. Discuss your findings on their similarities/differences. e) Create one stress testing scenario for this time period and data set. Describe your approach. f) Create one scenario testing situation for the data above. Describe your approach. g) Evaluate the portfolio VaR results for the January 1 2020 to present (you may add stock close prices to this data set). Comment on VaR exceedances for the initial VaR thresholds calculated and for the scenario and stress testing cases. Question 6: The future of risk management (15 points) Risk management in banking has been transformed over the past decade, largely in response to regulations that emerged from the global financial crisis and the fines levied in its wake. But important trends are afoot that suggest risk management will experience even more sweeping change in the next decade. Consider the outbreak of COVID-19 witnessed during the last couple of months. What do you think this will mean for financial institutions (FI)? Do you think that FI were prepared to withstand the shock? Discuss the merits and problems of the current regulations and how they will shape the reaction of the financial market to the coming shock (max 250 words).RSM 432: Spring 2020 Assignment 2 Page 4 of 4 Internal Appendix A