Function duration

Calculates the Macauley duration for an assumed par value of $100. Duration is defined as the weighted average of the present value of cash flows, and is used as a measure of a bond's price sensitivity to changes in yield.

Remarks:

• The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires.
• settlement, maturity, frequency, and basis are truncated to integers.
• If settlement or maturity is not a valid date, an error is thrown.
• If coupon < 0 or if yld < 0, an error is thrown.
• If frequency is any number other than 1, 2, or 4, an error is thrown.
• If basis < 0 or if basis > 4, an error is thrown.
• If settlement >= maturity, an error is thrown.

The duration is calculated as:

When N > 1:

DURATION = (1 / PRICE) * [
  SUM(k=1 to N) (100 * rate/frequency) / (1 + yld/frequency)^(DSC/E + k-1)
    * (DSC/E + k-1)
  + redemption / (1 + yld/frequency)^(DSC/E + N-1) * (DSC/E + N-1)
]
Copy

When N = 1:

DURATION = (1 / PRICE) * [
  (100 * rate/frequency + redemption) / (1 + yld/frequency * DSC/E)
    * (DSC/E)
]
Copy