Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Understanding Financial Functions in Excel

ciju.in

Post reply on HN
Print view View original

11–15 of 15 posts

Re: Understanding Financial Functions in Excel

#11

Post by goldenCeasar »

gamegoblin wrote: post #10

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 di…

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.

Re: Understanding Financial Functions in Excel

#12

Post by nhatcher »

gamegoblin wrote: post #10

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 di…

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!

Re: Understanding Financial Functions in Excel

#14

Post by tantalor »

I was floored when I found IRR.

I know my way around a spreadsheet, but I had no exposure to the financial functions. As I recall, I wanted to find the rate of return for a rental property I was selling. I thought it would be really complicated to compute. Not knowing anything about that, I asked Gemini for help, and it suggested using IRR. Five minutes later, I had my rate of return.

@ciju chasflow_dates -> cashflow_dates

Re: Understanding Financial Functions in Excel

#15

Post by gamegoblin »

nhatcher wrote: post #12
gamegoblin wrote: post #10

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 di…

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!

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.
Post reply on HN