ExcelFlux

DURATION function

the annual duration of a security with periodic interest payments.

Financial

Where can you use it?

Budgeting, treasury and finance teams. When reviewing a scenario with cash flows, payment periods and rate assumptions, compare the input and output units in the example.

Scenario in the workbook

One payment remaining in one year gives a Macaulay duration of one year. Multiple payments change the weights.

A worked example from the workbook

RowAB
6InputSecond input
70.050.05
80.060.07
90.080.09
Record 1
=DURATION(DATE(2026,1,1),DATE(2027,1,1),A7,B7,1,3)

Expected result1

See the inputs and instructions for all 3 examples

Copyable formulas

English Excel · en-US
=DURATION(DATE(2026,1,1),DATE(2027,1,1),A7,B7,1,3)
Turkish Excel · tr-TR
=SÜRE(TARİH(2026;1;1);TARİH(2027;1;1);A7;B7;1;3)

Worked examples

One payment remaining in one year gives a Macaulay duration of one year. Multiple payments change the weights.

Use the blue inputs in the table starting at A6. F7:F9 calculate results and G7:G9 retain the original values for comparison.

Row A B
6 Input Second input
7 0.05 0.05
8 0.06 0.07
9 0.08 0.09
Trial Expression Sample output
Record 1 =DURATION(DATE(2026,1,1),DATE(2027,1,1),A7,B7,1,3) 1
Record 2 =DURATION(DATE(2026,1,1),DATE(2027,1,1),A8,B8,1,3) 1
Record 3 =DURATION(DATE(2026,1,1),DATE(2027,1,1),A9,B9,1,3) 1
Microsoft Excel reference