Zoho Sheet DURATION Function: Macaulay Weights and Coupon Timing

The official DURATION reference was retrieved October 15, 2026. The original introduction release and last functioning version are unknown from the checked sources. Current web documentation does not establish every older, mobile or offline client’s behavior.

No native Zoho Sheet session was executed. Worked values are independent controls from complete fixtures, not screenshots or claims of live app output. Examples use the help’s semicolon formula notation; verify the actual editor’s accepted separator.

DURATION(settlement; maturity; rate; yield; frequency; [basis])

The dates describe settlement and maturity. rate is the annual coupon rate and yield is the annual yield used for discounting. frequency is 1, 2 or 4 payments per year.

Optional basis defaults to 0: US/NASD 30/360. The other modes are 1 actual/actual, 2 actual/360, 3 actual/365 and 4 European 30/360. The worked examples settle on a regular coupon boundary with ordinary first-of-year dates and explicitly specify basis 0.

Zoho’s illustrative argument values include unusually large bare numbers. This guide supplies unambiguous percentages such as 5% and 6%, rather than assuming those bare examples imply a special percentage scale.

Use settlement January 1, 2026, maturity January 1, 2029, annual coupon 5%, annual yield 6%, frequency 1 and basis 0.

=DURATION(DATE(2026;1;1);DATE(2029;1;1);5%;6%;1;0)

The independent Macaulay-model target is 2.8573474353 years. Normalize face value to 100 for the calculation; the annual cash flows are 5, 5 and 105.

The total discounted value is 97.3269880505. Multiply each discounted value by its time and add: the weighted numerator is 278.0970196874. Dividing numerator by total value gives the duration target.

This is an explicit independent model of the named Macaulay measure. The official embedded examples do not expose their full input cells, so their small printed results are not reverse-engineered into invented datasets.

The three present-value weights are approximately 0.0484652944, 0.0457219759 and 0.9058127297. Their full-precision sum is one. Multiplying them by times 1, 2 and 3 reproduces the weighted average.

Because all three cash flows and discount factors are positive, the weighted average lies between the earliest and latest payment times, 1 and 3 years. It is below three because some value arrives in the first two years.

Keep the dates, 6% yield, annual frequency and basis fixed. With coupon rate 2%, the independent duration is 2.9378154168 years. With 5%, it is 2.8573474353. With 10%, it is 2.7491428745.

Higher coupons shift more present-value weight toward earlier payments in these fixtures. This is a comparison of positive-rate models; zero-coupon and negative-rate native behavior is not inferred from the help’s generic error table.

For the original 5% coupon with annual payments, yields 4%, 6% and 8% give independent durations 2.8614628745, 2.8573474353 and 2.8531901714 years. The cash flows remain fixed while their relative present-value weights change.

Changing instead to semiannual payments creates six cash flows at 0.5, 1, 1.5, 2, 2.5 and 3 years. Coupons are 2.5 per half-year and the final cash flow is 102.5. Discounting at 6%/2 per period gives independent duration 2.8200025926 years.

Do not return the raw weighted number of half-year periods as though it were years. The time units in the weighted numerator must match the units used to label the result.

Doubling the normalized face amount from 100 to 200 doubles every coupon, discounted value and the weighted numerator. Their ratio, duration, is unchanged. This helps explain why the function’s signature does not require a face-value argument.

Macaulay duration is also not itself a guaranteed percentage price loss for a one-percentage-point yield move. A price-sensitivity approximation requires a specifically defined modified measure and assumptions; this guide does not silently substitute that interpretation.

Zoho specifically lists #NUM! for a frequency outside 1, 2 or 4, a basis outside 0–4, and settlement after maturity. Its wording says after, not an independently verified rule for equal dates. Equal dates, fractional modes, invalid rates and unusual coupon dates were not executed.

If a result is unexpected, check percentage scale, frequency, date order and whether time was measured in years or coupon periods. The generic lookup-error text is not treated as a special DURATION rule. Keep the fully stated ordinary-date controls separate from unsupported boundary behavior.