Understanding Financial Functions in Excel (opens in new tab)

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.

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.

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

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

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

...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!