MDURATION
TheMDURATION
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.
 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:
 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.  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.  rate (required):
The annual coupon rate of the security, expressed as a decimal.  yield (required):
The annual yield to maturity of the security, expressed as a decimal.  frequency (required):
The number of interest payments per year. This can be 1 for annual payments, 2 for semiannual payments, or 4 for quarterly payments.  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 theMDURATION
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 nonstandard 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.
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.