Practice free

MDURATION function

MDURATION returns the modified duration of a security in years, which is Macaulay duration divided by one plus the periodic yield. It estimates the percentage price change for a one-point change in yield.

Example

Read it as a sentence: find the modified duration of a bond from January 1, 2024 to January 1, 2029, with an 8% coupon and 9% yield, paying twice a year, which gives 4.01806536102434.

Practice this in the app

Syntax

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

Arguments in [square brackets] are optional.

settlement (required)
Date the security is bought (after issue, before maturity).
maturity (required)
Date the security expires and is repaid.
coupon (required)
Annual coupon rate.
yld (required)
Annual yield.
frequency (required)
Coupon payments per year: 1 annual, 2 semiannual, 4 quarterly.
basis (optional)
Day-count convention: 0 or omitted = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360.

Returns: Modified duration in years.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2,0) 6.817646373 Typical
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2) 6.817646373 Basis omitted
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,1,0) 6.72768278 Frequency 1
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,4,0) 7.011174 Frequency 4
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2.9,0) 6.817646373 Frequency 2.9 truncates
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,2,0) 6.817646373 Basis 0
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,3,0) #NUM! Frequency 3
=MDURATION(DATE(2024,2,15),DATE(2034,11,15),0.08,0.09,0,0) #NUM! Frequency 0

Watch out for

  • Pass dates with DATE() or cell references; text dates can misbehave and invalid dates return #VALUE!.

Where it works

Excel
MDURATION is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .