MDURATION in Zoho Sheet: Syntax and Worked Examples

MDURATION measures the sensitivity of a security’s price to its annual yield within a regular coupon-discounting model. It returns modified duration in years, not a currency price or a percentage return.

For a bond with one annual payment remaining, annual yield 5%, settlement January 15, 2026 and maturity January 15, 2027, the independent expected result is 1/1.05 = 0.9523809524 years. The examples below derive their numerical expectations independently; they have not been executed in Zoho Sheet.

=MDURATION(settlement; maturity; rate; yield; frequency; [basis])

Use actual date values for settlement and maturity. Rate is the annual coupon rate; yield is the annual yield. Enter 5% as 0.05 or 5%, not as 5. Frequency is payments per year: 1, 2 or 4.

Basis defaults to 0. Zoho lists 0 for US/NASD 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365 and 4 for European 30/360. All numerical examples here explicitly use basis 0 and dates on regular coupon boundaries. This avoids relying on undocumented handling of partial coupon periods or unusual schedules. The official MDURATION reference supplies the argument contract and numeric-error conditions.

Enter this complete setup:

Then enter =MDURATION(A2;B2;C2;D2;E2;F2). A self-contained version is:

=MDURATION(DATE(2026;1;15);DATE(2027;1;15);0.06;0.05;1;0)

To check the mathematics, use a nominal principal of 100. The sole future payment is 106 at year 1. Its present value is 106/1.05, so its present-value-weighted payment time is exactly one year. Modified duration divides that Macaulay duration by 1 + yield/frequency: 1/1.05 = 0.9523809524. The arbitrary principal cancels; it is not an omitted function argument.

One-payment controls at other frequencies clarify the annualization:

Each row uses basis 0. For example, the first row is =MDURATION(DATE(2026;1;15);DATE(2026;7;15);0.06;0.04;2;0). The half-year payment time is divided by 1.02; dividing a full year by 1.04 would mix different conventions.

Use settlement January 15, 2026, maturity January 15, 2028, coupon 5%, yield 5%, annual payments and basis 0:

=MDURATION(DATE(2026;1;15);DATE(2028;1;15);0.05;0.05;1;0)

With principal 100, payments are 5 after one year and 105 after two. Independently:

• Present values are 5/1.05 = 4.7619047619 and 105/1.05^2 = 95.2380952381.
• Their total price is 100.
• Macaulay duration is (1×4.7619047619 + 2×95.2380952381)/100 = 1.9523809524 years.
• Modified duration is 1.9523809524/1.05 = 1.8594104308 years.

For a small absolute yield change, the local mathematical approximation is relative price change ≈ −modified duration × yield change. An increase from 5% to 5.1% is a change of 0.001, giving approximately −0.00185941, or −0.185941%. It is a first-order model calculation, not a guaranteed realized price move. Larger changes need actual repricing; changing cash flows or irregular terms can invalidate this simple model. This example teaches the function rather than recommending a security or transaction.

Zoho explicitly documents #NUM! for a frequency other than 1, 2 or 4, a basis outside 0–4, or settlement after maturity. For example, replacing frequency 1 with 3 in the two-date example above meets a documented error condition. Do not infer the result for settlement equal to maturity from the after-maturity rule.

The public reference does not establish every negative-rate, fractional-frequency, coercion or irregular-coupon boundary. Use clean numeric inputs and verify those cases separately before applying them. Formatting a duration result as a percentage would also be misleading: 0.95238 is a time-based sensitivity measure, not a 95.238% yield.

DURATION is related but has a different result: Macaulay duration has not yet been divided by the periodic-yield adjustment. DATE constructs unambiguous inputs. YEARFRAC helps explain day-count conventions, but substituting a single year fraction for a coupon cash-flow calculation is not generally equivalent.

The rolling Zoho Sheet help was checked on October 18, 2026. It does not identify the function’s first release, so its introduction version is unknown here. No retirement or deprecation is established by that page, and this guide does not establish mobile or imported-workbook parity. All results in this guide are documentation-derived or independently calculated expectations; none was obtained from the Zoho application or calculation engine.