CMBS/ABS Securitization Excel Model

Accounting, Asset Management, Banking, Business Consulting Services, Financial Model, Financial Services, Loans & Lending Businesses, Microfinance

10-year Financial Projections, 5-year Financial Projections, Amortization, Bonds, Break-Even, Budget, Cash Flow Analysis, Cash Flow Projections, Dashboard, Debt Amortization, Debt Schedule, Debt Security, Debt Service Coverage, Financial Analysis, Financial Debt, Financial Due Diligence, Financial Feasibility, Financial Model, Financial Modeling, Financial Projections, Financial Ratios, Financing, Forecast, Forecasting, Gross Yield, Loans, Monthly Recurring Revenue (MRR), Net Yield, NPV (Net Present Value), Pro-Forma, Sensitivity Analysis, Uses and Sources of Funds, Valuation, WACC (Weighted Average Cost of Capital)

The CMBS/ABS Static Securitization Excel Model is a powerful tool designed to simplify the complex process of asset-backed static securitization for issuers. With its intuitive and comprehensive approach, this model enables users to visualize the entire lifecycle of a vehicle, making it easy to adjust pricing and bond structure with just a few toggles. With the ability to evaluate up to 50 loans at once, users can gain insights into geographic and property type diversification, ensuring profitable execution and debt coverage for up to 10 years. The model’s advanced features, including the “Structuring Tool” and detailed cash flow tabs, provide users with a comprehensive understanding of bond payments, paydown schedules, and monthly/annual cash flows. Empower your securitization process with accuracy, efficiency, and precision.

Instructions:

**IMPORTANT** – ONLY CHANGE THE GREY CELLS WITH BLUE FONT. CHANGING ANY OTHER CELLS MAY CAUSE A BREAK IN THE MODEL

Tab 1: Assumptions

Loan Assumptions

In this box you can input the general assumptions for all of the loans to be sold into the deal.  This includes the assumed cut-off date, the set dates the loans pay and the interest accrual method of the underlying loans.  Industry practice is for these inputs to be standardized across the entire loan pool. 

Bond Assumptions

This box allows you to toggle the assumptions for the bonds to be issued by the trust. You can adjust the 1st pay date of the bonds, the % of risk retention, interest accrual (bonds only), and the servicing fees. 

Checks

This box is not an input section. These tabs ensure there’s enough payments from the underlying loans in the trust to cover the payments on the bonds. If you see FALSE in any of the three, you’ll need to change the assumptions of your pool to make sure all three read TRUE.

Capital Stack

This box allows you to toggle the assumptions for the full potential bond stack.  I recommend first utilizing the “Structuring Tool” tab to try and select appropriate tranche sizes (see more below in the Structuring Tool section). The first two columns are descriptive and do not impact the model. you can input the total tranche size, coupon rate, yield-to-maturity and the coupon type. Please note, the price of the tranche is a factor of the yield and coupon inputs and should not be adjusted. 

If you change a tranche to a “WAC” you will then have to manually set the corresponding coupon rate (column E) to equal cell M9 on the Loan Cash Flows tab.

Sources and Uses

This box summarizes the sources and uses of the transaction. The input cells here allow you to toggle fees and other miscellaneous closing costs (i.e. bankers, legal, third parties, etc.)

Tab 2: Loan Tape

This is the second and final assumption tab. Again, please only change the grey cells with blue font.

Loan Name

This cell is solely descriptive and used to identify each individual loan. You can also use a number if that is your preference. Changing these cells will not impact the model.

Funding Date

This is the date the loan was originally funded. This is used to calculate Loan Seasoning, Cut-Off WAL, Maturity Date, and Amort Start Date.

Cut-Off Balance

This is the final balance of the loan prior to it being sold into the trust. Please note, if you’re contemplating a sale several months out you will need to factor the amortization for each loan and input the future balance prior to the Cut-Off Date in this column.

Rate

This is the coupon rate of the loan.

Term

This is the original term of the loan at origination in years.

IO Period

This is the number of years of interest only before the loan begins to amortize. If the loan is fully interest only, this column should equal column H.  

Amort Type

This is the number of years of amortization of the loan in years.

Current LTV

This cell should reflect the LTV of the loan at the Cut-Off Date. Please note, this cell does not impact the cash flows in the model but rather the outputs in the following “Pool Strats” tab.

Property Type

This cell should reflect the property type of the underlying property collateralizing the loan. Please note, this cell does not impact the cash flows in the model but rather the outputs in the following “Pool Strats” tab.

State

This cell should reflect the state of the underlying property collateralizing the loan. Please note, this cell does not impact the cash flows in the model but rather the outputs in the following “Pool Strats” tab.

Tab 3: Pool Strats

This tab is solely descriptive outputs, ideal for a pitch deck or marketing materials. Please note NO cells on this tab are inputs and it should not be touched.

Tab 4: Annual Cash Flows

This tab reflects the annual roll up of the deals total cash flows. It is split by Loan Cash Flows and Bond Cash Flows.

Tab 5: Monthly Cash Flows

This tab reflects the monthly roll up of the deals total cash flows. It is split by Loan Cash Flows and Bond Cash Flows.

Tab 6: Loan Cash Flows

This tab shows you the cash flows for each individual loan until maturity. There are only two cells of potential input on this tab (G9 and H9). These cells are for any additional carrying costs that may be incurred across the loan portfolio (i.e. broker fees, additional servicing fees, etc.)

Tab 7: Structuring Tool

Please note, this tab is a tool and does notimpact the model. This tab is a sizing tool where you can toggle which principal payments you’d like to flow to each bond. You can toggle either Amort Pmts, Balloon Pmts or Both over a set time frame. You can toggle the start and end months in column D and E.