The Mortgage-Backed Securitization is the process of packaging mortgages 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 mortgage-backed securitization process from the initial debt to the bonds sold to investors with regards to a property development project. 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
• Deal Summary
• Monthly Cash Flows through the purchase of the land, construction, and sale of property
• Cap Rate & Net Operating Income
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.
The model continues into the Real Estate Investment portion with the Cap Rate & NOI. This tab offers net operating income and valuation for up to 5 different floor plans/rental properties along with sensitivity analysis and scatters plot for Cap Rate and Rental Income. The Monthly Cash Flows is a proforma with projecting the revenue build-up of the project from pre-construction to after the sale of the properties. This also includes important aspects such as development costs, financing, levered free cash flow, and a cash flow waterfall for investors. The Deal Summary tab provides a snapshot of the project’s financials. Details of the project such as the construction schedule, development costs, and finance terms are entered in this tab and flow into the Monthly Cash Flows tab.
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.
Important: This file comes as .xlsx file type and you need to allow iterative calculations in excel in order to use this model.
