RATE function misbehaves

[mrfullset]

Description

RATE function misbehaves for some valid inputs.

Examples:

• input: (300, -465.96, 100000), expected: ~0.25%, got: #NUM!
• input: (200, -500, 200000), expected: ~-0.62%, got: #NUM!

Steps to reproduce

const hf = HyperFormula.buildFromArray([['=RATE(200, -500, 200000)']], { licenseKey: 'gpl-v3' });
console.log(hf.getCellValue({ col: 0, row: 0, sheet: 0 }));

Your environment

MB Pro 2016
macOS 12.2
node v14.17.4
npm v6.14.14

• HyperFormula version: 13.3.1
• Browser Name and version: Chrome 98.0.4758.80 x86_64
• Operating System: macOS 12.2

Links

https://gist.github.com/kucukharf/677d8f21660efaa33f72 -- this algorithm works ok. maybe it's ok as a reference for you.

[sequba]

Hi @mrfullset-yolk!

I investigated your issue. In fact our algorithm is able to compute the correct values for these inputs. The problem is, it takes too many iterations. We'll see what we can do to make it converge faster.

https://gist.github.com/kucukharf/677d8f21660efaa33f72 -- this algorithm works ok. maybe it's ok as a reference for you.

How did you verify that kucukharf's implementation works correctly? Could you share your code? It might be helpful for us.

[mrfullset]

Hi @sequba,

I've verified that algorithm with provided examples in the issue. kucukharf's implementation returns correct values, which are the same with MS Excel. Currently I'm using his implementation in my project and it works ok.

The only code I had is also provided in the description. It was a usual React functional component with only those 2 rows inside.

[sequba]

Thanks. In the meantime I managed to fix our implementation and it will be included in the upcoming release of HF: #933

[AMBudnik]

Hi @mrfullset-yolk I'm just here to share the good news. HyperFormula v2 is already online with @sequba 's fix for this issue.

Here #964 you can read more about the changes within that version.