Hi all

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

DURATION(settlement, maturity, coupon, yld, frequency, [basis])

it is usually calculated by excel ; for example

B7=DURATION(B1,B2,B3,B4,B5,B6)

IePum.png


but what i am trying to do , is to customize Duration function with vba editor

2017-07-04_202126.png

i think Excel uses this formula as explained in Wikipedia for calculation Macauley duration.

ol1oM.png

the problem is i find difficulty to write this function in vba to calculate Duration like Excel