Manual calculation formula for Duration

Ravi_RV 

The following demonstrates a "manual" implementation of Excel DURATION when the maturity date is an annual anniversary of the settlement date (i.e. same month and day).  See the attached Excel file for details.

Formulas:

B10: =DURATION(B3,B4,B5,B6,B7,B8)

B12: =100*(SUMPRODUCT(ROW($A$1:INDEX(A:A,D12)),
          PV(C12,ROW($A$1:INDEX(A:A,D12)),0,-E12))

          + D12*PV(C12,D12,0,-1)) / B7 / F12

C12: =B6/B7

D12: =COUPNUM(B3,B4,B7,B8)

E12: =B5/B7

F12: =PRICE(B3,B4,B5,B6,100,B7,B8)

The formula in B12 is based on the description in the wiki page (click here).

ROW($A$1:INDEX(A:A,D12) produces a row array of integers {1; 2; ... ; D12},  stylistically.  It can be replaced with SEQUENCE(D12,1) in Office 365 and later versions of Excel.

t[i] (time in years) is calculated by ROW($A$1:INDEX(A:A,D12) / B7).  The factor 1/B7 is removed from SUMPRODUCT algebraically.

PV[i] (present value of each coupon payment per 100) is calculated by

PV(C12,ROW($A$1:INDEX(A:A,D12)),0,-E12).

t[n]*FV (present value of the face value payment per 100) is calculated by

D12*PV(C12,D12,0,-1).

TBD:  An "adjustment" in my implementation is needed to handle the case when the maturity date is not an annual anniversary of the settlement date.

-----

An important point to note is:  for Excel DURATION, yield (B6) is treated as a simple ("nominal") rate, whereas the wiki page suggests that it should be treated as a compound ("effective") rate.

I cannot say, with impunity, which is correct.  But the Excel DURATION calculation agrees with one online calculator (click here), which is not authoritative.

ERRATA:  My previous posting cited one description of a "manual" Excel calculation (click here).  I removed that citation because it treats yield as a compound rate, unlike Excel DURATION.