Fixed Income Portfolio Model

The model is built around four revenue sources: coupon income, realised gains or losses on sales, amortisation of purchase premium or discount, and reinvestment at current yields. Coupon income depends on the opening value of each tranche and its coupon rate, while amortisation uses the difference between purchase price and par divided by remaining maturity.

• Credit losses arise from annual default rates by tranche multiplied by loss given default, and operating costs include management, custody, administration, and transaction fees. These inputs are set on an Assumptions sheet, so changing yields, default rates, or fee levels directly affects the projected outcome.
• The model assumes no leverage and quarterly income distributions.

The portfolio allocation schedule deploys opening capital, new contributions, maturities, and retained income across government, investment-grade corporate, high-yield corporate, and mortgage-backed tranches. It also deducts credit losses from closing values.

• The income schedule then applies coupon rates to opening tranche values and adds or subtracts premium/discount amortisation. A separate credit losses schedule calculates gross defaults, applies recovery rates, and produces net losses.
• The expense schedule charges fees on opening AUM to avoid circularity, and the cash flow waterfall collects coupon receipts, maturities, new capital, and asset sales, then pays for bond purchases, fees, and distributions. Finally, the portfolio summary aggregates income, losses, and expenses into net investment income and total return.

The returns analysis sheet presents period income return, price return, and total return, as well as annualised return, portfolio duration, yield to maturity, weighted average credit quality, and a Sharpe ratio proxy. It also shows attribution between income, credit, and rate effects.

• The portfolio summary reports net investment income, realised and unrealised gains, total return in dollars and percentage, income yield, and cumulative return. A checks sheet validates that allocations sum to 100%, cash never goes negative, credit losses do not exceed tranche values, distributions are funded from available income, and the expense ratio stays between 0.10% and 1.00%.
• These checks help catch common input errors without needing to trace every formula.

You can change reinvestment yields, default rates, or distribution policy to see how the portfolio responds under different rate or credit environments. The model automatically reflects the impact on coupon income, amortisation, and cash available for reinvestment.

• It also highlights reinvestment risk and extension risk because falling rates can lead to higher premium amortisation and longer effective durations, while rising rates create unrealised losses. The template is designed for a single portfolio over a five-year horizon with no leverage.
• The public download is a values-only preview, so you can review the structure and logic but not edit live formulas. Use it to understand the key relationships before building or customising your own version.