Using Yield Formula From Excel

Question

HI All,

Need your expertise to replicate the attached calculation in Alteryx, I had tried all the available Yield workflow, but does not seem to be matching with the result I m looking for.

Attached the data and the formula used in excel to calc the Yield - Cell H2

Appreciate your help,

Thank You

RP

Yield Calculation.xlsx

Jan_C_Ott · Answer

I have been hunting for this myself for some time. The best way I have found to replicate this, and I haven't finished my build yet, is to use the python tool with numpy-financial. It has rate function that will get the same yield calc as excel down to like 14 digits for coupon bonds. For zero coupon bonds you can just use the Zero coupon YTM formula (FV/PV)^(1/n)-1 where n is periods.

sethdwilson · Answer

I agree with Qiu. Excel's Yield function is quite complex. It would be a fun exercise for someone that has the time.

Here are some details of the function, which explains its methodology and iterations.

https://support.microsoft.com/en-us/office/yield-function-f5f5ca43-c4bd-434f-8bd2-ed3c9727a4fe

There are various ways you could integrate Excel into the flow. This article explains how Excel can be called from a flow using VBA.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel-Macros-VBA/ta-p/471551

Qiu · Answer

@rajeshprbh 
Maybe there is no such function in Alteryx.

How about we let the Excel do the calculation then bring up the results back to Alteryx workflow?