MDURATION in WPS Spreadsheet: Modified Duration with an Explicit Yield Convention
Use WPS MDURATION and independently verify its relationship to Macaulay duration for regular semiannual coupons.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =MDURATION(DATE(2025,1,1),DATE(2027,1,1),6%,8%,2,0) has expected modified duration 1.838925325027 under the supplied annual-yield convention.
Syntax and arguments
=MDURATION(settlement,maturity,coupon,yld,frequency,[basis])
settlement is the settlement date; maturity is the last payment date; coupon is the annual coupon rate; yld is annual yield; frequency is 1, 2 or 4 annual payments; and basis is the optional day-count convention. The source assumes face value 100 and provides no separate redemption argument.
The defining first-party formula illustration is MDURATION = DURATION / (1 + market yield / coupon payments per year). It resolves the lesson’s imprecise introductory use of Macaulay terminology: the modified result is the weighted-time measure after that division.
Complete worksheet example
| Cell | Value | Meaning |
|---|---|---|
| A2 | =DATE(2025,1,1) |
Settlement |
| A3 | =DATE(2027,1,1) |
Maturity |
| A4 | 6% |
Annual coupon |
| A5 | 8% |
Annual yield |
| A6 | 2 |
Semiannual payments |
| A7 | 0 |
US 30/360 |
Enter =MDURATION(A2,A3,A4,A5,A6,A7) in B2. Assume settlement on a regular January coupon date with four remaining half-year payments. Coupons are 3 per 100, with a final 103 including redemption. Discounting at 8%/2=4% gives a price of 96.3701047757431. The present-value-weighted time is 1.91248233802787 years.
Applying the documented conversion gives 1.91248233802787/1.04 = 1.83892532502679. This independent arithmetic is the expected output, not a measured WPS runtime result. Keep it as a number, not a percentage; the yield input is the percentage.
Advanced source control and sensitivity interpretation
The official example uses January 1, 2008 to January 1, 2016, coupon 8%, yield 9%, semiannual frequency and actual/actual basis. Independently discounting its 16 regular half-year cash flows gives Macaulay duration 5.99377495554519. Division by 1+0.09/2 gives 5.73566981391884, matching the lesson’s displayed 5.736.
For the two-year control, modified duration implies a local price sensitivity of approximately -1.83892532502679*0.001 = -0.00183892532503, or −0.183892532503%, for a 0.001 increase in the annual yield under the same fixed-cash-flow model. This is a first-order mathematical approximation, not an exact repricing result. A 0.001 rate change is 0.1 percentage point, not 1 percentage point. Larger changes need full repricing because the price-yield relationship is curved.
Errors, conventions and boundaries
The source documents #VALUE! for invalid dates, and #NUM! for negative coupon or yield, frequency other than 1/2/4, basis outside 0–4, or settlement on/after maturity. It says date, frequency and basis inputs are truncated to integers. Use genuine date cells and integer controls.
Do not divide the input annual yield by frequency before entering it; the documented denominator already performs that adjustment. Do not report the Macaulay number as the modified result. These controls assume regular coupon schedules and do not establish odd-coupon handling, all between-coupon details, or exact runtime precision. The source’s formula and displayed example support the distinction without requiring an Excel compatibility assumption.
Dates, rates and basis
Enter dates with DATE(year,month,day) or references to genuine date cells, rather than locale-dependent text. Rates are annual decimals: enter 6% or 0.06, not 6. Use the same currency throughout; these functions do not convert currencies.
The official basis table lists 0 (or omitted) as US NASD 30/360, 1 as actual/actual, 2 as actual/360, 3 as actual/365, and 4 as European 30/360. The examples specify basis explicitly. The names alone do not define every February, month-end or multi-year adjustment; this guide does not generalize its controls to those edge cases.
Evidence and version scope
The official WPS Academy MDURATION lesson labels its applicable edition WPS Office For Windows Personal (个人版). The lesson and relevant formula/table illustrations were reviewed on October 19, 2026. This establishes a Chinese first-party documented scope, not English-global, Linux, macOS, mobile or all-edition availability. The numeric build, introduction release and last supported version are not established. The lesson does not identify the function as deprecated.
Examples above are independent mathematical expectations under the stated assumptions. They were not executed in WPS, and no current application screenshot or runtime precision guarantee is supplied. Documentation images establish the written contract, not a new native test. English function tokens and comma separators follow the source; verify localized entry and workbook interchange in the intended client.