I used to load my HP15c with common formula for engineering and a basic polynomial root finder.
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 _2One 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.
I feel I got a lot of inspiration from my time automating working with Excel as a Financial Analyst.
https://github.com/ironcalc/IronCalc/blob/main/base/src/func...
although at this moment would only pass some "smoke" tests
RowZero is great!
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
nhatcher•2mo ago
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