Understanding Financial Functions in Excel

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.

Re: Understanding Financial Functions in Excel

#4

Post by ryandv » Mon, Nov 10, 2025, 9:01 AM UTC

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

Re: Understanding Financial Functions in Excel

#5

Post by bvan » Mon, Nov 10, 2025, 11:58 AM UTC

simonjgreen wrote: post #3

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

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.

Re: Understanding Financial Functions in Excel

#6

Post by macintux » Mon, Nov 10, 2025, 1:29 PM UTC

bvan wrote: post #5

simonjgreen wrote: post #3

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

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.

Re: Understanding Financial Functions in Excel

#7

Post by lordgrenville » Mon, Nov 10, 2025, 2:12 PM UTC

ryandv wrote: post #4

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. A…

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

Re: Understanding Financial Functions in Excel

#8

Post by nocoiner » Mon, Nov 10, 2025, 2:42 PM UTC

simonjgreen wrote: post #3

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

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.

Re: Understanding Financial Functions in Excel

#9

Post by wombatpm » Mon, Nov 10, 2025, 3:02 PM UTC

nocoiner wrote: post #8

simonjgreen wrote: post #3

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

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.

Re: Understanding Financial Functions in Excel

#10

Post by gamegoblin » Mon, Nov 10, 2025, 3:04 PM UTC

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.