4 ms·

How do finance folks deal with decimals? Doesn't Excel use binary floats? Do they switch to integers?

by throwaway613834 9y ago

frgtpsswrdlame 9y ago
What? Excel handles decimals just fine.
- throwaway613834 9y ago
  > What? Excel handles decimals just fine. I thought it uses binary floats just like any typical computer program? https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel https://support.microsoft.com/en-us/help/78113/floating-poin...
  - dm319 9y ago
    Yes, it doesn't use binary-encoded decimal. I guess the precision is good enough...
    - throwaway613834 9y ago
      If precision was sufficient then so many languages wouldn't put in support for decimal data types, right?
      - dm319 9y ago
        Decimal precision isn't so important in modelling. More important in, say, accurate accounting of bitcoins.
- dmurray 9y ago
  It's an amazingly pervasive myth taught in CS 101 courses that "you should never use floating point types for currencies, or you might go to jail". In reality floats are fine for most financial applications, and for almost all those that would find themselves in a spreadsheet.
  - TillE 9y ago
    It's all fun and games until you realize that adding a large set of numbers in different orders gives you significantly different results. Floats are fine for a lot of things, but you have to be really really careful not to overstep into the situations they can't adequately handle.
    - cm2187 9y ago
      Even in accounting a certain rounding error is accepted depending on the size of the company. And for pricing financial instruments, prices pretty much always get rounded on the execution (you will never communicate a price with 20 decimals to a client on the phone). Excel rounding is a non problem in finance. Except when you are making exact payments. But payment systems aren’t run in excel anyway.
  - throwaway613834 9y ago
    > It's an amazingly pervasive myth taught in CS 101 courses that "you should never use floating point types for currencies, or you might go to jail". In reality floats are fine for most financial applications, and for almost all those that would find themselves in a spreadsheet. What do you think about this then? https://www.journalofaccountancy.com/issues/2014/mar/excel-calculation-errors.html https://www.journalofaccountancy.com/issues/2014/mar/excel-c...
    - dmurray 9y ago
      I think that if anyone ever needs to give me 12 quadrillion dollars (more than a million dollars for every human alive), they can keep the extra 21 dollars. I'm not unfamiliar with the idea that floating point numbers have a limited precision, I'm saying the precision is normally good enough.
      - throwaway613834 9y ago
        > I think that if anyone ever needs to give me 12 quadrillion dollars (more than a billion dollars for every human alive), they can keep the extra 21 dollars. You're missing the point. You don't need a 1-cent error for a smaller error to throw things off. Say someone owes you $0.30 and pays you $0.10 and $0.20. If the data type inherently has 2 digits past the decimal, or if you just do everything with integers, then they'll be debt-free by the end and nothing will go wrong. But if you're representing these with binary floats, then you have to remember to explicitly do rounding at precisely the correct intermediate spots in order to avoid incorrectly listing them as still having a balance. Even for a programmer it's too easy to either put it or forget to put it in the correct spot, let alone for a non-programmer writing the sheet. And that's just one example. I'm sure there are tons more I can't think of off the top of my head. It's not like people made the decimal data types for nothing.

How do finance folks deal with decimals? Doesn't Excel use binary floats? Do they switch to integers?

frgtpsswrdlame 9y ago

What? Excel handles decimals just fine.

throwaway613834 9y ago
> What? Excel handles decimals just fine. I thought it uses binary floats just like any typical computer program? https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel https://support.microsoft.com/en-us/help/78113/floating-poin...
- dm319 9y ago
  Yes, it doesn't use binary-encoded decimal. I guess the precision is good enough...
  - throwaway613834 9y ago
    If precision was sufficient then so many languages wouldn't put in support for decimal data types, right?
    - dm319 9y ago
      Decimal precision isn't so important in modelling. More important in, say, accurate accounting of bitcoins.
dmurray 9y ago
It's an amazingly pervasive myth taught in CS 101 courses that "you should never use floating point types for currencies, or you might go to jail". In reality floats are fine for most financial applications, and for almost all those that would find themselves in a spreadsheet.
- TillE 9y ago
  It's all fun and games until you realize that adding a large set of numbers in different orders gives you significantly different results. Floats are fine for a lot of things, but you have to be really really careful not to overstep into the situations they can't adequately handle.
  - cm2187 9y ago
    Even in accounting a certain rounding error is accepted depending on the size of the company. And for pricing financial instruments, prices pretty much always get rounded on the execution (you will never communicate a price with 20 decimals to a client on the phone). Excel rounding is a non problem in finance. Except when you are making exact payments. But payment systems aren’t run in excel anyway.
- throwaway613834 9y ago
  > It's an amazingly pervasive myth taught in CS 101 courses that "you should never use floating point types for currencies, or you might go to jail". In reality floats are fine for most financial applications, and for almost all those that would find themselves in a spreadsheet. What do you think about this then? https://www.journalofaccountancy.com/issues/2014/mar/excel-calculation-errors.html https://www.journalofaccountancy.com/issues/2014/mar/excel-c...
  - dmurray 9y ago
    I think that if anyone ever needs to give me 12 quadrillion dollars (more than a million dollars for every human alive), they can keep the extra 21 dollars. I'm not unfamiliar with the idea that floating point numbers have a limited precision, I'm saying the precision is normally good enough.
    - throwaway613834 9y ago
      > I think that if anyone ever needs to give me 12 quadrillion dollars (more than a billion dollars for every human alive), they can keep the extra 21 dollars. You're missing the point. You don't need a 1-cent error for a smaller error to throw things off. Say someone owes you $0.30 and pays you $0.10 and $0.20. If the data type inherently has 2 digits past the decimal, or if you just do everything with integers, then they'll be debt-free by the end and nothing will go wrong. But if you're representing these with binary floats, then you have to remember to explicitly do rounding at precisely the correct intermediate spots in order to avoid incorrectly listing them as still having a balance. Even for a programmer it's too easy to either put it or forget to put it in the correct spot, let alone for a non-programmer writing the sheet. And that's just one example. I'm sure there are tons more I can't think of off the top of my head. It's not like people made the decimal data types for nothing.