Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

CP
CP

Posted on • Edited on

     

How-to Avoid Rounding "Errors" in Excel

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)

Alt Text

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 get112.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 bothT andI 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 theirUsing 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.

Top comments(4)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss
CollapseExpand
 
devanghingu profile image
Devang Hingu
Self Taught Guy | Post graduate Student | Cybergeek <br/>After all i'm Lazy Engineer
  • Location
    404
  • Education
    Sardar Patel University
  • Work
    associate software engineer at Tntra.io
  • Joined

sometimes, excel not allow to write unrecognized character. right now i just replacing those character by"" but yet not found perfect solution.

CollapseExpand
 
paddy3118 profile image
Paddy3118
  • Joined
• Edited on• Edited

DON'T USE A SPREADSHEET.
If you used Python, for example, you could use decimals and set the precision appropriately.

CollapseExpand
 
calvinpak profile image
CP
It feels like every 2 years I have to re-learn everything. Yeah, that's the life of a developer.
  • Location
    Las Vegas, NV
  • Education
    B.Sc. Electrical Engineer & Computer Science
  • Work
    Founder & CEO at Titans Finance
  • Joined

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. 🤷‍♂️

CollapseExpand
 
paddy3118 profile image
Paddy3118
  • Joined

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.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

It feels like every 2 years I have to re-learn everything. Yeah, that's the life of a developer.
  • Location
    Las Vegas, NV
  • Education
    B.Sc. Electrical Engineer & Computer Science
  • Work
    Founder & CEO at Titans Finance
  • Joined

More fromCP

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp