Hi, may I ask if there are any functionality in Alteryx for calculate modified duration.
https://www.investopedia.com/terms/m/modifiedduration.asp
yes, I have coupon rate, yield, maturity date and start date
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 ?
Start Date | Maturity Date | Coupon (%) | Yield (%) |
30-Jun-20 | 5-Aug-21 | 6.5 | 3.8 |
30-Jun-20 | 30-May-21 | 6.4 | 3.7 |
30-Jun-20 | 30-Apr-21 | 6.6 | 3.6 |
Thanks. This is my data set
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.
Pedro.
@pedrodrfaria
Nice work and you must be really good at Math.😁
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
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?
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 ?
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.