Excel MDURATION function
Function syntax and arguments
MDURATION(settlement, maturity, coupon, yld, frequency, [basis])
(1) Settlement: Required. It’s the security’s settlement date that the buyer purchased the security. It’s later than the issue date.
(2) Maturity: Required. It’s the security’ maturity date when the security expires.
(3) Coupon: Required. It’s the annual coupon rate of the security.
(4) Yld: Required. It’s the security’s annual yield.
(5) Frequency: Required. It’s the number of frequencies you can get coupon payments in one year.
- For annual coupon payments, the number of frequencies is 1;
- For semi-annual coupon payments, the number of frequencies is 2;
- For quarterly coupon payments, the number of frequencies is 4;
(5) Basis: Optional. It’s the type of day count basis to use. There are 5 types:
| Values | Description |
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Return Value
Numerical value.
The MDURATION function returns the modified Macauley duration for a security with assuming per value of $100.
Usage notes
(1) The settlement, maturity, and basis are truncated to integers. For example, you type 1.4 as the basis value, the MDURATION function will take it as 1 automatically.
(2) The MDURATION function returns #VALUE! error value, if the settlement or maturity is not a valid date.
(3) The MDURATION function returns #NUM! error value, if the settlement date is no less than the maturity date.
(4) The MDURATION function returns #NUM! error value, if the yld or coupon is less than 0.
(5) The frequency can only be one of these numbers: 1, 2, or 4. Otherwise, the MDURATION function returns #NUM! error value.
(6) The MDURATION function returns #NUM! error value, if the basis is less than 0 or greater than 4.
Formula Examples
Supposing there is a bond with an annual coupon rate of 8% and semi-annual coupon payments. The yield to maturity is 10%, the settlement date is 7/1/2017, the maturity date is 12/31/2018. This bond uses the day count basis of Actual/360.
In this case, you can figure out the Macaulay modified duration with one of below formulas:
=MDURATION(C2,C3,C4,C5,C6,C7)
=MDURATION(DATE(2017,7,1), DATE(2018,12,31),8%,10%,2,2)
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.