8 ms·
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
by gamegoblin 10mo ago
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.
- goldenCeasar 10mo agoI 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.
- nhatcher 10mo agoNice! This is my implementation: https://github.com/ironcalc/IronCalc/blob/main/base/src/functions/financial_util.rs#L8-L47 https://github.com/ironcalc/IronCalc/blob/main/base/src/func... although at this moment would only pass some "smoke" tests RowZero is great!
- gamegoblin 10mo agoI 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.