Fixed Income Portfolio Analysis

The spreadsheet approach everyone uses (and why it lies to you)

Most people doing Fixed Income Portfolio Analysis start with a clean dataset, run their scripts, and get back a bunch of numbers they pretend make sense. The truth is it's messy from day one. You'll be pulling Bloomberg terminal exports that have missing tickers, stale prices, and positions that don't reconcile between your custody statement and what your risk system thinks you own. I've spent years just untangling that baseline mess before anything useful comes out. Here's the practical way to actually do this. Start by understanding what you're trying to measure. You're not just calculating yield or duration for the sake of it. You're trying to answer whether this portfolio will behave the way you expect when rates move, when spreads widen, when prepayments accelerate, or when a liquidity event hits. The moment you conflate those things is the moment the analysis goes off the rails.

Fixed Income Portfolio Analysis

The core workflow breaks into three stages. First, you reconstruct the position-level data with as much granularity as your sources allow. That means getting coupon rates, payment dates, call schedules, embedded options, and issue-specific conventions like day count and settlement terms. Second, you price every position to a common valuation date using appropriate curves. Third, you aggregate risk metrics while accounting for how the positions actually interact with each other. The part everyone glosses over is the pricing step. You can't just plug in a treasury curve and call it a day. Corporate bonds need a spread overlay. MBS needs a prepayment model. Foreign currency exposure needs to be handled if you're holding euro-denominated paper. When I started out, I used to apply a flat OAS to everything and wonder why my risk numbers looked wrong during volatile periods. It was because OAS is not constant. It changes with the curve shape, with volatility regimes, and with where you are relative to the call wall. I had a specific problem a few years ago where a client's portfolio had significant commercial mortgage-backed securities positions. The standard analytics engine was spitting out durations that made no sense. Turns out the prepayment model was calibrated to historical S&P/Case-Shiller data from the mid-2000s, which massively understated prepayment speed in the then-current environment. The portfolio looked like it had zero interest rate risk when it actually had enormous negative convexity sitting there. I ended up writing a custom prepayment layer that pulled in current loan-level data, ran a Monte Carlo simulation with the actual remaining loan balances, and recalculated the duration from that output. The position's effective duration jumped from about 1.3 years to roughly 5.8 years once the real prepayment assumptions were applied. That single adjustment changed the entire hedging strategy.

There's a counter-intuitive thing most people miss about duration. Higher duration doesn't always mean more risk. If your portfolio has long-dated floating rate notes that reset quarterly, they'll show low duration but can still have massive exposure to basis swap movements or funding cost shifts. Meanwhile, a portfolio of short-duration inflation-linked securities might have a higher DV01 than you'd expect because the breakeven inflation assumption moves the entire curve, not just the nominal side. People look at a single number and treat it as the whole picture. It's not. Another thing that bites people is the treatment of accrued interest. When you're comparing portfolios across managers or running attribution analysis, even a couple of basis points of error from wrong accrued interest calculations can compound into meaningful P&L drift over a quarter. Make sure your system handles payment date quirks properly, especially for bonds with irregular coupons or bonds that trade between ex-coupon dates.

What the standard tools get wrong

Bloomberg PORT and MSCI risk systems are fine starting points, but they have blind spots. Both tend to over-rely on vendor-supplied analytics rather than letting you drill into the cash flow level. When spreads move by more than twenty basis points, the linear approximation in most standard duration calculations breaks down. You need scenario-based analysis that actually reprices the portfolio under shifted curves, not just a single derivative estimate. For credit risk, the market value approach using CDS spreads or hazard rates is useful but incomplete. It doesn't capture liquidity risk, concentration risk, or the fact that in a stress event your correlations break down and everything sells at the same time. I've seen portfolios that looked perfectly diversified on paper get wiped out in a matter of days because the diversification was based on sector labels rather than actual cash flow exposure to the same macro driver. If you're analyzing a large portfolio with hundreds or thousands of positions, the computational bottleneck is usually the pricing engine, not the data. A well-built cash flow generator that feeds into a vectorized pricer can handle ten thousand positions in under two minutes on a decent machine. A naive approach that calls a pricing function in a loop for each position can take hours. Profile your code early.

Here's a practical checklist I use now. Pull the position file and validate the sum of market values against the custody statement to five decimal places. Check for any flagged positions like restricted securities or known settlement fails. Run a quick aggregate duration and convexity sanity check against a manual calculation of at least ten positions across different sectors. If the numbers are within a basis point or two, you probably have the right data. If they're off by more, something is misaligned in the pricing or valuation step. From there, I run the full risk analysis using scenario shifts rather than relying solely on analytical Greeks. I shift the treasury curve by plus and minus fifty basis points in parallel, then run key rate durations to see which tenors drive the risk. For credit positions, I overlay spread scenarios and check what happens when certain sectors widen simultaneously. I also run a liquidity stress test by assuming a certain percentage of positions can't be sold without a discount, and I size that discount based on historical trading ranges for that sector and that market condition. The final piece is attribution. You need to know whether your returns came from duration positioning, spread pickup, sector rotation, or security selection. Without this, you're just guessing about what drove performance and you'll repeat the same mistakes. I usually break it down by contribution to total return across these four buckets for each period I'm analyzing. The numbers don't always look great, but they tell you the truth.

If you want to download a template for this, I keep a Python-based framework that handles the cash flow extraction, curve building, and scenario analysis. It's not a complete product, just something I use internally. It assumes you have access to either a vendor API for pricing or your own curve source. If you're working in Excel, the process is roughly the same but you'll spend more time fixing broken formulas and less time doing actual analysis. That's the tradeoff most people accept until they hit a portfolio large enough to make Excel unusable.