Spreadsheet errors

1

How do you avoid spreadsheet errors? We use them for everything from budgeting, forecasting and retirement planning.

Today, I noticed an error in a spreadsheet which I rely on which resulted in me losing around 8k. At least for these near term things, you can catch easily. But what if there is a fundamental flaw in your retirement calculations which you might not catch until too late?

Normally, you should apply some kind of sense check, but with larger numbers, varying assumptions and compounding, the range of plausible answers could be quite large.

1 Like
2

How does one avoid errors in spreadsheets?

  1. Do research on how others do it. Ask specific questions on how to calculate values that you aren’t sure about. Google in different languages.
  2. Check and recheck.
  3. Use extreme values to see if it still does what you would expect.
  4. Discuss with others and share a copy with fake numbers for others to play around with.
  5. Update frequently with actual values.
  6. Check again.

Keep in mind that investment returns are probably the biggest unknown and due to compounding you might get it wrong anyway :man_shrugging:

1 Like
3

This.

I’m accounting for inaccuracy in my assumptions, data and modelling by having contingency plans (that is, staying flexible as I go as opposed to a “I’ll retire at 35 with 1.5M no matter what and I’ll burn all the bridges I can going there” approach, which I believe no one has).

I then refine/correct my assumptions/data and modelling as I go, taking account of what my actual observations are vs the original plan.

I believe with such an approach, there is no such thing as “too late”. Things will be caught as I go and I will live my life nonetheless. The path isn’t the most optimal one for sure but, in my assessment, chasing the most optimal path is a sure way to not end up on it and take a good shot at also missing the “good enough” paths along the way.

5

After finding the error, I went back and did a more detailed review of my spreadsheet and found the following errors:

  • SUM formulas overlapping so some items were double counted
  • Sign errors on some entries
  • Data errors (some items had tax values instead of market values)
  • Confusing wording which could lead to future errors
  • Mortgage calculations didn’t use 360 days per year for interest calculation
6

The trap I’m trying to avoid myself is to cram a complex formula into one cell. Instead I’m trying to lighten the load by using multiple cells. Helps with error detection.

2 Likes
7

Simple, really.

Don’t forecast. Measure.

8

I use two techniques:

  • changing a formula input (kinda like a unit test):
    • make a backup copy of your spreadsheet so you’re safe to do invasive changes
    • pick the formula you want to examine
    • play with the inputs, overwrite them with whatever. Is the result still reasonable? Can you use some known input/output combo?
  • conditional alerts (kinda like a proptest):
    • calculate control values
    • apply conditional formatting to the control value such that it lights up in bright red if a bad value is in there
    • example: in a balance sheet assets are supposed to be equal to debt plus liabilities. Control value/condition: assets - liabilities - equity = 0. If it’s not 0, then I made a mistake when entering data and the field will have a bright red background
3 Likes
9

Thanks. Those are some good and practical suggestions.

Sense checking against external sources is also good for numbers that it is difficult to have an intuitive sense check for. That’s how I found an error that was a whopping 50% out.

10

I found another two whoppers today:

  • I forgot to include Federal tax on pension withdrawal
  • I also forgot to include the Gemeinde tax on pension withdrawal
1 Like