Menu
EXCEL DURATION FUNCTION
Excel DURATION Function Introduction
The DURATION function is classified as a financial function. It is useful in calculating the Macauley Duration. The function computes the duration of a security with a par value of $100 that pays interest on a periodic basis.
Portfolio Managers that utilise the vaccination approach frequently use DURATION. Aside from that, the function is important in financial modelling, specifically in projecting future cash flows of assets.
Description of Excel DURATION Function
Syntax:
=DURATION(settlement, maturity, coupon, yield, frequency, [basis])
- Settlement – The settlement date of the security or the date on which the coupon is acquired.
- Maturity – The maturity date of the securities or the date on which the coupon expires.
- Coupon rate – The coupon rate of the security.
- Yield (mandatory argument) – This is the yearly yield of the security.
- The number of coupon payments made every year is referred to as the frequency.
- The frequency for yearly payments is 1, the frequency for semiannual payments is 2, and the frequency for quarterly payments is 4,The basis is the sort of day count basis that will be utilized.
The following are some examples of base values:
Basis | Day Count Basis |
0 or omitted | Day Count Basis |
1 | Actual/actual |
2 | Actual/360 |
3 | Actual/365 |
4 | European 30/360 |
Step By Step Guide of DURATION Function
Example :
We wish to compute the bond’s duration using a 7% yearly coupon rate. The bond’s settlement date is 3/23/2010, and it has a 4% yield. Other aspects of the relationship are depicted in the image above: The formula is as follows: =DURATION (C4,C5,C6,C7,C8,C9)
Key Points
- #NUM! error – Occurs if: the given settlement date is the maturity date; or Invalid values are given for the coupon, yld, frequency, or [basis] arguments, i.e. if any of the following conditions are met: coupon 0; yld 0; frequency is not equal to 1, 2, or 4; or [basis] is supplied and is not equal to 0, 1, 2, 3, or 4).
- #VALUE! error – Occurs if any of the provided parameters is not a numeric value. One or all of the provided settlement or maturity dates are invalid in Excel.
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
