[SOLVED] ASB4416 CREDIT RISK ANALYTICS ASSIGNMENT SEMESTER 1 2024 SQL

ASB4416 – CREDIT RISK ANALYTICS ASSIGNMENT

(SEMESTER 1, 2024)

(25% of total module mark)

SUBMISSION DEADLINE – 4PM FRIDAY 22ND NOVEMBER 2024

Please submit an electronic copy ONLY via Turnitin

You are reminded not to copy material from any sources without properly referencing it, as this constitutes plagiarism: cases of plagiarism will be referred to the Business School Academic Integrity Officer and will be subject to a deduction ofmarks, which can result in an overall mark of 0%.

Background

You have to apply the skills you’ve learnt during the lectures (Merton Default Model) and use Excel to answer the assignment question.  You must answer all your questions in one document (i.e. Word) and provide screenshots or copy and paste your working from Excel into the document.  You will need to clearly explain the steps you have taken in Excel.

Do not present your answers in essay form. You are assessed on how you use Excel to answer the questions, and then how you discuss your findings. Therefore your answers need to be clear and concise.

The word limit for the report is 1,500 words.

You can collect data to complete the assignment from whatever source you feel best BUT you need to clearly specify the source of any data used.

Assignment

Using the Merton’s structural model with a one-year horizon, calculate the probability of default (using both the iterative approach and two-equation approach) for two US non-financial firms between Q1 2022 and Q4 2022 (i.e. a full calendar year).  Compare the results and describe your calculations.

To complete this assignment you should,

1.    Download the following information between Q1 2022 and Q4 2022 for each firm:

a.    the daily equity price and amount of common shares in circulation (to calculate the market equity),

b.   the quarterly total book liabilities.

2.    Download the daily S&P 500 index price for the Q1 2022 to Q4 2022 period.

3.    Assume the risk free rate to be 3% throughout the whole period.

4.    For each firm, calculate the probability default (using both the iterative approach and two-equation approach) using the Merton’s structural model with a one-year horizon.  As part of your calculations you will need to:

a.    Calculate the asset volatility,

b.    Perform. CAPM calculations,

c.    Derive the implied default probability,

d.    Calibrate the Merton model.

5.    In your report, you will need to:

a.    briefly describe the two firms, including industry, overall structure, and performance over the period,

b.    explain and briefly describe the data feeding into the model and how it is used,

c.    describe the calculations performed in the Excel workbook,

d.    compare and explain the different approaches for calculating the probability of default;

i.   iterative approach,

ii.   two-equation approach

e.    interpret the output of the Merton model and describe key parameters of the model,

f.     Compare the probability of default results for the two firms.