MBS Trading Strategy Analysis
Analyze mortgage-backed securities with Sourcetable AI. Calculate yields, prepayment speeds, duration, and portfolio risk automatically—no complex formulas needed.
Andrew Grosser
February 24, 2026 • 15 min read
Introduction to MBS Trading Strategy
June 2022: 30-year mortgage rates hit 5.8% - up from 3.1% six months earlier. Prepayment speeds on 3% MBS pools collapse from 35% CPR to 4% CPR. Duration extends from 6 years to 12 years overnight. Mortgage-backed securities (MBS) trading represents one of the most complex yet lucrative segments of fixed income markets. These securities, backed by pools of residential or commercial mortgages, offer attractive yields while introducing unique risks like prepayment uncertainty and negative convexity. For institutional investors, hedge funds, and portfolio managers, successful MBS trading requires analyzing dozens of variables simultaneously—prepayment speeds, interest rate sensitivity, credit quality, and cash flow projections.
Traditional MBS analysis in Excel demands intricate formulas for calculating weighted average life (WAL), option-adjusted spreads (OAS), effective duration, and prepayment modeling. You're juggling PSA curves, CPR calculations, and scenario analysis across hundreds of securities. A single portfolio review can take hours of manual formula writing and data manipulation sign up free.
Sourcetable transforms this process completely. Upload your MBS portfolio data, import market pricing feeds, and simply ask questions in plain English. 'What's the weighted average coupon across my FNMA pools?' or 'Show me securities with duration under 3 years and yields above 4.5%'—the AI instantly analyzes your data and delivers answers. No VBA macros, no nested INDEX-MATCH formulas, no manual updates. Get started with intelligent MBS analysis at sign up free.
Whether you're trading agency MBS, non-agency RMBS, or CMBS, Sourcetable's AI understands fixed income terminology and automatically handles complex calculations. This guide shows how Sourcetable makes sophisticated MBS analysis accessible to traders at any experience level.
Why Sourcetable Excels at MBS Trading Analysis
Excel and Google Sheets weren't designed for the complexity of mortgage-backed securities. Calculating prepayment speeds requires understanding PSA standards and CPR models. Duration and convexity calculations involve partial derivatives and yield curve scenarios. OAS analysis demands Monte Carlo simulations. Most traders end up with bloated spreadsheets filled with fragile formulas that break when data structures change.
Sourcetable's AI chatbot understands MBS market conventions automatically. Ask 'Calculate the WAC for my Ginnie Mae pools' and it identifies the relevant securities, weights them by balance, and computes the weighted average coupon. Request 'Show prepayment speeds trending above 15 CPR' and it filters your portfolio instantly. The AI knows that FNMA, FHLMC, and GNMA refer to agency issuers. It understands that a 30-year 4.5% coupon MBS trades differently than a 15-year 3.0%.
Real-time data integration sets Sourcetable apart. Connect your Bloomberg terminal feeds, import TBA pricing from dealers, or upload custodian reports. The AI maintains relationships between your holdings and market data automatically. When rates move 25 basis points, you don't manually update hundreds of cells—ask 'How does my portfolio perform if the 10-year rises 50bps?' and Sourcetable runs the scenario analysis instantly.
Visualization happens automatically. Request a chart showing yield versus duration across your portfolio, and Sourcetable generates it immediately. Ask for a prepayment speed comparison between 2023 and 2024 vintages, and you get a clear visual breakdown. Traditional Excel charting requires selecting ranges, choosing chart types, and formatting axes—Sourcetable's AI handles this in seconds based on your natural language request.
For trading desks managing billions in MBS exposure, speed matters. Sourcetable eliminates the friction between question and answer. Your analysts spend time interpreting results and making trading decisions, not debugging VLOOKUP errors or troubleshooting circular references in duration calculations.
Benefits of MBS Trading Analysis with Sourcetable
Sourcetable delivers specific advantages for mortgage-backed securities traders that directly impact portfolio performance and operational efficiency. These benefits translate to faster trade execution, better risk management, and reduced analysis costs.
Instant Prepayment Analysis
Prepayment risk represents the primary challenge in MBS trading. When homeowners refinance or sell properties, your securities pay down faster than expected, forcing reinvestment at potentially lower yields. Sourcetable's AI calculates CPR (Constant Prepayment Rate), PSA speeds, and SMM (Single Monthly Mortality) across your entire portfolio instantly. Ask 'Which securities are prepaying faster than 20 PSA?' and get immediate answers with current outstanding balances and projected cash flows.
The system compares actual prepayment speeds against historical averages and dealer projections automatically. If your FNMA 4.0% pools are prepaying at 18 CPR versus a market average of 12 CPR, Sourcetable flags this immediately. You can drill down by vintage, geography, or loan size without writing a single formula. This speed advantage lets you identify mispriced securities before the broader market reacts.
- CPR (Constant Prepayment Rate): Annualized prepayment speed; 35% CPR means 35% of the remaining pool balance prepays per year; at 35% CPR, a $100M pool loses $35M in principal annually - duration shortens significantly as cash flows accelerate.
- PSA Model: Public Securities Association standard: 100% PSA = CPR ramps from 0.2%/month to 6%/month over 30 months, then stays at 6% (72% CPR); a 3% MBS in 2021 ran at 300-400% PSA as homeowners refinanced into the record-low rates.
- Refinancing Incentive: The primary driver of prepayments; when current mortgage rates fall 100bps below existing note rate, prepayment speeds roughly triple; in June 2022 with rates rising, the refinancing incentive turned deeply negative - prepayments collapsed.
- Burnout: Pools where borrowers have had repeated refinancing opportunities but have not refinanced are burned out - remaining borrowers have poor credit, non-standard situations, or are rate-insensitive; burnout pools show lower prepayment sensitivity to rate changes.
Automated Duration and Convexity Calculations
Effective duration measures how MBS prices change with interest rate movements, but calculating it requires modeling cash flows under multiple rate scenarios. Negative convexity—where MBS lose value faster when rates rise than they gain when rates fall—adds another layer of complexity. Sourcetable's AI handles these calculations automatically using standard fixed income models.
Upload your portfolio and ask 'What's my portfolio duration?' The AI computes weighted average effective duration across all holdings, accounting for embedded prepayment options. Request scenario analysis—'Show P&L if rates rise 100 basis points'—and Sourcetable models the impact including convexity effects. For a $500 million MBS portfolio with 4.2 years duration, that 100bp rate increase translates to approximately $21 million in mark-to-market losses, calculated and visualized in seconds.
- Effective Duration: For a callable instrument like MBS, effective duration = -(DeltaPrice / Price) / Deltar using an OAS model; standard 30-year MBS at 3% coupon had effective duration of 6 years at 3.5% mortgage rates - not the 7.5-year duration of a comparable non-callable bond.
- Negative Convexity: When rates fall, prepayments accelerate and duration shortens; when rates rise, prepayments slow and duration extends; the investor loses in both directions - negative convexity means the MBS underperforms a simple bond in large rate moves.
- OAS (Option-Adjusted Spread): The spread over the risk-free curve after removing the embedded prepayment option value; a 3% MBS at OAS of 50bps means it offers 50bps over Treasuries after adjusting for prepayment risk - this is the fair credit spread.
- Duration Extension in 2022: With mortgage rates rising from 3.1% to 5.8%, effective duration on 3% MBS extended from 6 years to 12 years; a $100M position with 6-year duration lost $6M per 100bp rise; at 12-year duration, it lost $12M per 100bp - a doubling of rate risk.
Real-Time Yield Spread Monitoring
MBS trade on spreads to Treasury benchmarks or swap rates. A FNMA 30-year 4.5% might trade at +145 basis points to the 10-year Treasury. Spread widening indicates cheapening securities (buying opportunities), while tightening suggests richening (potential sells). Sourcetable connects to market data feeds and monitors spreads continuously.
Set up alerts by asking 'Notify me when FNMA 4.5% spreads widen beyond 150bps' or 'Show securities where spreads widened more than 10bps today.' The AI tracks your entire portfolio against current market levels and highlights outliers. When non-agency RMBS spreads blow out during market stress, you see exactly which positions are affected and by how much, enabling rapid portfolio rebalancing decisions.
- MBS Spread to Treasury: Agency MBS typically trades at T+50 to T+150bps depending on coupon, prepayment history, and market conditions; spreads widen during stress (T+200 in 2008) and compress when Fed buys MBS (T+15 during QE).
- Coupon Stack Relative Value: At any time, multiple MBS coupons (2.5%, 3%, 3.5%, 4%, 4.5%) are outstanding; comparing OAS across the coupon stack identifies which coupon offers best value - higher coupons have shorter duration and more refinancing risk, while lower coupons have extension risk.
- TBA vs. Specified Pools: TBA (To-Be-Announced) futures allow generic MBS trading; specified pools trade at a premium of 0-50+ ticks for superior characteristics like low loan balances that reduce prepayment risk.
- Z-Spread vs. OAS: Z-spread assumes no prepayment optionality; OAS adjusts for prepayment behavior; for a current-coupon MBS, the Z-spread vs OAS difference equals the option cost (typically 30-60bps in normal markets) - the option cost is what you pay for the prepayment flexibility given to borrowers.
Portfolio Stress Testing and Scenario Analysis
Regulators and risk managers demand comprehensive stress testing. What happens to your MBS portfolio if rates spike 200 basis points? What if prepayment speeds double? What's the impact of spread widening combined with rate increases? These scenarios require hundreds of calculations in traditional Excel models.
Sourcetable runs complex scenarios through simple conversational requests. 'Model a scenario with +150bp rate shock, spreads widening 25bps, and prepayments increasing to 25 CPR' generates complete portfolio impact analysis including P&L, duration changes, and cash flow projections. The AI applies the scenario consistently across all securities, handles correlation effects, and presents results in clear visualizations. A stress test that took hours in Excel runs in under a minute.
Seamless Data Integration from Multiple Sources
MBS traders work with data from Bloomberg, custodian banks, dealer pricing sheets, and internal systems. Consolidating this information in Excel means manual copying, reformatting, and constant reconciliation. Sourcetable connects directly to data sources or accepts uploads in any format—CSV, Excel, PDF reports, or API feeds.
The AI recognizes MBS identifiers automatically—CUSIP numbers, pool numbers, TBA specifications. Import a dealer run with 300 securities, and Sourcetable maps each to your existing holdings, updates pricing, and recalculates portfolio metrics without manual intervention. When your custodian sends monthly factor updates (showing how much principal has paid down), upload the report and ask 'Update my portfolio with new factors'—done instantly.
Collaborative Analysis for Trading Teams
Trading desks involve portfolio managers, analysts, risk officers, and traders working with the same data. Excel files get emailed back and forth, creating version control nightmares. Someone updates the wrong copy, and suddenly reports don't match. Sourcetable provides a shared workspace where everyone accesses the same live data.
Your risk analyst runs stress tests while your trader monitors live spreads and your portfolio manager reviews performance attribution—all in the same Sourcetable environment. Changes sync instantly. When market conditions shift, the entire team sees updated analytics simultaneously. This eliminates the 'which spreadsheet version is correct?' problem that plagues trading operations.
How MBS Trading Analysis Works in Sourcetable
Sourcetable makes sophisticated mortgage-backed securities analysis accessible through a straightforward workflow. Here's how traders use the platform for daily MBS portfolio management and trading decisions.
Step 1: Import Your MBS Portfolio Data
Start by uploading your current MBS holdings. This might be a custodian report showing CUSIP numbers, original face amounts, current factors, coupon rates, and settlement dates. Or import a Bloomberg portfolio export with market prices, yields, and analytics. Sourcetable accepts Excel files, CSV exports, or direct data connections. The AI automatically identifies columns—recognizing that 'Orig Bal' means original balance and 'Curr Factor' indicates how much principal remains outstanding.
For example, upload a file with 200 FNMA and FHLMC pools totaling $1.2 billion in current face value. Sourcetable ingests the data in seconds and creates a structured portfolio view. No need to format columns, create pivot tables, or set up formulas—the AI understands MBS data structures inherently.
- " means original balance and "
- For example, upload a file with 200 FNMA and FHLMC pools totaling $1.
Step 2: Connect Market Data and Pricing Sources
MBS prices change continuously throughout the trading day. Connect your Bloomberg terminal, import dealer pricing sheets, or use market data APIs. Sourcetable updates security prices automatically and recalculates portfolio values, yields, and spreads in real-time. When TBA prices move from 101-16 to 101-08 (32nds pricing), your portfolio marks adjust instantly without manual updates.
The system handles MBS-specific pricing conventions automatically—32nds notation, settlement date adjustments, and accrued interest calculations. Ask 'What's my total portfolio value?' and get the current mark including accrued interest. Request 'Show me today's P&L' and see exactly how market movements affected your positions.
Step 3: Ask Questions in Natural Language
This is where Sourcetable's AI transforms your workflow. Instead of building formulas, simply ask questions about your portfolio. Type 'What's my weighted average coupon?' and the AI calculates it instantly, weighting each security by current outstanding balance. Ask 'Show me all securities with yields above 5%' and get a filtered list immediately.
More complex queries work just as smoothly. 'Calculate portfolio duration and convexity' triggers sophisticated fixed income calculations across all holdings. 'Which pools have prepayment speeds above 20 CPR?' analyzes recent payment history and flags fast-paying securities. 'Compare my FNMA holdings versus FHLMC by weighted average life' generates a comparative analysis with visualizations.
The AI understands context and MBS terminology. Mention 'spreads' and it knows you mean option-adjusted spreads to benchmarks. Reference 'PSA' and it applies Public Securities Association prepayment assumptions. Say 'TBA' and it recognizes you're discussing to-be-announced forward trades rather than specific pools.
- "s my weighted average coupon?"
- "Show me all securities with yields above 5%"
- "Calculate portfolio duration and convexity"
- "Which pools have prepayment speeds above 20 CPR?"
- " and it knows you mean option-adjusted spreads to benchmarks. Reference "
- " and it applies Public Securities Association prepayment assumptions. Say "
Step 4: Run Scenario Analysis and Stress Tests
Risk management requires understanding how portfolios behave under different market conditions. Ask Sourcetable to model scenarios: 'What happens to portfolio value if rates increase 75 basis points?' The AI applies the rate shock to your entire portfolio, recalculates prices using duration and convexity, and shows the projected P&L impact.
Combine multiple variables: 'Model rates up 100bps, spreads widening 20bps, and prepayments accelerating to 25 CPR.' Sourcetable runs this comprehensive stress test across all securities and presents aggregated results. For a $2 billion MBS portfolio, you might see a projected $85 million loss from the rate increase, partially offset by $12 million from spread widening (if you're positioned correctly), with prepayment acceleration reducing weighted average life from 5.2 to 4.1 years.
Step 5: Generate Automated Reports and Visualizations
Portfolio managers need regular reports for stakeholders, risk committees, and regulatory filings. Instead of manually creating charts and tables, ask Sourcetable to generate them. 'Create a chart showing portfolio allocation by coupon' produces a visual breakdown instantly. 'Generate a report with portfolio statistics, top 10 holdings, and duration analysis' builds a comprehensive document ready to share.
The AI chooses appropriate visualizations automatically. Duration analysis appears as a bar chart comparing securities. Prepayment speeds show as line graphs tracking trends over time. Yield curves display as scatter plots with fitted curves. Every chart is professional-quality and customizable if needed.
Step 6: Monitor and Adjust Positions
Markets move continuously, and MBS portfolios require active management. Sourcetable becomes your constant monitoring tool. Set up alerts: 'Notify me when portfolio duration exceeds 4.5 years' or 'Alert if any security's spread widens more than 15 basis points.' The AI watches your portfolio and flags situations requiring attention.
When evaluating new trades, model their impact before execution. 'What happens to portfolio yield if I buy $50 million FNMA 4.5% at 101-12?' Sourcetable shows how this trade affects overall yield, duration, WAC, and WAL. Compare alternatives: 'Should I buy FNMA 4.5% or FHLMC 4.0% to reduce duration while maintaining yield?' The AI analyzes both scenarios and presents comparative results.
This workflow transforms MBS trading from a formula-intensive Excel exercise into a conversational analysis process. You spend time thinking about markets and making trading decisions, not troubleshooting spreadsheet errors or waiting for calculations to complete.
Real-World MBS Trading Use Cases
Mortgage-backed securities traders face diverse challenges across different market environments and portfolio objectives. Here's how Sourcetable solves specific problems for various MBS trading scenarios.
Agency MBS Portfolio Management for Asset Managers
A $5 billion fixed income fund maintains 30% allocation to agency MBS—primarily FNMA and FHLMC pass-throughs with some GNMA project loans. The portfolio management team needs daily monitoring of 400+ individual pools across multiple coupons and maturities. Their challenge: tracking prepayment speeds, managing duration within a 3.5-4.5 year range, and optimizing yield while maintaining investment-grade credit quality.
Using Sourcetable, the team imports their custodian's daily position file each morning. They ask 'What's current portfolio duration?' and immediately see they're at 4.38 years—approaching their 4.5 upper limit. A quick query—'Show securities with duration above 5 years and yields below 4.5%'—identifies candidates for sale. They find 8 pools totaling $120 million that meet these criteria.
When mortgage rates drop 40 basis points over two weeks, the team asks 'Which securities show accelerating prepayments?' Sourcetable analyzes recent payment history and flags 23 pools where CPR increased more than 5 points month-over-month. These higher-coupon securities face refinancing risk, so the portfolio manager decides to rotate into lower-coupon, more stable pools. The entire analysis—from identifying the problem to finding replacement securities—takes 15 minutes instead of half a day in Excel.
Hedge Fund Relative Value Trading
A mortgage-focused hedge fund executes relative value trades between TBA contracts and specified pools, exploiting pricing inefficiencies. Their strategy involves buying undervalued specified pools with favorable prepayment characteristics and shorting TBA contracts as a hedge. Success requires analyzing thousands of pools daily to identify those trading cheap to TBA despite superior collateral.
The trading desk uploads dealer runs with 2,000+ available pools each morning. They ask Sourcetable: 'Show FNMA 4.0% pools with WAC above 4.25%, WALA below 36 months, and pay-ups less than 8/32nds versus TBA.' The AI filters the universe instantly, returning 47 pools meeting these criteria. These newer, higher-note-rate pools should command 12-16/32nds pay-ups but are currently offered at only 6-8/32nds—a clear value opportunity.
The trader models a potential trade: 'What's the carry and roll-down on buying $100 million of these pools versus shorting FNMA 4.0 TBA?' Sourcetable calculates the net financing cost, projected prepayment income, and price appreciation as the pools age. The analysis shows 18 basis points of positive carry over three months with limited prepayment risk due to the higher note rates. The fund executes the trade, monitoring performance daily through Sourcetable's automated P&L tracking.
Bank Balance Sheet Duration Management
A regional bank holds $800 million in MBS as part of its securities portfolio, balancing liquidity needs with yield generation. The asset-liability management committee requires maintaining portfolio duration between 2.5-3.5 years to match deposit funding duration. Rising interest rates have extended their MBS duration from 3.1 to 3.8 years as prepayments slowed, creating risk of losses if rates continue climbing.
The bank's treasurer uses Sourcetable to evaluate options. She asks 'Show me the impact of selling $150 million of our longest-duration MBS and buying 15-year securities.' The AI identifies the longest-duration holdings (primarily 30-year 3.5% and 4.0% coupons at 5.2 years duration) and models replacing them with 15-year 4.5% pools at 3.1 years duration. The scenario shows portfolio duration dropping to 3.4 years while maintaining similar yield—solving the duration problem without sacrificing income.
She runs a stress test: 'Model rates rising another 100 basis points with prepayments slowing to 5 CPR.' Sourcetable calculates that the current portfolio would lose $42 million in market value and extend to 4.3 years duration. The proposed rebalanced portfolio loses only $28 million and extends to 3.7 years—still within policy limits. This analysis gives the ALCO committee confidence to approve the restructuring.
Non-Agency RMBS Credit Analysis
A distressed debt fund specializes in non-agency residential mortgage-backed securities—legacy deals from 2005-2007 and newer credit-risk transfer securities. These securities require detailed credit analysis of underlying loan pools, including FICO scores, loan-to-value ratios, geographic concentration, and delinquency rates. The fund analyzes hundreds of tranches monthly to identify bonds trading below intrinsic value.
The investment team uploads loan-level data files (often 50,000+ rows) provided by trustees. They ask Sourcetable: 'What percentage of loans have current LTV above 90% and FICO below 680?' The AI analyzes the entire loan pool and returns '8.3% of loans by balance meet these criteria, representing elevated default risk.' Follow-up question: 'How is this distributed geographically?' Sourcetable breaks down the high-risk loans by state, revealing 40% concentration in three states with weak housing markets.
For a specific bond trading at 72 cents on the dollar, they model credit losses: 'Assume 15% cumulative default rate on the remaining pool with 40% severity. What's the projected cash flow to this mezzanine tranche?' Sourcetable runs the loss scenario, applies the waterfall structure, and calculates that this tranche receives full principal return plus accrued interest—implying 38% upside to par from the current 72 price. This analysis drives a $25 million investment decision.
These use cases demonstrate how Sourcetable handles the full spectrum of MBS trading activities—from plain-vanilla agency portfolio management to complex non-agency credit analysis. The common thread: transforming time-consuming Excel analysis into fast, conversational data exploration that lets traders focus on markets instead of spreadsheets.
Frequently Asked Questions
If your question is not covered here, you can contact our team.
Contact UsWhat is the Option-Adjusted Spread (OAS) in MBS trading?
How does convexity affect MBS valuation and risk?
What is the TBA (To-Be-Announced) market and why does it matter?
What prepayment models are used in MBS analysis?
What is the difference between agency MBS and non-agency MBS?
How do rising interest rates affect agency MBS prices differently than Treasuries?
What MBS-focused ETFs exist and what is their typical yield pickup vs Treasuries?
