Project
You will need to use Excel/VBA or another programming language to model and analyze a collateralized bond obligation (CBO).
For modeling purposes, I have aggregated the hundreds of corporate bonds that populate this security into 20 discrete components. Base-case weighted average coupon (WAC), weighted average maturity (WAM), default, prepayment and loss given default assumptions are provided below. Note that one half of the bonds pay a fixed rate of interest, while the other half pay a floating rate indexed to 3-month LIBOR.
Collateral: Loss
WAM Principal Annual Annual Given
WAC (quarters) ($millions) Default Rate Prepay Rate Default
Libor3 + 225 bp 30 20 4% 2% 20%
6.10% 30 20 6 5 10
6.20 40 25 6 4 20
Libor3 + 250 40 20 8 1 20
Libor3 + 250 40 20 4 2 20
6.45 50 20 5 3 20
6.79 50 25 5 5 40
Libor3 + 275 50 25 4 1 20
Libor3 + 275 50 20 6 2 20
7.22 60 20 8 5 10
Libor3 + 300 60 40 8 3 50
7.54 70 40 8 5 50
Libor3 + 325 70 20 4 2 20
7.72 80 20 4 4 20
7.91 90 20 3 5 60
Libor3 + 335 90 20 6 3 50
Libor3 + 350 100 20 8 5 40
8.12 110 25 4 3 50
Libor3 + 400 120 30 6 5 50
8.56 120 50 8 5 40
The CBO will consist of 5 classes of notes described below.
Triggers (%)
Class Rating Principal Coupon I/C O/C
A AAA $350 LIBOR3 + 75 bp 115 107
B A $50 LIBOR3 + 125 bp 112 104
C BBB $35 LIBOR3 + 250 bp 110 102
D BB $15 LIBOR3 + 750 bp 105 101
E unrated $50 Excess
Assume there is no re-investment period and the transaction is static (i.e., there is no asset manager). During the amortization period, the interest cash flow waterfall matches the flowchart below.
CDO Waterfall
Interest Proceeds
Taxes, Fees, Hedge Payments
Class A Interest
Class A Coverage Tests
Pass Fail
Class B Interest Class A Principal
Class B Interest When Cured
Class B Coverage Tests
Pass Fail
Class C Interest Class A Principal
Class B Principal
Class C Interest When Cured
Class C Coverage Tests
Pass Fail
Class D Interest Class A Principal
Class B Principal
Class C Principal
Class D Interest When Cured
Class D Coverage Tests
Pass Fail
Subordinated Management Fee Class A Principal
Class B Principal
Class C Principal
Class D Principal
Subordinate Fee When Cured
None of the CBO notes are PIK-able. Assume taxes, fees, hedge payments and the subordinated management fee are all zero. The principal cash flow waterfall is described below.
Principal Waterfall
Collateral Principal Proceeds
↓
Class A Principal
↓
Class B Principal, if Class A is retired
↓
Class C Principal, if Class B is retired
↓
Class D Principal, if Class C is retired
↓
Class E Principal
Assume the collateral pays quarterly cash flows and the notes pay quarterly cash flows. If the current quarter is April, 2020, then the relevant LIBOR rate (used to compute interest payments on the collateral and the notes) is the LIBOR rate as of January, 2020 (i.e., the reset rate for interest looks back 90 days). You can assume all collateral and security cashflows are paid on the 15th of the month.
Instructions: Please upload the following three files: a WORD document containing the answers to the questions below; a WORD document containing your source code; an Excel macro-enabled workbook containing your source code and output.
- Assume LIBOR remains static at 4.00% for the life of the security. Compute the collateral cashflows under the base case prepayment, default and loss scenario. What is the sum of the (undiscounted) collateral interest payments? What is the sum of the (undiscounted) collateral principal payments? Compute the principal and interest cash flows for all notes. Specify the sum of the undiscounted interest and principal payments for each note. Compute the average lives for all notes and specify the values in years.
- Using the cash flows from question 1, compute the prices of the notes under the 3 scenarios described below. Prices should be stated as a percentage of par/face value (e.g., 98.23%). Discuss the results.
Scenario I Scenario II Scenario III
Spread/Yield Spread/Yield Spread/Yield
Class A 75 bp 125 bp 25 bp
Class B 125 250 75
Class C 250 350 150
Class D 750 1000 500
Class E 15% 20% 10%
- Assume LIBOR starts at 2.00% and increases 5 basis points each quarter for the life of the security. Re-compute the principal and interest cash flows for all notes. Re-compute the average lives for all notes. Compute the prices of the notes using the base case pricing spreads. Discuss your results.
- Assume LIBOR evolves according to the following equation.
with s = .10 and Î represents a random variable from a standard normal distribution. Assume LIBOR0 = 4% and LIBOR cannot fall below 0. Generate 50 rate paths for LIBOR. For each rate path, generate the cash flows for class C. For each set of cash flows, price the class D notes using the initial spread of LIBOR + 750 bp. Graph the prices for each scenario and show the average price. Discuss your findings.
- If this transaction were a typical managed CBO, how would your analysis differ (i.e., what additional features would you need to model)?
Bond cash flow calculations
Balancet + 1 = Balancet – (Scheduled Principalt + Prepaid Principalt) – Defaulted Principalt
Interest Paymentt = Balancet * cpn / 4
Prepaid Principalt = Balancet * annual prepayment rate / 4
Prepaid PrincipalWAM = 0
Defaulted Principalt = Balancet * annual default rate / 4
Total Principal Paidt = Scheduled Principalt + Prepaid Principalt + (Defaulted Principalt * recovery rate)
recovery rate = 1 – Loss given default rate
Scheduled Principalt = 0 if t < WAM
Balancet – Defaulted Principalt if t = WAM
Pricing floating rate securities
For example, if N = 4, then:
Here, CFi is the total cashflow in period i and yi is the interest rate per period in period i. For quarterly cash flows, yi = cpn / 4.
The units for AVL are periods (months, quarters or years, depending on payment frequency).
Bond Calculation Example
I/C test: interest collected from the collateral /
Interest demanded by the tranche and all senior tranches
å (remaining balance) * [(Libor + Spread)/4]
O/C test: principal remaining on collateral t /
Principal remaining on the tranche and all senior tranches
