Menu
EXCEL MDURATION FUNCTION
Excel MDURATION Function Introduction
The Mduration function in Excel calculates the Modified Macaulay Duration of an asset that pays periodic interest and has a par value of $100.
Description of Excel MDURATION Function
Syntax :
=MDURATION( settlement, maturity, coupon, yld, frequency, [basis] )
Parameters :
- Settlement – The security’s settlement date .
- Maturity – The security’s maturity date (i.e. the date that the coupon expires).
- Coupon – The yearly coupon rate of the security.
- Yld – The security’s yearly yield.
- The number of coupon payments made every year. It has to be one of the following:
1 – Annually
2 – Semi-Annually
4 – Quarterly - Basis – An optional integer input that defines the security’s financial day count basis. Possible values include:
| Basis | Day Count Basis |
|---|---|
| 0 (or omitted) | US (NASD) 30/360 |
| 1 | actual/actual |
| 2 | actual/360 |
| 3 | actual/365 |
| 4 | European 30/360 |
Step By Step Guide of MDURATION Function
Example :
The Excel Mduration function to determine the modified Macaulay Duration of a security with a closing date of 01-Apr-2015, a maturity date of 31-Mar-2025, and a yield of 8%. The interest rate is 10%, and payments are paid quarterly. The Mduration function above returns a value of 6.540828452 years.
Key Points
- #NUM! – Occurs if the specified settlement date is a maturity date or if invalid numbers are supplied for the coupon, yld, frequency, or [base] arguments. (That is, if any of the following conditions are met: coupon 0; yld 0; frequency is not equal to 1, 2, or 4; or [basis] is given and is not equal to 0, 1, 2, 3, or 4).
- #VALUE! – Occurs if either of the provided arguments are non-numeric, or if one of the specified settlement or maturity dates is not a valid Excel date.
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
