[gamegoblin]

I work on an Excel-compatible spreadsheet startup (rowzero.com) and had to implement these.

One tricky part is RATE involves zero-finding with an initial guess. The syntax is:

RATE(nper, pmt, pv, [fv], [type], [guess])

Sometimes there are multiple zeros. When doing parity testing with Excel and Google Sheets, I found many cases where Sheets and Excel find different zeros, so their internal solver algorithm must be different in some cases.

My initial solution tended to match Sheets when they differed, so I assume I and the Google engineers both came up with similar simple implementations. Who knows what the Excel algorithm is doing.

Of course, almost all these edge cases are for extremely weird unrealistic inputs.

[nhatcher]

Nice! This is my implementation:

https://github.com/ironcalc/IronCalc/blob/main/base/src/func...

although at this moment would only pass some "smoke" tests

RowZero is great!

[gamegoblin]

I started with basic Newton-Raphson solver too but found cases where it diverges but Excel somehow doesn't, so concluded that Excel has some kind of extra logic to handle more cases, so I also bolted on more fallback logic.

[goldenCeasar]

I wonder what would be your opinion on a OSS library that I am working that provides a declarative data flow DSL that statically checks and compile/optimize pure functions (no runtime. working on C target but have Ruby and JS already).

I feel I got a lot of inspiration from my time automating working with Excel as a Financial Analyst.