DURATION function

[exc05]

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

[SlipEternal]

How about:

WorksheetFunction.Duration

[exc05]

Hi thanks for reply

yes i know , but i must write the function without using "WorksheetFunction.Duration"

[scottiex]

exc05,
I presume this is also school work.
If we do all your excel homework for you without you even showing your best attempt at creating the code - we rob you of the opportunity to learn...

[exc05]

I studied VBA for only two months, and my specialist not in vba, I only need these functions to integrate them into my project. i made a great effort to write the functions but I could not, so for this i'm asking for help now , Thanks

[SlipEternal]

If you want to integrate them into your project, then I stand by my recommendation. If you really want to create a wrapper function, how about this:

Please Login or Register  to view this content.

Now, you can use your CalcDuration function instead. It will still call the WorksheetFunction, so it will probably be more efficient than code you write yourself.

[exc05]

thank you Mr SlipEternal ,but i'm really wanted it without using WorksheetFunction

[MrShorty]

i made a great effort to write the functions but I could not

I think we could provide better help if you share some of these attempts you have tried. Explain the errors you are getting and what you expect from some of these attempts. I suspect that, if we could see some of your attempts, we would be able to see some of what you are misunderstanding and be able to help you understand why your code does not work and what changes you could make so it would work better.

[bakerman2]

Try example file.

[FDibbins]

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

[exc05]

Thank you very much for the clarification , ه asked for help only because I could not reach a solution

[exc05]

Thank you for help , but in this example the Macaulay Duration have a 6 arguments , and in excel duration function we have just 5 arguments

[bakerman2]

Maybe you can put a little effort in yourself, if it's not too much to ask.

Please Login or Register  to view this content.

[exc05]

Thank you bro , the code final

Please Login or Register  to view this content.