MDURATION

• How to use MDURATION formula?
• Examples of using MDURATION formula
• MDURATION formula not working?
• Similar formulas to MDURATION

Usage

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

=MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])

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

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

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.

Related Formulas

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.

Learn More

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