|Asset Management, Financial Model, Investment|
|Amortization, Bonds, Debt Amortization, Debt Schedule, Debt Security, Excel, Investors, Mortgage|
The Debt Securitization is the process of packaging debt into a Securitization Vehicle sold to a Fiduciary where it is converted into bonds sold to investors. These bonds are usually sold in tranches with varying pay-out schedules and risk levels. This model goes through the debt securitization process from the initial debt to the bonds sold to investors. The tabs included are:
• Loan Amortization Table
• Mortgage Pass-Through Table
• Sequential Pay CMO with up to 4 Tranches
• Debt Security Valuation using discounted cash flows
In the first tab, you have the Loan Amortization Table where the details of the Originator’s loan are outlined. Here you can enter the terms of the loan such as length, interest rate, and dollar amount. The next tab is the Mortgage Pass-Through, where the debt is bundled into an SPV and sold to a Fiduciary Trust. This breakdown the portion of the mortgage payments that are interest and the pay-out to the investors. There is also a rate allotted for conditional prepayments (CPR) by the originator for the debt. The Sequential-Pay CMO tab models the pay-out to the investors as bonds. This is modeled for four tranches with varied portions of the debt, coupon rates, and different pay-out schedules depending on the risk. Lastly, the Debt Security Valuation is a DCF valuation based on the estimated cash flow of the pay-out to investors. This is set to calculate cash flows for Tranche A by default.
All cells in black font are input cells where custom information can be entered. All cells in blue font are formulas set to streamline the model.