Understanding Financial Functions in Excel

4 comments

• simonjgreen 4 hours ago 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.

  [-]
  • bvan an hour ago 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 5 hours ago 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 4 hours ago 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