DURATION
TheDURATION
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.
 How to use
DURATION
formula?  Examples of using
DURATION
formula DURATION
formula not working? Similar formulas to
DURATION
Usage
Use the DURATION
formula with the syntax shown below, it has 5 required parameters and 1 optional parameter:
 settlement (required):
The settlement date of the security, represented as a date or a reference to a cell containing a date.  maturity (required):
The maturity date of the security, represented as a date or a reference to a cell containing a date.  rate (required):
The annual coupon rate of the security as a percentage, represented as a decimal or a reference to a cell containing a decimal.  yield (required):
The annual yield of the security as a percentage, represented as a decimal or a reference to a cell containing a decimal.  frequency (required):
The number of coupon payments per year, represented as a number or a reference to a cell containing a number.  day_count_convention (optional):
Optional. The day count convention to use when calculating the time between settlement and maturity. If omitted, the function uses the actual number of days between the two dates.
Examples
Here are a few example use cases that explain how to use theDURATION
formula in Google Sheets.
Calculating bond duration
Investors can use the DURATION
function to calculate the Macauley duration of a bond, which can help them to assess the sensitivity of the bond's price to changes in interest rates.
Comparing bond investments
By comparing the Macauley durations of different bonds, investors can assess which bonds are more sensitive to changes in interest rates and make more informed investment decisions.
Assessing interest rate risk
Investors can use the Macauley duration of a bond to assess the risk of changes in interest rates affecting their investment returns, and to adjust their portfolios accordingly.
Common Mistakes
DURATION
not working? Here are some common mistakes people make when using the DURATION
Google Sheets Formula:
Incorrect date format
One common mistake when using the DURATION
function is entering the settlement and maturity dates in the wrong format. Make sure the dates are entered as valid dates in a recognized format.
Missing arguments
Another common mistake is leaving out required arguments or entering them in the wrong order. Doublecheck that all required arguments are included and that they are in the correct order.
Related Formulas
The following functions are similar to DURATION
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.

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 DURATION
Google Sheets function on Google Support.