Bond Trading Model
Capital Markets Financial Model (Free Excel Download)
Model bond inventory, purchases, sales, financing, mark-to-market gains, carry, duration, and trading performance across rate scenarios.
professionals from Deloitte
Used by professionals from
About this model
A bond trading desk model tracks fixed income positions, calculates mark-to-market P&L, Greeks (duration, convexity, DV01, key rate durations), and scenario analysis to support daily P&L reporting, risk monitoring, and inventory management. The model answers what the current unrealised loss is on the portfolio, how much P&L would swing if the yield curve shifts, and where the concentration risk sits across issuer and sector.
Positions are tracked with issuer name, maturity, coupon, amount, and cost basis. Valuation uses a bond pricing formula (present value of future coupons plus final principal, discounted at current market yield) which updates daily as spreads and base rates move. P&L is decomposed into carry (coupon accrual), roll-down (capital appreciation as bonds age and yields converge to maturity), spread widening/tightening, and curve reshaping (steepening or flattening). Greeks are calculated analytically: duration tells the percentage price move per 1% yield change (DV01 in dollars), convexity accounts for the acceleration of price changes at extreme yields, and key rate durations isolate sensitivity to specific maturity buckets (2Y, 5Y, 10Y, 30Y). Scenario analysis applies parallel shifts (all yields up/down 100bps), steepening (short-end up, long-end down), or flattening to show portfolio sensitivity.
Fixed income traders, portfolio managers, and risk managers use bond models to size trading positions, monitor daily P&L attribution (which curve factors drove profits/losses), and stress test the book for adverse rate or credit scenarios.
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 Bond Trading Model
- Position-level inventory: issuer, maturity, amount, cost basis
- Mark-to-market pricing with yield curve and spread assumptions
- Duration, convexity, DV01, and key rate durations (KRD)
- P&L attribution: roll-down, carry, spread, and curve reshaping
- Scenario analysis: parallel shift, steepening, flattening
- Portfolio concentration and counterparty exposure
What a Bond Trading Model Captures for a Fixed-Income Desk
This bond trading model template organises the economics of a fixed-income trading desk, from client flow and inventory carry through to mark-to-market risk and regulatory capital. It shows how four revenue streams, funding costs, compensation and capital requirements connect in one structure, so a reader can follow the drivers behind reported trading performance.
Revenue Drivers and Trading Activities
The template models four revenue streams. Bid-ask spread income equals client trading volume multiplied by an average spread, and it is sensitive to market volatility and franchise breadth.
- Carry income is the average inventory position multiplied by the difference between bond yield and repo funding cost, so curve shape and inventory duration matter. Mark-to-market P&L is net position exposure multiplied by a change in spread or rate, a scenario-driven figure that can be negative.
- New issue fees reflect primary market volume and syndication fee rates.
Calculating Net Trading Revenue and Costs
Net trading revenue is the sum of bid-ask income, carry income, mark-to-market P&L and new issue fees.
- Average inventory, calculated as the simple average of opening and closing trading assets, drives both carry income and repo funding cost, which keeps the two calculations consistent.
- Repo financing is treated as a trading liability that scales with inventory, not as corporate debt.
- Compensation combines base salaries with a bonus pool constrained by a floor, and other operating costs cover technology, compliance, occupancy and operations.
Outputs, Metrics and Control Checks
The income statement flows into a balance sheet, a cash flow statement and a dedicated capital adequacy sheet that calculates risk-weighted assets from market, credit and operational risk components, then compares CET1 capital against a regulatory minimum.
- A key metrics sheet produces return on tangible common equity, compensation and efficiency ratios, inventory turnover, value-at-risk utilisation and revenue per trader.
- Built-in checks test that the balance sheet balances, CET1 and leverage ratios stay inside defined ranges, cash never turns negative, and dividends do not exceed available excess capital.
Practical Use for Evaluating a Trading Operation
The structure supports evaluating whether to invest in, expand or assess a fixed-income trading operation by linking projected trading profit and loss to risk-adjusted returns, capital efficiency and regulatory capital adequacy.
- Because client volume growth and inventory growth are independent inputs, the model shows how inventory turnover changes over time.
- Scenario levers for mark-to-market stress can trigger inventory restraint, and the funding and capital calculations demonstrate how position size, repo spreads and capital charges interact when sizing a desk.
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 bond trading model?+
It is a model that tracks fixed income positions, calculates mark-to-market P&L, Greeks, and scenario analysis for trading desks.
What is DV01 and how is it calculated?+
DV01 (Dollar Value of 1 basis point) measures the change in position value if yields rise 1 bp; it equals duration * position size / 10,000.
What is a key rate duration (KRD)?+
KRD measures sensitivity to moves at specific points on the yield curve (2Y, 5Y, 10Y, 30Y) rather than parallel shifts.
How do I model carry?+
Carry is the daily P&L from coupon accrual and roll-down as a bond ages closer to maturity.
Can I run curve scenarios?+
Yes. The model supports parallel shifts, steepening, flattening, and custom twists with instant portfolio repricing.
Have more financial modelling questions? Contact us
Related templates
Fixed Income Portfolio Model
Bond portfolio analytics with duration, convexity, credit spreads, and yield curve positioning.
Credit Rating Model
Calculate credit metrics and probability of default to estimate debt ratings.
Convertible Bond Model
Model convertible bond economics with conversion scenarios, dilution, and bond value.
DCF Model
Discounted cash flow valuation model with explicit forecast, terminal value, and WACC.
