Use the DURATION function to calculate the Macauley duration for an assumed parity value of 100 monetary units.
The Macauley duration is the weighted average maturity of cash flows. That is, the weighted average distance to payment. It is used to measure a bond price's response to changes in yield.
A higher Macauley duration value indicates a riskier investment.
The Macauley duration is calculated with the following formula:
Where:
 C is coupon;
 y is yield;
 F is face value;
 P is price, inclusive of accrued interest; and
 T is number of periods.
Syntax
DURATION(settlement, maturity, rate, yield, frequency[, basis])
The DURATION function has the following arguments:
Argument  Data type  Description 
settlement (required)  Date  The bond settlement date — the date the bond is traded to the buyer. 
maturity (required)  Date  The bond maturity date — the date when the bond expires. 
rate (required)  Number  The bond annual coupon rate. 
yield (required)  Number  The bond annual yield. 
frequency (required)  Number 
The number of coupon payments per year. Enter:

basis (optional)  Number 
The basis determines how many days exist in a year. A full year has:
US 30/360 is the default basis for DURATION. It can also be specified by entering 0. To use a different type of day count basis, enter:
Learn about the conventions used to calculate the day count for basis. 
Returns 
Number 
Constraints
The DURATION function has the following constraints:
 the settlement and maturity dates must be valid dates between 01/01/1900 and 12/31/2399;
 the maturity date must be later than the settlement date;
 the rate and yield must be positive or zero;
 the frequency must be either 1 (annual), 2 (semiannual), or 4 (quarterly); and
 the basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).
Examples
The following tables show some example formulas using the DURATION function.
You can reference line items or list properties in your formula.
Formula  Description  Result 
DURATION(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.1, 1, 4) 
This example shows a Macauley duration calculation that specifies a basis. The basis is given as 4 (European 30/360). The example has:

2.6976811 
DURATION(DATE(2015, 1, 15), DATE(2018, 1, 15), 0.12, 0.1, 4) 
In this example, the Macauley duration is calculated without specifying a basis. As a result, the basis defaults to US 30/360. Here:

2.5760086 