While working on a financial calculator using Excel as the "reference of truth", I've run into this rounding error -- payments are off by pennies and it ends up off by dollars over time. Instinctively, I tend to believe Excel must be correct. But after digging into the details, I noticed that there is a round off error with Excel:

While Excel displays only 2-decimals, internally, it has a higher precision. And calculations use the highest internal precisions before rounding off.

And this causes problems for financial calculators, where we assume the precision is always set to 2 decimals.

See the following calculation:

`P = T - I (Principal = Total Payment - Interest Payment)`

The principle (P) is off by 1-penny when using Excel, and here is why:

I = 32.93698323

T= 145.20 (fixed amount the user pays monthly)

P = 112.26535914 (calculated with Excel formula with previous round off error)

When displaying only 2 decimals, the numbers become:

I = 32.94

T = 145.20

P = 112.27

Plugging in the formula:

`P = T - I`

and you get`112.27 = 112.26`

This does not sound right, does it? Since we are dealing with money--which only has two decimals precisions--to get the correct numbers in Excel, we need to round up both `T`

and `I`

by using the formula `=ROUND(..., 2)`

in Excel.

Use

`=ROUND(......, 2)`

in your formula to force a 2 decimal precision inExcel

Similarly, while programming the calculator, we have to be mindful that we always round up the result to `.toFixed(2)`

before subjecting it to additional calculations.

This maybe something accountants learned in their `Using Excel for Accounting 101`

course, but it is something easily missed for non-accountants and would cause a lot of confusion when not done right.

## Latest comments (4)

sometimes, excel not allow to write unrecognized character. right now i just replacing those character by

`""`

but yet not found perfect solution.DON'T USE A SPREADSHEET.

If you used Python, for example, you could use decimals and set the precision appropriately.

I agree, Python is amazing for dealing with numbers! While this is true for developers, we often get the requirements from the business people/product managers and they put the samples in Excel. 🤷♂️

Then archive their concept and deliver a solution in a less error prone programming language rather than patching up a buggy, flashy spreadsheet. We had inaccurate Covid 19 stats in the UK due to Excel errors lately, but the horrors are many, and usually buried by companies to save their embarrassment.