Mortgage Portfolio Model
Capital Markets Financial Model (Free Excel Download)
Project mortgage balances, prepayments, delinquencies, credit migration, interest income, and yield-curve sensitivity across a loan portfolio.
professionals from Deloitte
Used by professionals from
About this model
A Mortgage Portfolio Model projects cash flows and financial metrics for a closed pool of residential mortgage loans acquired at par or discount, modeling prepayment speed (CPR - conditional prepayment rate), default rates (CDR - cumulative default rate by FICO and vintage), and loss severity (LGD - loss given default) to forecast principal and interest cash flows. A typical portfolio of $500M–$5B notional with weighted-average coupon (WAC) of 4.5% funded at 3.5% cost generates 100 bps net interest margin (NIM). Prepayment speeds vary with interest rates: when rates fall, borrowers refinance (CPR increases to 20-40% annually); when rates rise, CPR declines to 5-10% annually. Default curves peak in Year 3-4 after origination (6-12% SMM in distressed vintages, <1% for prime); recovery rates range 50-75% for first liens.
The Portfolio_Rollforward sheet models monthly (or annual for simplicity) vintage segments, each with opening balance, interest income, scheduled principal, prepayments (driven by refi incentive, proxy via CPR input curves), defaults, and recoveries (lagged 12-18 months). Cash flows segregate into principal receipts and interest receipts; both decline over time as the portfolio runs off. Price sensitivity calculations (duration and convexity) measure how portfolio value changes with interest rate shocks: +100 bp shock typically reduces portfolio duration-adjusted value by 2-4%, with convexity dampening the decline in large moves. The model tracks weighted-average life (WAL, years until 50% of principal repaid) and effective duration (years of interest rate sensitivity). Key outputs include portfolio yield (IRR on cash flows), option-adjusted spread (OAS, accounting for refinancing optionality), and price impact from Fed rate changes.
This model applies to mortgage REIT investors, MBS traders, portfolio managers, and fixed-income analysts managing single-family rental (SFR) mortgage pools, jumbo mortgage portfolios, or agency pass-through securities. Typical portfolio yields are 4-6% depending on WAC and expected prepayments; duration ranges 3-8 years. Sensitivities to refunding rates (Fed policy), home price appreciation (affects default), and economic cycles (unemployment, affecting prepayment behavior) are acute. MBS valuations and trading decisions hinge on CPR and default assumptions - 1% change in assumed CPR can swing portfolio duration by 1-2 years.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Mortgage Portfolio Model
- Dynamic amortisation for fixed and adjustable rate products
- Prepayment modelling using CPR, SMM, and PSA curves
- Credit loss provisioning with PD, LGD, and EAD inputs
- Net interest margin and spread analysis
- Interest rate sensitivity and duration metrics
- Mortgage portfolio composition by loan age, rate, and FICO cohort
- Conditional prepayment rate (CPR) and single monthly mortality (SMM) curves
- Default rates and loss severity by vintage
Mortgage Portfolio Model: How the Template Captures Loan-Level Economics
This mortgage portfolio model template provides a structured framework for analysing loan-level cash flows, prepayment behaviour, and yield curve sensitivity. It is designed for lenders, asset managers, and structured finance teams evaluating a US residential mortgage banking operation.
The model integrates origination, servicing, hedging, and financial reporting into a single coherent workbook.
Operating Drivers and Scenario Toggle
The Assumptions sheet consolidates approximately 80 named inputs across volume, pricing, product mix, credit, channels, pipeline, servicing, MSR, costs, warehouse, balance sheet, tax, and covenants.
- A single scenario toggle, Scenario_Index, selects Bull/Base/Bear columns for scenario-aware rows, covering loan volume, growth rate, GOS margin, CPR, and hedge cost.
- This centralised approach allows users to switch scenarios with one input and observe the impact across all schedules.
- The model assumes a greenfield start with opening equity, zero retained earnings, and zero unpaid principal balance.
Origination, Pricing, and Pipeline Hedging
Origination_Build drives funded volume, split by loan purpose, channel, and product. It decomposes the blended gain-on-sale rate into a base margin plus purpose premium, product premium, and FICO band adjustment, less hedge cost and fallout cost.
- This yields gain-on-sale revenue, channel commissions, processing cost, and contribution margin. Pipeline_Hedge calculates locked volume, average open pipeline, fallout volume, TBA hedge notional, hedge cost, and hedge profit and loss.
- These interconnected schedules show how pricing decisions and hedging activity affect near-term profitability.
Servicing Portfolio and MSR Roll-Forward
Servicing_Portfolio tracks the unpaid principal balance roll from opening balance plus additions minus prepayments and defaults to closing. It calculates servicing fee income, late fees, escrow float income, subservicing cost, and servicing losses.
- The MSR roll-forward captures additions less runoff. MSR_Sensitivity evaluates MSR carrying value under -100 and +100 basis point rate shocks using separate multiples.
- This section illustrates how prepayment behaviour and credit events feed through to servicing revenue and the value of mortgage servicing rights, which are capitalised as assets.
Financial Statements and Practical Use
Income_Statement integrates GAAP ASC 860 linkage: MSR capitalisation income and MSR amortisation expense ensure balance sheet reconciliation alongside servicing fee, late fees, escrow float, net interest income, variable costs, operating expenses, warehouse interest, MSR-financing interest, servicing-advance financing interest, and tax.
- Balance_Sheet lists cash, loans held for sale, MSR, servicing advances, PP&E, and various financing and accrual liabilities.
- Cash_Flow begins with net income and adds back non-cash items such as depreciation, MSR capitalisation reversal, MSR amortisation, and R&W reserve build, then reflects working-capital changes, capex, and financing flows.
- Ratios_Covenants computes operating metrics and covenant checks on debt to tangible net worth, leverage, liquidity, GNMA TNW, and warehouse utilisation.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a mortgage portfolio model?+
It is a model used to analyse cash flows, credit risk, and interest rate sensitivity across a pool of residential or commercial mortgage loans.
What should a mortgage portfolio model include?+
It should include amortisation schedules, prepayment assumptions, credit loss forecasting, NIM analysis, and duration or convexity metrics.
Who uses mortgage portfolio models?+
Mortgage lenders, hedge funds, asset managers, bank treasury teams, and structured finance analysts use them for valuation, risk management, and regulatory reporting.
How is prepayment risk modelled?+
Prepayment is typically modelled using CPR or PSA curves that estimate the rate at which borrowers pay off their loans early, which affects cash flow timing and yield.
Does it support both fixed and adjustable rate loans?+
Yes. The model handles standard fixed-rate mortgages as well as adjustable-rate products with configurable reset periods, caps, and index margins.
Have more financial modelling questions? Contact us
Related templates
Loan Portfolio CDR Model
Credit default risk model for loan portfolios with cumulative default rates and loss severity analysis.
Fixed Income Portfolio Model
Bond portfolio analytics with duration, convexity, credit spreads, and yield curve positioning.
Credit Portfolio CDO Model
Model credit default swap portfolio, tranching, and waterfall for CDO securitization.
DCF Model
Discounted cash flow valuation model with explicit forecast, terminal value, and WACC.
