How to use the MDURATION function in Excel office 365?
Tags: tousethemdurationfun, excel, excelfunctions
Published: 2021-08-25 09:28:50

---
title: How to use the MDURATION function in Excel office 365?
published: true
date: 2021-08-22 15:04:21 UTC
tags: ToUseTheMDURATIONFun,Excel,ExcelFunctions
canonical_url: https://geekexcel.com/how-to-use-the-mduration-function-in-excel-office-365/
---

 **MDURATION function** returns the **modified duration in years for security** for an assumed **$100 par value** with some required terms. These required terms are given as **arguments for the function**. In this article, we are going to see how to  **use the MDURATION Function In Excel Office 365.**  Let’s get into this article!! Get an official version of MS Excel from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**

## MDURATION function syntax

```
=MDURATION( settlement, maturity , rate , yld , redemption , frequency, [basis])
```

**Syntax Explanation:**

- **Settlement :**  Settlement **date of the security**.
- **Maturity :**  Maturity **date of the security**. The maturity date is the date when the security expires.
- **rate : ** The security’s **annual coupon rate**.
- **yld :**  The security’s **annual yield rate**.
- **frequency** – **payments per year** (1 = annual, 2 = semi, 4= quarterly).
- **basis ** – [optional] **Day count basis**. **Default is 0**.

## Example

- Firstly, you need to create **sample data with value and arguments**.

![](https://geekexcel.com/wp-content/uploads/2021/08/Sample-data-20.png)<figcaption>Sample data</figcaption>

- Then, you have to calculate the **MDURATION function in year**.
- Now, you need to use the following **formula** given below.

```
=MDURATION( B2 , B3 , B4 , B5 , 2 , 0 )
```

![](https://geekexcel.com/wp-content/uploads/2021/08/MDURATION.png)<figcaption>MDURATION Function</figcaption>

- After that, you have to use the same arguments to calculate the **duration function**.
- Now, you need to use the following **formula** given below.

```
=DURATION( B2 , B3 , B4 , B5 , 2 , 0 )
```

![](https://geekexcel.com/wp-content/uploads/2021/08/DURATION.png)<figcaption>DURATION formula</figcaption>

**Check this too:** [List of Excel ‘Information Functions’ in Office 365!!](https://geekexcel.com/list-of-excel-information-functions-in-office-365/)

**NOTES:**

- Excel stores **dates as serial numbers** and is used in calculation by the **function**.
- It is used to recommend to use **dates as cell reference** instead of giving **direct argument to the function**.
- You need to check the **date values** if the function returns **#VALUE! Error**.
- Then, the **security settlement date** is the date after the issue date when the security is traded to the buyer.
- Now, the arguments like **settlement date & maturity date** , **frequency & basis** are truncated to **integers**.
- After that, the function returns the **#VALUE! Error** if the **settlement date & maturity date** is not a **valid date format**.
- Finally, the function returns the **#NUM! Error** if:
  - If maturity date settlement value.
  - If the **basis argument** is **either < 0 or  > 4**
  - **Payment Frequency** argument is **not one of the three** (1, 2 or 4).
  - If **coupon rate or yield rate argument** is **< 0**
- The function generates  **#VALUE! Error** if arguments to the function is **non – numeric**.

## Closure:

This article gives you clarity on **how to use the MDURATION Function In Excel Office 365.** Is this article helpful? Don’t forget to share your valuable  **queries/suggestions**  in the below comment box & also drop your  **worthwhile feedback**.

Thanks for visiting **[Geek Excel!!](https://geekexcel.com/)** Keep Learning with us!!

**Keep Reading:**

- **[Insert Sheet in Microsoft Excel 2007?](https://geekexcel.com/insert-sheet-in-microsoft-excel-2007/)**
- **[Excel Formulas to Calculate the Time Duration With Number of Days!!](https://geekexcel.com/excel-formulas-to-calculate-the-time-duration-with-number-of-days/)**
- **[Formulas to Calculate the Annual Compound Interest Schedule!!](https://geekexcel.com/excel-formulas-calculate-annual-compound-interest-schedule/)**
- **[Excel Formulas to Calculate the Effective Annual Interest Rate!!](https://geekexcel.com/excel-formulas-to-calculate-the-effective-annual-interest-rate/)**