CMBS & ABS Financial Model | Asset-Backed Securities Excel Template

This CMBS financial model in Excel is a complete, formula-driven commercial mortgage-backed securities template built for structurers, credit analysts, B-piece buyers, bond investors, lenders and asset managers. It models a 50-loan conduit pool financing office, retail, multifamily, industrial, hotel, healthcare, self storage and mixed-use properties, and carries every loan through to bondholder cash flows. The model covers 3 years of monthly historical data and a 20-year monthly forecast with annual roll-up columns, all in USD and with real calendar years (2027, 2028, 2029 and so on) across a horizontal timeline.

Built across 20 fully linked tabs, the model runs a 3-year historical period and a 20-year, 240-month monthly forecast with annual roll-up columns, all denominated in USD and displayed on true calendar-year timelines. A single dynamic date engine drives every date in the workbook — change the deal’s start date once on the Assumptions tab and every schedule, from the 50-loan collateral pool through the bond-level cash flow waterfall, updates instantly and automatically.

Whether you’re evaluating a CMBS conduit deal, structuring an ABS waterfall, stress-testing a securitization under different CPR/CDR/loss-severity scenarios, or teaching structured finance modeling, this Excel-based CMBS/ABS model gives you a transparent, fully formula-driven build with no hidden macros or black boxes. A dynamic scenario manager runs Base, Upside, Downside, Severe Stress and a fully customisable stress case through the whole model. Sensitivity tables, a tornado chart, bond yield, IRR, WAL and duration analytics, REMIC tax and accounting statements, interest rate cap and swap hedging, a defeasance schedule and more than 70 automatic integrity checks round out a lender-ready, investor-grade structured finance model.

Why Buy This Model

If you need a credible, audit-ready CMBS/ABS model fast, building one in-house from a blank workbook can take days or weeks — and still leave gaps in the waterfall logic, loss allocation, or date-handling that only surface under stress testing. This model gives you a complete, internally consistent structure out of the box: a 50-loan collateral pool, full loan-level amortization, delinquency and special servicing mechanics, an 8-class sequential-pay waterfall with reverse-sequential loss allocation, bond-level returns analytics (yield, WAL, duration, credit enhancement), and 11 built-in integrity checks that reconcile every dollar from the collateral pool to the bond classes. It’s built to be extended — swap in your own loan tape, adjust the capital structure, or flex the scenario assumptions — without breaking a single formula.

CMBS & ABS Model Tab-by-Tab Breakdown

01. Cover Contents

The Cover Contents tab is the control centre of this CMBS model. It holds the full transaction identity, a live transaction overview and the model structure summary. It also contains the master Projection Start Date that re-dates the entire workbook, plus clickable navigation links to all 23 tabs for fast, professional review.

  • Single master date input that re-dates every tab automatically
  • Live deal overview: collateral balance, WA coupon, LTV, DSCR, debt yield, WAL and deal yield
  • Transaction parties, key dates, legal final and expected maturity
  • Hyperlinked navigation and colour-coding legend

02. Assumptions

The Assumptions tab centralises every driver of the CMBS cash flow model: general, interest rate, property, credit, prepayment, loan, capital structure, transaction fee and scenario inputs. Clearly colour-coded inputs, an editable default timing curve, risk-grade multipliers and a SOFR forward curve make the model transparent and easy to customise.

  • Over 150 labelled, documented inputs with blue/yellow input cells
  • SOFR forward curve, default curve (front, base or back-loaded) and growth indices
  • Capital structure percentages for Classes A-1 to E, X-1 notional and residual
  • Trigger thresholds, payment mode, reserve and liquidity settings
CMBS & ABS Financial Model | Asset-Backed Securities Excel Template
CMBS & ABS Financial Model | Asset-Backed Securities Excel Template

CMBS & ABS Revenues & Schedules

03. Collateral

The Collateral tab is a detailed 50-loan CMBS loan tape covering identification, property classification, origination, loan economics, collateral metrics, loan characteristics, property characteristics, historical performance and risk classification. It also includes a major-tenant lease rollover schedule and a lease expiry summary by year for credit analysis.

  • 129 loan-level fields: balance, coupon, IO period, amortisation, maturity, LTV, DSCR and debt yield
  • Fixed and SOFR floating loans with floors, caps and extension options
  • Lease rollover and expiry schedule with renewal probability, TI and leasing commissions
  • Internal risk grades, shadow ratings and concentration flags

04. Property CF

The Property CF tab builds pool-level commercial real estate cash flow, from gross potential rent through vacancy, credit loss, operating expenses, NOI, capital expenditure and net property cash flow to property valuation. Historical actuals sit alongside a 20-year forecast driven by rent, expense, tax and insurance growth indices.

  • Revenue, vacancy and credit loss, 13 operating expense lines, NOI and EBITDA
  • Capex, tenant improvements, leasing commissions and replacement reserves
  • Property valuation, cap rate, haircut and stressed value
  • Lease rollover forecast with downtime, reletting and leasing costs

05. Property Revenue

The Property Revenue tab breaks rental income down by property type, including office, retail, industrial, multifamily, hotel, healthcare, self storage, mixed use, student housing and entertainment. It adds sector-specific revenue analytics, ancillary income, tenant reimbursements and revenue adjustments that reconcile back to effective gross revenue.

  • Rental revenue by property type using underwritten tape weights
  • Hotel, retail, office, industrial and healthcare revenue analytics
  • Parking, telecom, signage, CAM and tax recoveries
  • Editable split assumptions with reconciliation checks

06. Loan Schedule

The Loan Schedule tab is the loan-by-loan amortisation engine of this CMBS model. Each of the 50 loans calculates its own monthly rate, interest, scheduled principal, defaults, prepayments, balloon repayment, extension and maturity default. These roll up into pool balances, interest income, fees and performance metrics.

  • Individual monthly cash flow schedule for all 50 commercial mortgage loans
  • SOFR plus margin with floor and cap, or fixed coupon, on 30/360 or Actual/360
  • Yield maintenance, prepayment premiums, extension and exit fees
  • Pool DSCR, LTV, debt yield and loan balance roll-forward

07. Delinquencies Defaults

The Delinquencies Defaults tab models 30, 60, 90, 120, 150 and 180-day delinquency buckets, roll rates, default balances and recoveries. Delinquencies are back-solved from projected defaults, so the pipeline always reconciles. Loan-level Appraisal Reduction Event (ARE) and Appraisal Reduction Amount (ARA) tables stress every loan.

  • Delinquency buckets and roll rates that tie exactly to defaults
  • CDR, MDR, cumulative default rate and defeasance-adjusted default rate
  • Default severity, recovery rate and realised loss analysis
  • Loan-level ARE, ARA calculation and ARA status tracking tables

08. Prepayments

The Prepayments tab analyses voluntary prepayments, CPR and SMM, partial versus full prepayments, refinancing, balloon repayments and prepayment income such as yield maintenance and defeasance fees. Timing analytics and alternative CPR scenarios show how prepayment speed affects pool cash flow.

  • Assumed versus realised CPR and SMM
  • Prepayment types, eligible balance and cumulative prepayments
  • Yield maintenance, prepayment premium and exit fee income
  • Low, base, high, zero and stressed CPR scenarios

09. Spec Servicing Recoveries

The Special Servicing Recoveries tab tracks special servicing transfers, loan workouts, modifications, foreclosure, REO management, liquidation proceeds, recoveries and losses. It also includes special servicing, workout and liquidation fees, projected pool ARA, and loan-level ARA review, allocation and resolution histories.

  • Special servicing balance roll-forward and status mix
  • REO income and expenses, and liquidation costs and proceeds
  • Net recoveries after fees and advance reimbursement
  • Loss severity, cumulative losses and ARA application tables

10. Collateral Cash Flow

The Collateral Cash Flow tab aggregates every source of cash available to the trust: loan interest, servicer P&I advances, scheduled and unscheduled principal, borrower fees, recoveries and other income. A collateral balance roll-forward and pool factor confirm that collateral cash flows reconcile period by period.

  • Total interest, principal, fee and recovery collections
  • Interest shortfalls, unpaid interest and servicer advances
  • REO income and ancillary loan income
  • Collateral balance roll-forward, check row and pool factor
CMBS & ABS Collateral Loan Financial Model
CMBS & ABS Property Cash Flow Template
CMBS & ABS Property Revenue Template

CMBS & ABS Cash Flow & Expenses

11. Fees Expenses

The Fees Expenses tab calculates every CMBS transaction cost from an editable fee schedule. This covers master, primary and special servicing, trustee, custodian, paying agent, legal, audit, rating agency, data reporting and financing fees. It also includes loan-level workout and liquidation fees and Class X-1 IO strip fee reconciliations.

  • Editable fee schedule: annual rate on balance plus fixed annual amount
  • Servicing, trust, professional, transaction and financing fees
  • Workout and liquidation fee table for all 50 loans
  • Notional / IO strip fee records and reconciliation

12. Credit Enhance Insurance

The Credit Enhancement Insurance tab tracks subordination, overcollateralization, reserve accounts, the liquidity facility, insurance, guarantees and servicer advances. It shows exactly how much credit support protects the senior bonds each month, and how reserves and advances are drawn and replenished.

  • Senior and junior subordination, OC and current credit enhancement
  • Reserve account with required balance, deposits, draws and releases
  • Liquidity facility drawdowns, repayments, interest and commitment fees
  • Interest and property protection advances with reimbursement

13. Investment & Other Income

The Investment & Other Income tab calculates interest earned on reserve, collection and distribution accounts, reinvestment income and net hedge receipts. It shows the cash account balances and cash management flows that feed the waterfall, so no source of transaction income is missed.

  • Reserve, collection float and distribution account income
  • Reinvestment and permitted investment income
  • Net interest rate cap and swap receipts
  • Cash management and income summary

14. Triggers Tests

The Triggers Tests tab runs the CMBS performance tests: overcollateralization, interest coverage, delinquency, cumulative default and loss, collateral performance (DSCR, LTV and debt yield) and concentration. Paydown triggers decide whether principal pays sequentially or pro-rata, with optional sticky or curable triggers.

  • OC and IC tests with PASS / FAIL results
  • 60+ delinquency, cumulative loss, special servicing and ARA triggers
  • Single-borrower, geographic, property-type and maturity concentration tests
  • Trigger activation and cure dates and event-of-default flag

15. Waterfall

The Waterfall tab is the CMBS priority of payments. Cash available pays senior fees and advance reimbursements, then interest on Classes A-1 to E and X-1, then principal (sequential or pro-rata), then losses reverse-sequentially, then reserve movements and residual distributions. Every dollar reconciles with zero unallocated cash.

  • Pro-rata senior interest, then sequential mezzanine and subordinate interest
  • Sequential and pro-rata principal allocation with lockout and triggers
  • Reverse-sequential loss allocation
  • Class X-1 notional reduction rules and IO cash flow

16. Tranche Schedule

The Tranche Schedule tab holds the class terms and monthly roll-forward for every bond class: beginning balance, coupon, interest due and paid, principal, write-downs, shortfalls and ending balance. It also covers the Class X-1 notional and IO interest schedules, the residual class and the trigger and lockout history.

  • Editable tranche terms: size, coupon, issue price and rating
  • Monthly schedules for Classes A-1, A-2, A-3, A-M, B, C, D and E
  • Class X-1 notional balance and IO interest schedule
  • Credit metrics, paydown status by tranche and trigger history
CMBS & ABS Loan Schedule Template
CMBS & ABS Credit Template

CMBS & ABS Bonds & Returns

17. Bond CF

The Bond CF tab presents the investor’s view of each CMBS bond: identification, opening balances, coupon, accrued and paid interest, scheduled versus unscheduled principal, write-downs and total cash received. Bond performance metrics link to the returns analytics for a complete bond cash flow report.

  • Bond identification for all classes
  • Interest and principal cash flows by class
  • Shortfalls, write-downs and ending balances
  • Yield, price, discount margin, WAL and duration summary

18. Returns Analytics

The Returns Analytics tab calculates bond pricing, yield to maturity, yield to call, IRR, weighted average life, Macaulay and modified duration, convexity and expected loss for every class. It also reports deal-level IRR, cost of funds, collateral performance and credit performance from a monthly cash flow matrix.

  • Yield, IRR, discount margin and spread to benchmark per class
  • WAL, Macaulay and modified duration, and convexity
  • Residual (equity) IRR and multiple of investment
  • Pool WAL, pool factor, cumulative loss and recovery metrics

19. Scenarios Sensitivity

The Scenarios Sensitivity tab is a dynamic scenario manager. Choose Base, Upside, Downside, Severe Stress or a Custom Scenario to re-run the entire CMBS model. Custom stress menus cover interest rates, defaults, recoveries, prepayments, property values, loans and the waterfall, and are supported by sensitivity tables and a tornado chart.

  • One-click scenario switching across the whole model
  • Custom stress menus such as “Rates +200 bps” and “Pro Rata Principal Payments”
  • 8 two-way sensitivity tables (for example default rate versus recovery rate)
  • Tornado chart and scenario output comparison

20. Checks Outputs

The Checks Outputs tab contains over 70 automatic integrity checks across collateral, property, loans, credit, cash flow, waterfall, tranches, capital structure and returns. It shows a PASS / REVIEW status each period, an overall model validation and a headline output dashboard ready for investor presentations.

  • More than 70 reconciliation checks with $1 tolerance
  • Period-by-period PASS / REVIEW status and overall model validation
  • Formula error, missing input and balance sheet checks
  • Output dashboard of key collateral, credit and bond metrics

21. Tax Accounting REMIC

The Tax Accounting REMIC tab covers REMIC compliance and asset eligibility, REMIC income and expenses, prohibited transactions, original issue discount (OID), tax basis, withholding taxes and investor tax reporting. It also contains a trust income statement, a balance sheet that balances, a cash flow statement and CECL / IFRS 9-style impairment staging.

  • REMIC qualification, asset, contribution and distribution tests
  • OID accrual, tax basis and gain and loss recognition
  • Income statement, balance sheet and cash flow statement
  • Stage 1, 2 and 3 expected credit loss provisioning

22. Hedges Caps & Swaps

The Hedges Caps & Swaps tab models an interest rate cap and a receive-fixed interest rate swap on the floating-rate loans. It covers settlements, fair value and mark-to-market, hedge coverage, rate sensitivity, counterparty credit risk, collateral and margin, downgrade provisions, termination and hedge accounting.

  • Cap settlements against a SOFR strike, and net swap settlements
  • Fair value and mark-to-market from the forward curve
  • Counterparty exposure, potential future exposure and downgrade triggers
  • Hedge coverage ratio and ±50/100/200 bps rate sensitivity

23. Defeasance Portfolio

The Defeasance Portfolio tab models CMBS loan defeasance: eligibility after the REMIC lockout, the defeased balance roll-forward, replacement Treasury collateral, cash flow matching, defeasance funding and cost, expenses, premiums and accounting. Defeased loans stay in the pool but no longer carry default risk.

  • Loan-level defeasance eligibility and indicative defeasance cost
  • Replacement Treasury collateral and cash flow matching
  • Defeasance funding, expenses, fees and premiums
  • Defeasance scenarios and portfolio outputs

CMBS & ABS Frequently Asked Questions (Faq)

Q: I’m not a CMBS specialist. Is this model easy to use?

A: Yes. Every editable input is shown in blue text on a yellow background, formulas are black and links are green. Each tab has a one-line summary at the top explaining what it does. Most users only need to update the loan tape on 03. Collateral and the key assumptions on 02. Assumptions.

Q: Can I use my own loan pool instead of the sample loans?

A: Absolutely. The 50-loan tape is illustrative. Overwrite the blue input fields with your own loan balances, coupons, terms, property values and NOI, and the loan schedules, waterfall, tranches and returns all recalculate automatically.

Q: How do I change the start date of the model?

A: Change the Projection Start Date on 01. Cover Contents. Every timeline, year header, loan date, lease expiry, hedge date and defeasance date updates automatically. No manual re-dating is needed.

Q: Does the model handle both fixed-rate and floating-rate loans?

A: Yes. Each loan can be fixed rate or SOFR plus a margin, with an interest rate floor and cap, on a 30/360 or Actual/360 basis. Floating-rate interest follows a projected SOFR forward curve, which you can shift for rate stress tests.

Q: How many tranches does the waterfall include, and can I change them?

A: There are eight principal classes (A-1, A-2, A-3, A-M, B, C, D and E), an interest-only Class X-1 and a residual. You can change each class’s size, coupon, issue price and rating in the inputs, and the subordination, credit enhancement and returns update instantly.

Q: Does it support both sequential and pro-rata principal payments?

A: Yes. Principal can pay sequentially or pro-rata, with a configurable pro-rata lockout. Delinquency, cumulative loss, special servicing, ARA, OC and interest coverage triggers can force sequential pay, and you can choose whether triggers are curable or sticky.

Q: Can I run stress tests and downside scenarios?

A: Yes. One dropdown switches the whole model between Base, Upside, Downside, Severe Stress and a Custom Scenario. The custom menus let you shock interest rates, default rates and timing, recovery rates and timing, prepayment speeds, NOI, property values, maturity defaults and the payment mode.

Q: What return metrics does the model produce?

A: For every bond class it calculates yield to maturity, yield to call, IRR, discount margin, spread to benchmark, weighted average life, Macaulay and modified duration, convexity, expected loss and multiple of investment. It also produces the residual (equity) IRR and the weighted deal cost of funds.

Q: Does the model include Appraisal Reduction Amounts and special servicing?

A: Yes. It projects pool-level ARA, which reduces servicer interest advances, and it includes loan-level ARE, ARA calculation, allocation and resolution tables. Special servicing transfers, workouts, REO, liquidations, workout fees and liquidation fees are all modelled.

Q: Is there any tax and accounting coverage?

A: Yes. The REMIC tab covers qualification and asset tests, REMIC income and expenses, OID accrual, tax basis, withholding taxes and investor tax reporting. It also includes a trust income statement, a balance sheet that balances every month, a cash flow statement and expected credit loss provisioning.

Q: How do I know the model is accurate?

A: The Checks Outputs tab runs more than 70 automatic reconciliations. These cover loan balance roll-forwards, collateral cash, waterfall allocation, tranche balances, lifetime principal conservation and the balance sheet. The model shows PASS when everything ties, and it passes in every built-in scenario.

Q: Does it use macros or VBA?

A: No. The model is 100% Excel formulas with no macros, no circular references and no iterative calculation, so it is transparent, auditable and safe to share.

Q: Which version of Excel do I need?

A: Any modern desktop version of Microsoft Excel (Excel 2016, 2019, 2021 or Microsoft 365). A small number of functions, such as MINIFS and MAXIFS, need Excel 2019 or later.

Q: Can I use this model for presentations to lenders or investors?

A: Yes. It follows professional formatting conventions with colour-coded inputs, summary bands, section headers, total rows and an output dashboard. That makes it suitable for credit committee packs, investor meetings and training. Please verify all inputs for your specific transaction.

CMBS & ABS Financial Model Excel Template
CMBS & ABS Waterfall Template
CMBS & ABS Tranche Schedule Template

Further Reading

Final Notes on the 20 Year Financial Model Integration

  • Interdependencies: Cash flow projections directly influence the income statement and balance sheet through interest income, reserves, and tranche payments.
  • Scenario Analysis: Embedded functionality to test various market assumptions like property value depreciation, default rates, and interest rate spikes.
  • Investor Reporting: Schedules for interest/principal payouts and performance updates tailored to tranche types.

Combined CMBS ABS Finance Model

This structured model helps CMBS and ABS Investors have a broad market spectrum, offering the right balance between cost, and investment support.