Understanding Bond Portfolio Duration and Calculation in Excel

Bond portfolio duration is a measure of how sensitive the value of a portfolio of bonds is to changes in interest rates. It is calculated as the weighted average of the durations of the individual bonds in the portfolio. Duration, in turn, is a measure of how long it takes, in years, for a bond to repay its price by its cash flows.

The higher the duration of a bond or a portfolio, the more its price will change when interest rates change. For example, if interest rates increase by 1%, a bond or a portfolio with a duration of 5 years will lose about 5% of its value. Conversely, if interest rates decrease by 1%, the same bond or portfolio will gain about 5% of its value.

Duration depends on two main factors: the time to maturity and the coupon rate of a bond. The longer the time to maturity, the higher the duration, and the greater the interest rate risk. The higher the coupon rate, the lower the duration, and the lower the interest rate risk. This is because a bond with a higher coupon rate will pay back its price faster than a bond with a lower coupon rate.

To calculate the duration of a bond portfolio, one needs to know the market value, the duration, and the weight of each bond in the portfolio. The weight of a bond is the ratio of its market value to the total market value of the portfolio. The duration of a bond portfolio is then the sum of the products of the weights and the durations of each bond.

For example, suppose a portfolio consists of two bonds: a 4-year zero-coupon bond and a 5-year semi-annual coupon bond. The details of the bonds are shown below:

Table

Bond Maturity (Years) Coupon (%) Price YTM (%) Duration Convexity
Zero 4 0 87.1442228 3.5 3.8647 19.32367
Semi-annual 5 4.5 101.115515 4.25 4.441605 23.12742

The market value of the zero-coupon bond is $87.1442228×$5,000,000 = $435,721,114. The market value of the semi-annual coupon bond is $101.115515×$5,000,000 = $505,577,575. The total market value of the portfolio is $435,721,114 + $505,577,575 = $941,298,689.

The weight of the zero-coupon bond is $435,721,114/$941,298,689 = 0.46289357. The weight of the semi-annual coupon bond is $505,577,575/$941,298,689 = 0.537106426.

The duration of the bond portfolio is (0.46289357×3.8647) + (0.537106426×4.441605) = 4.174559367.

This means that if interest rates change by 1%, the value of the portfolio will change by about 4.17%.

Basic Theory:

Duration measures the sensitivity of a bond’s price to changes in interest rates. It is expressed in years and
represents the weighted average time it takes for the bond’s cash flows (interest payments and principal
repayment) to be repaid. A bond with a higher duration is more sensitive to interest rate changes.

The formula for Macaulay duration is given by:

        D = ∑ [(t * CFt) / (1+y)^t] + [n * M / (1+y)^n] / P
    

Where:

  • D = Macaulay Duration
  • t = Time period
  • n = Total number of periods
  • CFt = Cash flow at time t
  • y = Yield or discount rate per period
  • M = Face value of the bond
  • P = Current market price of the bond

Procedures:

  1. Set Up Your Excel Spreadsheet:
    • Create a table with columns for time period (t), cash flows (CFt), and the present value of cash
      flows.
    • Include rows for each cash flow period and a final row for the total.
  2. Input Your Bond’s Characteristics:
    • Enter the bond’s cash flows, face value, yield, and market price in the appropriate cells.
  3. Calculate Present Value of Cash Flows:
    • Use the formula PVt = CFt / (1+y)^t to calculate the present value of each cash flow.
  4. Calculate Macaulay Duration:
    • Use the formula mentioned above to calculate the Macaulay Duration for the bond.

Scenario:

Let’s consider a 5-year bond with a face value (M) of $1,000, a 5% annual coupon rate, and a market price (P)
of $950 with a yield (y) of 4%.

Calculation in Excel:

t CFt PVt
1 $50 50 / (1+0.04)^1
2 $50 50 / (1+0.04)^2
3 $50 50 / (1+0.04)^3
4 $50 50 / (1+0.04)^4
5 $50 + $1,000 (1,050) / (1+0.04)^5
Total $1,250

Now, apply the Macaulay Duration formula:

        D = [(1 * 39.22) + (2 * 37.69) + (3 * 36.21) + (4 * 34.77) + (5 * 874.64)] / 950
    

Result: The Macaulay Duration of the bond is approximately 4.33 years, indicating the average time it takes for
the bond’s cash flows to be repaid.

Other Approaches:

  • Modified Duration:
                    Modified Duration = Macaulay Duration / (1+(y/n))
                

    Modified Duration provides a percentage change in the bond’s price for a 1% change in yield.

  • Effective Duration:

    Incorporates the impact of changes in both interest rates and cash flows.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *