# Mortgage Portfolio Model Build a mortgage portfolio model for loan-level cash flow analysis, prepayment behaviour, credit migration, and yield curve sensitivity. Designed for lenders, asset managers, and structured finance teams. - Canonical: https://finamodel.com/templates/mortgage-portfolio-model - Excel download: https://finamodel.com/templates/mortgage-portfolio.xlsx - Category: Capital Markets - Model type: Portfolio - Difficulty: Advanced - Audiences: Credit & risk, Fund managers, Mortgage Servicers, Mortgage Investors, Fixed Income Analysts, Risk Managers - Tags: mortgage, prepayment, cpr, duration, rate-risk ## Overview 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's included - 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 - Monthly cash flow projections including principal and interest - Duration and convexity calculations - Price sensitivity to interest rate changes ## 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. ## Built for portfolio-level analysis Use this model when you need to value mortgage assets, stress test credit performance, or assess rate sensitivity across a pool of loans. ## Useful for acquisition and securitisation work A mortgage portfolio model helps both buyers and originators understand cash flow timing, prepayment risk, and credit exposure before committing capital. ## Better for institutional mortgage analytics This gives you a structured framework for portfolio valuation instead of relying on single-loan amortisation tables that miss prepayment and credit dynamics. ## Built for portfolio-level analysis Use this model when you need to value mortgage assets, stress test credit performance, or assess rate sensitivity across a pool of loans. ## Useful for acquisition and securitisation work A mortgage portfolio model helps both buyers and originators understand cash flow timing, prepayment risk, and credit exposure before committing capital. ## Better for institutional mortgage analytics This gives you a structured framework for portfolio valuation instead of relying on single-loan amortisation tables that miss prepayment and credit dynamics. ## Features - **Rate and FICO segmentation:** Model prepayment behavior separately for high-FICO and low-FICO borrowers, and by coupon relative to current rates. - **Refinancing incentives:** Model prepayments as a function of refinancing incentive (current rate vs. mortgage coupon), capturing the S-curve acceleration in fast markets. - **Interest rate sensitivity:** Calculate effective duration, convexity, and price impact of rate changes to forecast portfolio value and reinvestment risk. ## Use cases - **Portfolio valuation and yield analysis:** Calculate market value, yield-to-maturity, and effective duration for investor reporting and risk management. - **Interest rate risk hedging:** Use duration and convexity to calculate hedge ratios and model portfolio protection strategies. - **MBS structuring and tranching:** Model cash flows to different tranches (senior, mezzanine, equity) to set subordination levels and tranche yields. ## Frequently asked questions ### 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. ## Related templates - [Loan Portfolio CDR Model](https://finamodel.com/templates/loan-portfolio-cdr-model) - [Fixed Income Portfolio Model](https://finamodel.com/templates/fixed-income-portfolio-model) - [Credit Portfolio CDO Model](https://finamodel.com/templates/credit-portfolio-cdo-model)