The MDURATION function in Excel is used to calculate the Macaulay modified duration for a security with periodic interest payments, which measures the bond’s price sensitivity to changes in interest rates. Here’s how to use the MDURATION function in Excel:
Syntax
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
Parameters
- settlement: The date when the bond is purchased by the buyer. This should be entered as a date value.
- maturity: The date when the bond expires or matures. This should also be entered as a date value.
- coupon: The bond’s annual coupon rate expressed as a decimal. For example, for a 5% coupon rate, enter 0.05.
- yld: The bond’s annual yield expressed as a decimal. For example, for a 6% yield, enter 0.06.
- frequency: The number of coupon payments per year. Use 1 for annual, 2 for semiannual, or 4 for quarterly payments.
- [basis]: (Optional) The day count basis to be used. If omitted, it defaults to 0. It can take the following values:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Example
Suppose you have a bond with the following details:
- Settlement date: January 1, 2023
- Maturity date: January 1, 2030
- Annual coupon rate: 5%
- Annual yield: 6%
- Coupon payments: Semiannual (2 times a year)
- Day count basis: Actual/actual (1)
You would enter the formula in Excel like this:
=MDURATION(DATE(2023, 1, 1), DATE(2030, 1, 1), 0.05, 0.06, 2, 1)
Steps to Enter the Formula
- Select the cell where you want the result to appear.
- Type `=MDURATION(` into the formula bar or cell.
- Input each parameter, separated by commas.
- Close the parenthesis and press Enter.
Important Notes
- Ensure that the settlement date is before the maturity date; otherwise, the function will return an error.
- Make sure to format the dates properly so that Excel recognizes them as date values.
- Use the correct periodicity (frequency) based on the bond’s coupon payments to get accurate results.
Unlock Your Potential
Excel
Access
Power BI
