MDURATION Function Examples – Excel, VBA, & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to use the Excel MDURATION Function in Excel to calculate the modified Macaulay duration of security.
MDURATION Function Overview
The MDURATION Function Calculates the annual duration of a security.
To use the MDURATION Excel Worksheet Function, select a cell and type:
(Notice how the formula inputs appear)
MDURATION Function Syntax and Inputs:
=MDURATION(settlement,maturity,coupon,yld,frequency,[basis])settlement – It’s the settlement date of the security or the date at which the security is purchased. It’s the date that comes after the issuing date of the security.
maturity – It’s the date at which the bond or security expires, and the principal amount is paid back to bond or security holder.
coupon – The annual coupon rate of the security.
yld – It’s the annual yield of bond or security.
frequency – It refers to the number of periodic coupon payments per year. The value of frequency for annual, semi-annual, and quarterly payments are 1, 2, and 4, respectively.
basis – OPTIONAL: It specifies the type of day counting to be used by the security or bond. Possible values can be:
| Basis | Day Count |
| 0 | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Acutal/360 |
| 3 | Acutal/365 |
| 4 | European 30/360 |
If the basis argument is omitted, it assumes its default value i.e. US (NASD) 30/360.
What Is MDURATION?
Modified duration is an extension of Macaulay duration, which measures the sensitivity of the bond prices to changes in its yield. Modified duration is based on the concept that the yield and bond prices movies in opposite directions.
The Modified Duration is calculated using the following equation:
MDURATION = Duration/(1+(market_yield/coupon_payments_per_year))What is the Excel MDURATION Function?
The Excel MDURATION function calculates the Modified Macaulay Duration of a bond or security that pays interest periodically and assuming a par value of $100.
Modified Macaulay Duration of a bond
In this example, we want to calculate the Modified Macaulay Duration of bond with an annual coupon rate of 7%. Other details of the bond are in the above table.
The formula used for the calculation is:
=MDURATION(C4,C5,C6,C7,C8,C9)
The Excel MDuration function returns the value of
MDURATION = 7.41 years
Modified Macaulay Duration of a fixed-income security
Let’s take a look at another example, here we are going to find out the duration of fixed-income security until it is paid back. Other details of the fixed-income security are mentioned in the above figure.
The formula to use is:
=MDURATION(C4,C5,C6,C7,C8,C9)
We get the following result:
MDURATION = 3.98 years.
Additional Notes
#NUM! Error occurs if the settlement date is greater or equal than the maturity date; or the values of the rate, yld, redemption, frequency, or [basis] arguments are not valid numbers (i.e. rate < 0; or yld < 0; or redemption ≤ 0; or frequency is any value other than 1, 2 or 4; or [basis] value is other than 0, 1, 2, 3, or 4)
#VALUE! Error occurs if the dates of the settlement or the maturity arguments are not valid Excel dates.
It is recommended that the settlement and maturity dates should be entered in the DURATION function as references to cells containing the dates or dates returned from formulas.
Return to the List of all Functions in Excel
MDURATION in Google Sheets
All of the above examples work exactly the same in Google Sheets as in Excel.
MDURATION Examples in VBA
You can also use the MDURATION function in VBA. Type:
application.worksheetfunction.mduration(settlement,maturity,coupon,yld,frequency,basis)For the function arguments (rate, etc.), you can either enter them directly into the function or define variables to use instead.
