Understanding Financial Functions in Excel
ciju.inI 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.
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.
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.
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.
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.
RPN is great. Start at the deepest level and work your way up.
I used to load my HP15c with common formula for engineering and a basic polynomial root finder.
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.
I had a small collection of RPN calculators, one or two non-HP, but alas someone broke into my house and took them. I really should have called around to pawn shops after, I’d very much like to have them back.
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.
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 _2Going to use "laughably trivial with automatic differentiation" as my new line to scare away PMs.
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!