MS Excel: MDURATION function for modified Macaulay

The MDURATION function in Excel calculates the modified Macaulay duration of a security (such as a bond) that pays periodic interest.

In simple words:MDURATION measures the price sensitivity of a bond to changes in interest rates, adjusting for how often the bond pays interest.

It’s mainly used by investors, portfolio managers, and financial analysts to assess interest rate risk.

Syntax

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

Argument	Description
settlement	The date you purchase the bond.
maturity	The date the bond matures (ends).
coupon	The annual coupon (interest) rate of the bond.
yld	The bond's annual yield (expected return).
frequency	Number of payments per year:  1 = Annual, 2 = Semi-annual, 4 = Quarterly.
[basis] (optional)	The day-count basis to use (default is 0 = US 30/360).

How MDURATION Works

• Macaulay Duration: measures how long it takes, in weighted average terms, to be repaid by the bond's cash flows.

• Modified Duration (which MDURATION calculates): adjusts the Macaulay Duration to account for changing yields.

Modified Duration = Macaulay Duration / ( 1 + Yield​/Frequency​)

It shows how much a bond’s price will change for a 1% change in yield.

Example 1: Basic MDURATION Calculation

You buy a bond with:

• Settlement Date: January 1, 2024

• Maturity Date: January 1, 2034

• Annual Coupon Rate: 5%

• Yield to Maturity: 4.5%

• Payments: Semi-annual (twice a year)

• Basis: 0 (US 30/360)

  
  

Cell	Data
A2	Settlement: 01/01/2024
A3	Maturity: 01/01/2034
A4	Coupon: 5%
A5	Yield: 4.5%
A6	Frequency: 2
A7	Basis: 0

Formula:

=MDURATION(A2, A3, A4, A5, A6, A7)

Result:Modified Duration ≈ 7.89 years

Full Example Table

Cell	Description	Value
A1	Settlement	01/01/2024
A2	Maturity	01/01/2034
A3	Coupon Rate	5%
A4	Yield	4.5%
A5	Frequency	2 (Semi-annual)
A6	Basis	0 (US 30/360)
A7	Formula	=MDURATION(A1,A2,A3,A4,A5,A6)
A8	Output	7.89

Understanding the Result

• 7.89 means if interest rates increase by 1%, the bond’s price will fall by approximately 7.89%.

• It shows how sensitive the bond is to interest rate movements.

  
  

Example 2: Using Different Frequencies

If the bond pays quarterly instead (4 times per year):

Frequency	Value
Quarterly	4

=MDURATION(A2, A3, A4, A5, 4, 0)

Result:Modified Duration will be slightly lower because payments are more frequent, reducing risk.

Important Notes

• Settlement and maturity must be valid dates, and settlement must be before maturity.

• Frequency must be 1, 2, or 4 — annual, semiannual, or quarterly payments.

• Basis determines how days are counted (important for precise financial calculations).

Real-World Applications

• Bond portfolio management.

• Comparing bonds with different maturities and coupons.

• Risk management strategies.

• Predicting how interest rate changes will affect bond prices.

Conclusion

The MDURATION function in Excel is essential for anyone working with bonds or fixed-income securities.It provides a clear measure of interest rate sensitivity, helping you manage and compare investment risks effectively.