MDURATIONfunction 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
- Examples of using
MDURATIONformula not working?
- Similar formulas to
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 semi-annual 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.
ExamplesHere are a few example use cases that explain how to use the
MDURATIONformula 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
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.
MDURATIONnot working? Here are some common mistakes people make when using the
MDURATIONGoogle 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:
PRICEfunction 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.
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.
DURATIONfunction 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.
COUPNUMformula 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.
COUPDAYBSfunction 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.