Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Modified duration functionality

hoiki426
6 - Meteoroid

Hi, may I ask if there are any functionality in Alteryx for calculate modified duration.

 

https://www.investopedia.com/terms/m/modifiedduration.asp

 

9 REPLIES 9
Jean-Balteryx
16 - Nebula
16 - Nebula

Hi @hoiki426 ,

 

In your data, do you have all information needed to compute the Modified Duration ?

hoiki426
6 - Meteoroid

yes, I have coupon rate, yield, maturity date and start date

Jean-Balteryx
16 - Nebula
16 - Nebula

Then using a formula tool with your data should be possible.

 

Do you have a mockup dataset so that I could show you with a workflow ?

hoiki426
6 - Meteoroid
Start DateMaturity DateCoupon (%)Yield (%)
30-Jun-205-Aug-216.53.8
30-Jun-2030-May-216.43.7
30-Jun-2030-Apr-216.63.6

 

Thanks. This is my data set

pedrodrfaria
13 - Pulsar

HI @hoiki426 

 

I attached an example of a calculation for the modified duration. This should give you a good understanding of how to do it. I used the provided example from Investopedia to build this out.

 

pedrodrfaria_0-1614167848877.png

 

pedrodrfaria_1-1614167858167.png

 

Pedro.

 

 

Qiu
20 - Arcturus
20 - Arcturus

@pedrodrfaria 
Nice work and you must be really good at Math.😁

hoiki426
6 - Meteoroid

COB Date    MATURITY DATE    COUPON   FINAL_YIELD

30-Jun-20    5-Aug-20                    6.55            3.85

30-Jun-20    30-Jun-21                   6.55            3.85

30-Jun-20    5-Aug-21                    6.55            3.85

30-Jun-20    30-May-21                  6.55            3.85

30-Jun-20    30-Apr-21                   6.55            3.85

 

@pedrodrfaria @Jean-Balteryx 

Thanks for the reply. It seems that the workflow above is with fixed payment period (i.e., 3).

If I have a data set above, may I seek your help how shall we construct general formula to calculate modified duration?

Jean-Balteryx
16 - Nebula
16 - Nebula

@hoiki426 ,

 

In your data, is it :

COUPON = number of coupons per year

FINAL_YIELD = Yield to maturity

 

And how is computed Macauley duration ? Is it the difference between both dates ?

wenkai
5 - Atom

Was facing similar issue to solve MDuration in Alteryx. Found the below post which shows the calculation done in a single Excel formula. May refer here to build a similar expression in Alteryx to calculate MDuration.

 

https://laptrinhx.com/macaulay-duration-formula-3586465511/

 

Need to slightly tweak the formula shown on the page to incorporate your COB Date and MATURITY DATE to derive at the Maturity Time. Simply use DATETIMEDIFF function calculated in "days", then divide by 365 to get the number of years with decimal.

 

 

Labels