# MDURATION

The `MDURATION` function calculates the modified duration of a security paying periodic interest, such as a US Treasury Bond. Modified duration is a measure of the sensitivity of a bond's price to changes in interest rates. It is most commonly used to estimate the potential volatility of a bond. The function returns a decimal value representing the number of years until the bond's cash flows would repay the price paid for the bond, taking into account that interest is reinvested at the yield to maturity rate.

## Usage

Use the `MDURATION` formula with the syntax shown below, it has 5 required parameters and 1 optional parameter:

Parameters:
1. settlement (required):
The settlement date of the security, which is the date that the buyer takes possession of the security and the seller receives payment.
2. maturity (required):
The maturity date of the security, which is the date when the security's owner will receive repayment of the security's principal amount.
3. rate (required):
The annual coupon rate of the security, expressed as a decimal.
4. yield (required):
The annual yield to maturity of the security, expressed as a decimal.
5. frequency (required):
The number of interest payments per year. This can be 1 for annual payments, 2 for semi-annual payments, or 4 for quarterly payments.
6. day_count_convention (optional):
Optional. The day count convention used for calculating the time between settlement and maturity. The default value is 0, which indicates the US (NASD) 30/360 day count convention. Other valid values include 1 for actual/actual, 2 for actual/360, and 3 for actual/365.

## Examples

Here are a few example use cases that explain how to use the `MDURATION` formula in Google Sheets.

### Estimating bond price sensitivity to interest rate changes

By calculating the modified duration of a bond using the `MDURATION` function, investors can estimate how the bond's price will change in response to changes in interest rates. A higher modified duration indicates a greater sensitivity to interest rate changes and therefore a higher potential for price volatility.

### Comparing bond investments

Investors can use the modified duration calculated using the `MDURATION` function to compare the price sensitivity to interest rate changes of different bonds with different coupon rates, yields to maturity, and maturities. This can help investors make informed decisions about which bonds to invest in based on their risk tolerance and investment goals.

### Valuing a bond with irregular cash flows

Investors can use the `MDURATION` function in combination with other financial functions, such as `PV` and `IRR`, to value bonds with non-standard cash flows. By calculating the modified duration, investors can estimate the bond's price sensitivity to interest rate changes and use this information to discount future cash flows to their present value.

## Common Mistakes

`MDURATION` not working? Here are some common mistakes people make when using the `MDURATION` Google Sheets Formula:

### Incorrect order of arguments

Make sure to input the arguments in the correct order as specified in the syntax. For example, settlement should come before maturity.

### Using incorrect date format

Ensure that the settlement and maturity arguments are in date format, as the formula requires them to be. You can format the cells as dates or use the DATE function to specify the dates.

### Not specifying all required arguments

Make sure to input all of the required arguments for the formula. settlement, maturity, rate, yield, and frequency are all required, while day_count_convention is optional.

### Using incorrect frequency

Ensure that the frequency argument is set to the correct value for your calculation. For example, if payments are made annually, frequency should be set to 1.

### Incorrect use of day_count_convention

If you choose to use the optional day_count_convention argument, ensure that you are inputting the correct value for your calculation. Refer to the documentation for a list of acceptable values.

The following functions are similar to `MDURATION` or are often used with it in a formula:

• `PRICE`

The `PRICE` function calculates the price per \$100 face value of a security that pays periodic interest. It is commonly used to determine the current value of a bond. The function takes the settlement date, maturity date, annual coupon rate, yield, redemption value, and frequency of coupon payments as input. It returns the price of the security, which is the sum of the present value of the coupon payments and the present value of the redemption value.

• `YIELD`

The YIELD function calculates the yield of a security that pays periodic interest. The yield is the annualized percentage rate returned on the bond, assuming the bond is held until maturity. This function is commonly used in finance and investment analysis.

• `DURATION`

The `DURATION` function calculates the Macauley duration of a security paying periodic interest, such as a US Treasury Bond, based on expected yield. The Macauley duration is a measure of the sensitivity of the price of the security to changes in interest rates. This function is commonly used in finance and investment analysis.

• `COUPNUM`

The `COUPNUM` formula calculates the number of coupons payable between the settlement date and maturity date of a security. This formula is commonly used in financial analysis to determine the amount of interest income earned on a bond or other fixed income security.

• `COUPDAYBS`

The `COUPDAYBS` function calculates the number of days from the beginning of the coupon period to the settlement date for a security that pays periodic interest. It is commonly used in financial calculations to determine the accrued interest between the last coupon payment and the settlement date.

You can learn more about the `MDURATION` Google Sheets function on Google Support.