Understanding Financial Functions in Excel

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|3 months ago

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!

goldenCeasar|3 months ago

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.

simonjgreen|3 months ago

If you want to get a really good feel for these functions, you can do worse than pick up a financial RPN calculator like the HP 12C. It is largely unchanged since it was introduced in the early 80s but it’s highly functional aesthetic and purpose make for a great experience if you like to learn something new that is also genuinely useful. Personally, I keep one of these in my bag. It’s great for meetings where financials are on the table and you also don’t want the distraction of a full desktop OS around you.

nocoiner|3 months ago

This is good advice. Also running a quick function can be quicker than opening up excel, fiddling with a cell, etc. (my excel skills are obviously at-best rudimentary). And it’s a cool moment when RPN finally “clicks” and figure out how to perform sequential operations in it without having to rely on increasingly nested parentheses.

bvan|3 months ago

Unfortunately, these have disappeared from trading floors. Mine is under lock and key.. I sometimes take it or an HP 41 out and place it on my desk just to see the horrified looks on twentysomething’s faces.

nhatcher|3 months ago

Hi! Currently I am implementing those on IronCalc[1]!

They are really complex:

https://www.oasis-open.org/2021/06/16/opendocument-v1-3-oasi...

Is the odf counterpart, full on details. The libreoffice implementation:

https://github.com/LibreOffice/core/blob/9667d5e9ebe4a68a772...

I should be done within the week.

[1]: https://github.com/ironcalc/IronCalc

ryandv|3 months ago

XIRR is laughably trivial with automatic differentiation in Haskell. Take as many iterations from the resulting [Double] as desired:

    type Cashflow = (Text, Day, Double)

    irr :: V.Vector Cashflow -> [Double]
    irr = fmap (flip findZero 0.01) npv
    
    npv :: V.Vector Cashflow -> (forall s. AD s ForwardDouble -> AD s ForwardDouble)
    npv cashflows = sum . flip discountedCashflows cashflows
      where
            discountedCashflows :: forall s. AD s ForwardDouble -> V.Vector Cashflow -> V.Vector (AD s ForwardDouble)
            discountedCashflows = fmap . presentValue

            presentValue :: forall s. AD s ForwardDouble -> Cashflow -> AD s ForwardDouble
            presentValue r (_,t,cf) = auto cf / ( (1 + r) ** numCompoundingPeriods t)

            numCompoundingPeriods t = (fromRational . toRational $ diffDays t t0) / 365.0

            t0 = maybe (toEnum 0) viewInvestmentDate $ cashflows V.!? 0

            viewInvestmentDate = view _2

lordgrenville|3 months ago

Going to use "laughably trivial with automatic differentiation" as my new line to scare away PMs.

tantalor|3 months ago

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

nxobject|3 months ago

...I will admit to thinking-harder-rather-than-smarter and implementing two of these once using Goal Seek. Of course Excel's going to have finance functions!

14 comments