Jeff Moden wrote:
<quote>From the Last Sentence of the Conclusion in the Article...
To maintain the best precision of the final result all intermediate calculations must be performed on numbers in floating point formats, and the rounding must be performed (if necessary) only on the final data set.
Lordy, isn't that the truth. Try getting your mortgage calculations approved by Granny and her 4 function calculator (or spreadsheet) unless you do it that way. 😀 Just like milliseconds, pennies matter.
That is actually true.
We've just been there, several days ago.
i posted my version of mortgage calculator which uses only floating point calculations inside of the function: https://www.sqlservercentral.com/forums/topic/mortgage-amortization-table/page/7/#post-3760769
the amounts to pay and balances for each period - true, they have to be rounded, because it's actual monetary amounts which must change hands on specific dates. Therefore they cannot be taken as "intermediate calculations", they are the numbers reported to the users, they are in the "resulting data set".
And don't forget about the favourite word of all accountants - adjustments.
no matter how hard we try with all sorts of rounding, we cannot equally distribute a hundred between 3 periods/accounts/etc. it will always be 33, 33 and the last one adjusted to 34 to make the total right.
Same happens to mortgage calculators: the last period contains the adjustment to compensate for deviations accumulated in all previous periods. Our task is only not to let those balances to deviate too far from the correct numbers calculated with floating point math, so the last adjustment would not look too bad.
actually, if calculated correctly, monthly payments must vary from month to month, to keep the balances near the straight line between the starting point of full amount and zero balance at the end. But it would be slightly inconvenient and impractical. So, I found, banks round the payments up, usually to whole dollar amounts, and customers don't complain because it slightly reduces the accumulated interest over the duration of mortgage. And nobody worries about those pennies.