MDURATION Excel Formula

Author Zaheer    Category Formulas     Tags ,

As the technology develops, tasks are even more becoming complicated. Fortunately, along with the rapid progress of technology is the development of new methods to finish any kind of task quickly. These methods help a lot of people to produce, organize, manipulate and analyze data. One of the most useful advancements in technology is Microsoft Excel which is skillfully created and designed by the Microsoft Company. This software is extensively used particularly in the field of business. Excel features lots of functions that are helpful for financing and accounting. Among these functions is the MDURATION function which will be discussed in this article. If you find the topic interesting, read the discussion below.

Overview of the MDURATION Function in Microsoft Excel

The MDURATION function in Excel is used to compute for the modified Macaulay Duration of security investment that pays periodic interest. The function assumes a par value of $100. The formula of the MDURATION function is:

MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

The arguments in this formula are the following:

  • Settlement – This is a required argument and it is the security’s settlement date (ie. The date of purchase of the coupon)
  • Maturity – This is argument is required and it is the security’s maturity date (ie. The date of expiration of the coupon)
  • Coupon – this is a required argument and it is the annual coupon rate of the security.
  • Yld – This argument refers to the annual yield of the security
  • Frequency – This argument indicates the number of payments per year for the coupon. The possible values of this argument are the following:
    •  Annually
    •  Semi-annually
    •   Quarterly
  • [basis] – This argument is optional. It defines the day count basis that will be used in the calculation. The possible values of this argument and their meaning are the following:

0 (or omitted) – US (NASD) 30/360

  •  actual/actual
  •  actual/360
  •  actual/365
  •  European 30/360

You have to take note that the date arguments can be supplied in the formula as either dates that are returned from other functions or formulas or cell references which contain the dates.

Remarks:

  • If the date arguments are supplied as text, Excel may interpret them in a different way, depending on the settings of date interpretation and date system on your computer.
  • Even though you can supply these dates as serial numbers, this is not advisable because serial numbers differ across various computer systems.

Example on How to Use the MDURATION Function

For instance, the security has a yield of 8%, the date of maturity is 31-Mar-2020 and the date of settlement is 01-Apr-2010. The payments for the security are made quarterly with a coupon rate of 10%. Always remember that you can supply the dates as cell references which contain the dates. So, you can type in the dates in any cell. For example, the settlement date is in cell B1 and the maturity date is in cell B2.

You can use this formula, =MDURATION(B1, B2, 10%, 8%, 4). After supplying all the required values in the formula, the function will return the result which is 6.540828452. It means that the modified Macaulay Duration is 6.540828452 years.

It is recommended by Microsoft to supply the date arguments as cell references as you can see in the example. Moreover, the above example shows that the [basis] argument is omitted. Therefore, the function will automatically use the default value of 0 defining the US (NASD) 30/360 day count basis.

Post comment