The Macaulay duration (named after Frederick Macaulay) and its modified version, is a measure of a bond’s sensitivity to interest rate changes.
Technically, duration is the weighted average number of years the investor must hold a bond until the present value of the bond’s cash flows equals the amount paid for the bond. Bonds with a higher duration will carry more risk, and hence have a greater volatility in prices, when compared to bonds with lower durations.
Excel gives us two function to calculate both versions of Macaulay duration. The DURATION and the MDURATION.
Practice File:
Transcript
In cells B10 and B11 we have already calculated these values which are represented in years.
Let’s try to calculate them again using a different security investment.
In cell B10 we will calculate the Macaulay Duration.
We type the name of the function, the settlement date from cell B4, maturity date from B5, the coupon rate from B6, the yield from B8 and the frequency of coupon payments from B7.
The last argument, basis, is optional and it helps us choose the type of day count to use. Since we will always use the default 0 value, we can omit it.
In Cell B11 we will use the MDURATION function, which calculates the modified Macaulay duration. The arguments are exactly the same. Cell B4, cell B5, cell B6, cell B8 and cell B7. We again omit the last argument.
If we change the annual coupon rate from 4 to 10 or 1 per cent we would observe that the two values are fairly stable. This means that they are not so sensitive to interest rate changes, thus making them a relative safe investment.