FinanceRate Difference Result between Excel and Alteryx

[petershen517]

I used the exact same inputs to calculate Rate using Excel and Alteryx, but I got different answers (Difference is under Col I in Excel Attached). Anyone is the community had similar issue?

Alteryx Developers: do you know why there is a difference? and how to reconcile between Alteryx and Excel?

Thank you.

[jamielaird]

Hi @petershen517 

My first instinct is that there's a difference in precision between Excel and Alteryx that is causing these discrepancies.

On the face of it both store double-precision floating-point values using 8 bytes with 15 digits precision, however a quick visit to Google throws up some results that suggest there are some limitations in the Excel approach that might result in an inaccurate result:

https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision...

https://support.microsoft.com/en-gb/help/78113/floating-point-arithmetic-may-give-inaccurate-results...

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

[petershen517]

Hi jamielaird,

Thank you for the fast response. I rounded to 6 decimals under both Excel and Alteryx, but the difference starts from the third decimal, so I don't think this is due to rounding or precision. Per the link in your reply "In the case of Excel, although Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it can only do so within 15 digits of precision. This limitation is a direct result of strictly following the IEEE 754 specification and is not a limitation of Excel. This level of precision is found in other spreadsheet programs as well."

[TeresaZhang7517]

Hi,

I am a newbie. And I have a same issue, do you have any solutions? Need your help.

[JessieC]

@TeresaZhang7517 - solution would be to calculate it with the formula for future value = present value x (1+ interest rate)^n  

In this formula, the superscript n refers to the number of interest-compounding periods that will occur during the time period you're calculating for.

https://www.business.org/finance/investing/how-to-calculate-future-value-of-an-investment/

In Alteryx, create column in Formula where Future Value = [Present Value]*POW(1+[interest rate], n)