I built a rental-property calculator in Excel (long-term and short-term rentals, 8 sheets, about 2,500 formulas). The maths was right early on. The part that took six review rounds was the behaviour on bad input. Here is what the reviews found, measured on the files, and how each one is now checked.

How the maths is checked

  • The workbook is recalculated in Microsoft Excel and in LibreOffice (headless).
  • Every scenario's key cells (479 for the shipped example, 416 for each other scenario) are compared with a separate Python model written without reading the workbook's formulas.
  • Every cell on every sheet is scanned for error values (#VALUE!, #REF!, #DIV/0!).

All of that passed in round 1. It still was not enough.

Bug 1: blank cells count as $0

A new landlord types price, down payment, interest rate and rent, and skips tax, insurance, vacancy and repairs. The sheet showed $1,182.52 a month and "Year-1 cash flow is positive". The same house with those costs filled in makes $234.76 a month, about five times less. No cell was wrong; the blanks were silently treated as zero.

Fix: every cost input is now "required, type 0 if none". A blank raises a flag, and the headline numbers show n/a until the flags are cleared. The test: the blank file with only four cells typed must show n/a, not a figure.

Bug 2: the free version was rosier than the paid one

The free quick-check sheet gave $295.86 a month for the same house where the paid file gave $234.76. It used a lighter default cost allowance and left out repairs at purchase. Fix: same house, same numbers. The test now runs nine equality checks between the two files.

Bug 3: text in one cell produced 1,359 error cells

Typing the word "abc" into the purchase price made the headline show #VALUE! and spread through the sheets: Results 5 cells, Projection 37, Sensitivity 15, Amortization 1,300 and more. My earlier note said "every cell was scanned for error values". That was true for text in the rent cell and untested for the price cell. Fix: every formula now reads a helper column that holds the typed number, or 0 when the cell is empty or holds text. The input check still flags it. New tests type text into ten different input cells and scan all 8 sheets for error values after each one.

What I would tell anyone selling a spreadsheet

  1. Test the blank file, not only the filled example.
  2. Test the wrong type in every input, not in one.
  3. Compare the free and paid versions on the same inputs.
  4. Make the checks part of the build. A test that was never seen failing proves nothing, so each fix above was first shown failing, then passing.

Limits: I tested in Excel and LibreOffice. Google Sheets, Numbers and Excel for Mac were not tested.

The calculator and a free quick-check sheet are on Gumroad: the paid calculator ($19) and the free quick check ($0+).