Bond modified duration is a measure of how much the price of a bond changes when the interest rate changes. It is expressed as a percentage, and it tells us how much the bond price will go up or down for every 1% change in the interest rate.
For example, if a bond has a modified duration of 5%, it means that if the interest rate goes up by 1%, the bond price will go down by 5%. Conversely, if the interest rate goes down by 1%, the bond price will go up by 5%. This is because bond prices and interest rates have an inverse relationship: when interest rates rise, bond prices fall, and vice versa.
Bond modified duration is based on another concept called Macaulay duration, which is the average time it takes for a bondholder to get back the money invested in the bond. Macaulay duration is calculated by weighting the time of each cash flow (coupon payment or principal repayment) by its present value. The higher the Macaulay duration, the longer it takes for the bondholder to recover the investment, and the more sensitive the bond price is to interest rate changes.
To get the modified duration, we divide the Macaulay duration by a factor that depends on the yield to maturity (YTM) and the frequency of coupon payments of the bond. The YTM is the total return that a bondholder will get if the bond is held until maturity. The frequency of coupon payments is how often the bond pays interest, such as annually, semiannually, quarterly, etc.
Basic Theory
Modified duration is a measure of the price sensitivity of a bond to changes in interest rates. It is expressed in years and helps investors estimate how much a bond’s price will change for a 1% change in interest rates. The formula for modified duration is as follows:
Modified Duration = Macaulay Duration / (1 + (Yield / Number of Compounding Periods))
Where:
- Macaulay Duration is the weighted average time to receive the bond’s cash flows.
- Yield is the bond’s annual yield to maturity.
- Number of Compounding Periods is the number of compounding periods per year.
Procedures for Calculating Modified Duration in Excel
- Determine Macaulay Duration:
- Macaulay Duration is calculated as the sum of the present value of each cash flow, multiplied by the time to receive that cash flow, divided by the current bond price.
- Macaulay Duration = Σ((CFt / (1 + (Yield / Number of Compounding Periods))^(t * Number of Compounding Periods)) * t * Number of Compounding Periods) / Current Bond Price
- Use Modified Duration Formula:
- Once Macaulay Duration is determined, apply it to the modified duration formula.
- Modified Duration = Macaulay Duration / (1 + (Yield / Number of Compounding Periods))
Let’s consider a 5-year bond with a face value of $1,000, a 6% annual coupon rate, and a yield to maturity of 5%. The bond pays interest semi-annually, and the current market price is $950.
Excel Table for Scenario:
| Year | Cash Flow | Present Value | Weighted PV*Year |
|---|---|---|---|
| 1 | $30 | $28.57 | $28.57 |
| 2 | $30 | $27.21 | $54.42 |
| 3 | $30 | $25.92 | $77.76 |
| 4 | $30 | $24.69 | $98.76 |
| 5 | $1,030 | $863.84 | $4319.22 |
Calculation Steps:
- Determine Macaulay Duration:
- Macaulay Duration = Σ(Weighted PV*Year) / Current Bond Price
- Apply Modified Duration Formula:
- Modified Duration = Macaulay Duration / (1 + (Yield / Number of Compounding Periods))
Results and Conclusion
In our scenario, the modified duration is approximately 4.53 years. This implies that for a 1% increase in interest rates, we can expect a 4.53% decrease in the bond’s price and vice versa.
Other Approaches
- Excel Functions:
- Excel provides built-in functions for financial calculations. The DURATION function can be used to calculate Macaulay Duration, and then modified duration can be derived accordingly.
- Solver Add-In:
- For more complex bond structures, the Solver Add-In in Excel can be employed to find the yield that minimizes the difference between the bond’s duration and modified duration.
