DURATION

What it does

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

DURATION(settlement, maturity, coupon, yld, frequency, [basis]) returns the Macaulay duration of a coupon bond with an assumed par value of 100, expressed in years.

Macaulay duration is the present-value-weighted average time to receipt of a security's cash flows:

D = ( Σ t_k · C_k · v^(t_k) ) / ( Σ C_k · v^(t_k) )        with  v = (1 + y/f)^(−f)

where t_k is the time in years from settlement to cash flow k, C_k the cash flow (a coupon of 100·coupon/f, plus 100 at maturity), y the annual yield and f the coupon frequency. The denominator is the dirty price; the numerator is that price with each term weighted by its own maturity. So duration is a weighted mean of times, and it carries units of years — that is the whole content of the definition, and it is why a zero-coupon bond's duration is exactly its time to maturity.

Two identities are worth holding on to:

1. Zero coupon. With coupon = 0 the only cash flow is the redemption, so D is the time to maturity in years. This is the sanity check that catches most implementation errors.

2. The price-sensitivity link. With P(y) the price,

   dP/dy = −(D / (1 + y/f)) · P
   

   which is why the modified duration D/(1 + y/f) — see MDURATION — is the number risk desks actually quote. Macaulay duration is the time; modified duration is the sensitivity.

Behaviour in the limits: D increases with maturity, decreases with coupon rate, and decreases with yield. For a perpetual-style long bond D approaches (1 + y/f)/y from below rather than growing without bound, which is why a 30-year and a 100-year bond have far more similar durations than their maturities suggest. As y → −f the discount base 1 + y/f approaches zero and the expression has a pole; the admissible domain excludes it (see Errors).

How Excel measures the time axis

The one place where a textbook formula and a spreadsheet function part company is the time axis. Excel does not measure t_k from settlement in calendar years. It works in coupon periods:

• the coupon schedule is generated backwards from maturity at 12/frequency-month steps until the first date at or before settlement, giving n remaining coupons;
• the settlement's position inside the current coupon period is expressed as a fraction, and the exponent for the k-th remaining coupon is that fraction plus k;
• the final quotient is divided by frequency to convert periods into years.

The fractional offset is the accrual measurement, and it depends on basis. This is why two calls that differ only in basis can differ in the third decimal of the answer: the day count is not a presentational choice, it changes the exponent on every discount factor.

Arguments

The day-count basis values are the standard five: 0 or omitted = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360.

Three argument traps, in decreasing order of how often they bite:

1. coupon and yld are annual rates even when frequency is 2 or 4. A 6% semi-annual bond is coupon = 0.06, frequency = 2 — not 0.03. The function divides by frequency internally.
2. frequency admits only 1, 2 and 4. There is no monthly bond here; frequency = 12 is an error, not a slow path.
3. Dates and basis are truncated to integers, as documented for the whole bond family. There is no issue date argument: DURATION assumes settlement falls inside a regular coupon period. Odd first or last periods need ODDFPRICE-family thinking, and DURATION will not tell you it was the wrong function.

Try it

Quirks

Result and edge cases

Returns Number — a duration in years, not in periods and not in days.

• settlement ≥ maturity is a domain error, not a zero or negative duration.
• Zero coupon is admissible (coupon = 0) and gives time to maturity in years.
• Zero yield is admissible and makes every discount factor 1, so D becomes the simple cash-flow-weighted average time.
• Non-integer frequency and basis are truncated toward zero before the range check, so frequency = 2.9 reads as 2.
• Empty, missing and error arguments follow the shared call model; see The value universe and Coercion and lifting.

Errors

As documented on Microsoft's DURATION page:

How to implement

Numerical notes

DURATION is a short sum of well-scaled terms, and the numerator and denominator are both positive and of similar magnitude, so the reduction itself is benign — no cancellation, no overflow for any realistic bond. The difficulty is entirely in the schedule and in the discount factor.

The schedule is integer calendar arithmetic and is unforgiving. Coupon dates are generated by month arithmetic backwards from maturity, with an end-of-month rule: a bond maturing on 31 August pays on 28/29 February, and a naive "same day number" step lands on the wrong date. Every discount exponent depends on that schedule, so a single mis-stepped coupon date moves the answer far beyond any rounding concern.

The discount factor is a pow, and pow is not one function. The exponents are integers when settlement falls exactly on a coupon date and fractional otherwise. Integer exponents can be evaluated by binary exponentiation (square-and-multiply on plain multiplies) or by exp(y·ln x), and the two differ in the last bits. Fractional exponents can be evaluated through a modern correctly-rounded pow or through a legacy exp(ln) chain, and those differ too. The reference engine deliberately implements both staging cases and documents that it selects them to match observed Excel behaviour. The Handbook does not assert what Excel does internally; what it can say is that this is the axis on which careful implementations of bond math disagree, and that any claim of agreement that has not distinguished on-coupon from off-coupon settlement has not tested the interesting half.

For the underlying mathematics, Macaulay's weighted-average-time construction and the dP/dy = −D_mod·P link are standard; any fixed-income text develops them, and the discrete-schedule form used by spreadsheet functions is the one in the market conventions literature rather than in the continuous-time treatment.

Rust code

3 duration_fn "DURATION" (5, 6) [V "settlement", V "maturity", V "coupon", V "yld", V "frequency", V "basis"] |s, a, sv| {
    let (settlement, maturity) = (date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?);
    bond_out(s, bond::duration_kernel(settlement, maturity, num(s, a, 2)?, num(s, a, 3)?, num(s, a, 4)?, Some(or(s, a, 5, 0.0)?)))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• MDURATION — modified duration, defined as DURATION/(1 + yld/frequency). Same arguments, same errors, one extra division. If you want a price-sensitivity number, that is the one.
• PRICE — the denominator of the duration quotient is the dirty price. The two functions share their coupon schedule, their day counts and their discount ladder, so a DURATION discrepancy and a PRICE discrepancy on the same bond are usually the same bug seen twice.
• YIELD — the inverse of PRICE, and the usual source of the yld argument. Note the loop: compute yld from a market price with YIELD, feed it to DURATION, and any day-count disagreement enters twice.
• COUPNUM / COUPDAYBS / COUPDAYS / COUPDAYSNC — the schedule quantities DURATION computes internally. They are the right instruments for localizing a disagreement: if COUPDAYBS differs, the duration will differ, and you have found the day count rather than the discounting.
• ACCRINT — accrued interest, which uses the same accrual span as the fractional offset.
• Confused with: the everyday sense of "duration" as time to maturity. For a coupon bond duration is always less than time to maturity, and a reader who expects the two to match will read every answer as wrong.
• There is no convexity function in Excel. The second-order term has to be built by hand.

Named variants

No named variants are recorded yet.