MDURATION function

The MDURATION function in Excel calculates the Macaulay duration of a bond. The Macaulay duration is a measure of the weighted average time to receive the bond’s cash flows (i.e., interest payments and the principal repayment) and is often used to assess the sensitivity of a bond’s price to interest rate changes. In other words, it represents how long, on average, it takes for an investor to receive the bond’s cash flows.

Syntax

MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Parameters

• settlement: The bond’s settlement date, i.e., the date after issuance when the bond is traded to the buyer. This must be a valid date.
• maturity: The bond’s maturity date, i.e., the date when the bond will mature and the face value will be repaid to the investor. This must also be a valid date.
• coupon: The bond’s annual coupon rate (interest rate). This is expressed as a percentage (e.g., 6% would be entered as 0.06).
• yld: The bond’s annual yield (yield to maturity). This is the annual return on the bond, expressed as a decimal.
• frequency: The number of coupon payments per year:
  • 1 for annual payments
  • 2 for semi-annual payments
  • 4 for quarterly payments
• [basis] (optional): The day count basis to use for calculations:
  • 0 or omitted: 30/360 (The most commonly used basis, assuming each month has 30 days and a year has 360 days).
  • 1: Actual/actual (actual number of days in a month and a year).
  • 2: Actual/360 (actual days in a month, but a year is considered to have 360 days).
  • 3: Actual/365 (actual days in a month, but a year is considered to have 365 days).
  • 4: 30/360 (same as 0).

How It Works

The MDURATION function calculates the Macaulay duration, which measures the average time (in years) for a bond’s cash flows to be repaid. The formula for the Macaulay duration is a weighted average of the time periods, with the weights being the present values of the bond’s cash flows.

The formula is as follows:

$$\text{Duration} = \frac{\sum \left(\frac{t \times \text{PV of Cash Flow at time t}}{(1 + \text{yield})^t}\right)}{\text{Bond Price}}$$

Where:

• $t$ is the time period.
• PV refers to the present value of the cash flows at time $t$.

Example

Let’s say you have the following bond details:

• Settlement Date: January 1, 2025
• Maturity Date: January 1, 2035
• Coupon Rate: 6% (annual)
• Yield: 5% (annual)
• Frequency: 2 (semi-annual payments)

To calculate the Macaulay duration, use the MDURATION function as follows:

=MDURATION("2025-01-01", "2035-01-01", 0.06, 0.05, 2)

Explanation of the Example:

• "2025-01-01": The settlement date (when the bond is traded).
• "2035-01-01": The maturity date (when the bond matures).
• 0.06: The annual coupon rate of 6%.
• 0.05: The annual yield of 5%.
• 2: The bond makes semi-annual payments.

This function will return the Macaulay duration, which indicates the weighted average time (in years) to receive the bond’s cash flows.

Important Notes

• The Macaulay duration can be used to assess the interest rate risk of a bond. A higher duration means that the bond is more sensitive to interest rate changes, while a lower duration indicates less sensitivity.
• The MDURATION function assumes the bond’s cash flows are made at regular intervals (e.g., annually, semi-annually). It doesn’t work for bonds with irregular payment schedules.

Summary

The MDURATION function in Excel calculates the Macaulay duration of a bond, which is a measure of the average time it takes for a bond’s cash flows to be repaid. It is a useful tool for investors to assess the interest rate risk associated with a bond and to understand how the bond’s price may react to changes in interest rates.