The Quantinal CMBS Loan Pricing and Hedging Model

Calculates required spread for a new CMBS loan origination given yields, spread, subordination levels, and desired average lives for the first three AAA bonds. Automatically creates and structures a CMBS and then solves for spread given a desired profit percentage.
Creates a rate sheet matrix for new originations for different 7 different LTV’s, 6 different property types, and 3 different desired profit percents.
For a portfolio of loans originated but not yet securitized, it uses updated rates and spreads to reprice each loan to determine how the hedge is performing. It also takes the portfolio of loans and calculates the weighted average subordination levels, structures a CMBS, and then prices that CMBS execution assuming treasuries move up and down 100 bp’s in 25 bp intervals to calculate an effective duration and facilitate a proper hedging strategy.
Creates stratifications on the portfolio - either the existing portfolio to securitize or an existing CMBS deal that was input into the model.
Generates monthly and annual cash flows under various Default / Prepay scenarios on either the existing portfolio to securitize or an existing CMBS deal that was input into the model.
Calculates yield and average life for every bond-class for 25 different default scenarios: 5 different CDR’s and 5 different loss severities (assuming no lag until recovery).
Prices an actual deal at origination. It can price a new deal and also is capable of pricing the pools of loans of different contributors to allocate proceeds.

Making the world a better place... one spreadsheet at a time!