DURATION in WPS Spreadsheet: Macaulay Duration from Discounted Coupon Cash Flows
Calculate WPS DURATION on a regular coupon-date control and distinguish its weighted time from MDURATION.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =DURATION(DATE(2025,1,1),DATE(2027,1,1),6%,8%,2,0) has expected Macaulay duration 1.912482338028 years.
Syntax and arguments
=DURATION(settlement,maturity,coupon,yld,frequency,[basis])
settlement is the purchase settlement date and maturity the final date. coupon and yld are the annual coupon rate and annual yield. frequency is 1, 2 or 4 annual payments. basis is the optional day-count convention. The source assumes face value 100. There is no separate par, price or redemption argument.
The result is a present-value-weighted time to cash flows. The lesson’s prose mixes Macaulay and modified terminology, but its numerical example and the official MDURATION formula distinguish the two: MDURATION divides DURATION by 1+yld/frequency. Thus the weighted-time result here is Macaulay duration; do not label it modified duration.
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 |
Use =DURATION(A2,A3,A4,A5,A6,A7). Assume four remaining regular half-year coupons and settlement exactly on the January coupon date. Future cash flows per 100 are 3, 3, 3 and 103 at times 0.5, 1, 1.5 and 2 years. Discount them at 4% per half-year. Their present values sum to 96.3701047757431.
Multiply each present value by its time in years, sum those products and divide by that price. The independent result is 1.91248233802787. Earlier coupons pull the weighted average below the two-year maturity. The result is in years, not in half-year periods or a percentage.
Advanced comparison and source reconciliation
For the same inputs, dividing the result by 1+0.08/2=1.04 gives modified duration 1.83892532502679. The official MDURATION lesson explicitly prints that relationship in its formula illustration. Keep the two measures separately labeled.
A longer independent control reproduces the DURATION lesson’s published data: settlement July 1, 2018; maturity January 1, 2048; coupon 8%; yield 9%; semiannual payments; basis 1. There are 59 regular half-year payments, and their present-value-weighted time is 10.919145281592, matching the source’s displayed 10.9191453. Dividing by 1.045 instead gives about 10.448942853198, confirming that the published DURATION number is the undivided weighted time despite imprecise prose.
Errors and troubleshooting
The lesson documents #VALUE! for invalid dates, and #NUM! for negative coupon or yield, frequency outside 1/2/4, basis outside 0–4, or settlement on/after maturity. Dates, frequency and basis are truncated according to the source. Use integer frequency/basis inputs. Keep annual rate units consistent: do not halve the yield before passing it to a function that already receives frequency.
These examples use regular coupon dates. They do not establish an odd-coupon algorithm, exact current-build floating-point precision or a full between-coupon reconstruction. Duration is a weighted timing measure; it is not the number of calendar years remaining or an exact forecast of a future price change.
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 DURATION 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.