Buzz
This article provides a detailed guide on how to use the DURATION function to determine the period of periodic interest payment.
Description: The function returns the effective term of a bond as the weighted average of the present value of cash flows.
Syntax: DURATION(settlement, maturity, coupon, yld, frequency, [basis]).
In this context:
- settlement: Securities settlement date is the date securities are sold to the buyer after the issue date, it's a mandatory parameter.
- maturity: Securities maturity date, also known as the expiration date, is a mandatory parameter.
- coupon: Annual bond interest rate, it's a mandatory parameter.
- yld: Annual yield of the security, it's a mandatory parameter.
- frequency: Number of bond interest payments per year, it's a mandatory parameter.
- basis: Basis used to calculate the number of days, it's an optional parameter. It has the following values:
+ basis =0: Calculate days based on the US standard, where the number of days per month/year is 30/360.
+ basis =1: Number of days per month/year is the actual number of days per month/actual number of days per year.
+ basis =2: Number of days per month/year is the actual number of days per month/number of days per year is 360.
+ basis =3: Number of days per month/year is the actual number of days per month/number of days per year is 365.
+ basis =4: Calculate days based on the European standard, where the number of days per month/year is 30/360.
Note:
- If the input parameters are decimal numbers => the function takes the integer part of the parameters.
- If the settlement date and maturity date of the security are invalid => The function returns the #VALUE! error value.
- If coupon <0 and yld <0 => The function returns the #NUM error value.
- If frequency is outside the set of values {1, 2, 4} => it returns the #NUM! error value.
- If basis is outside the set of values {0, 1, 2, 3, 4} => it returns the #NUM! error value.
- If the maturity date is earlier than the settlement date => the function returns the #NUM! error value.
Example:
With the following data table, calculate the duration of periodic interest payments per year.
In the cell where you need to compute, enter the following formula: =DURATION(D6,D7,D8,D9,D10,D11).
After pressing Enter, the result is:
Thus, the bond duration with the given terms is 1.927235704.
Here's the guide to using the DURATION function.
Wishing you all success!
