Spreadsheet checks before running Payment Plan Math in Philippines

What the checker catches

Before you run Payment Plan Math in the Philippines (PH) with DocketMath, use a spreadsheet checker to catch errors that can quietly change your results. A payment plan math model is only as reliable as the inputs feeding it—and PH calculations are especially sensitive to date boundaries, compounding (if used), and “missing” fee components.

Here are the most common spreadsheet issues the checker is designed to detect (and how they typically change your outputs):

• **Mismatched date types (strings vs. real dates)

  • Example: 2026-01-15 stored as text, not a real date.
  • Output impact: schedule builders may treat the value as invalid, shift due dates, or compute wrong day counts—leading to incorrect interest/principal breakdowns.

• Off-by-one day errors around start dates

  • Example: using StartDate for the first payment date when the model expects it to represent the start of accrual instead (or vice versa).
  • Output impact: day-count changes can swing interest totals, particularly when the spreadsheet uses an interest convention like Actual/365 or 30/360.

• **Unit and scale mistakes (percent vs. decimal)

  • Example: entering 12% as 12 when the formulas expect 0.12.
  • Output impact: interest can be multiplied by 100x without any spreadsheet “error” showing up.

• Missing or double-counted fees

  • Example: ProcessingFee included both in the “amount financed” field and again as an add-on in the cashflow rows.
  • Output impact: payments can still look plausible, but the repayment totals may be inflated because fees are effectively counted twice.

• Inconsistent currency formatting and rounding

  • Example: one tab rounds the monthly payment to 2 decimals, while another tab recalculates totals using unrounded numbers (or vice versa), then copies the rounded payment back in.
  • Output impact: totals won’t reconcile with the amortization schedule. Balances can drift by small amounts that compound over time.

• Payment frequency mismatches

  • Example: the sheet is generating a weekly schedule, but your mapping treats it as “monthly” (or the reverse).
  • Output impact: the number of installments changes, which affects principal allocation and the maturity date.

• Negative sign convention mismatches

  • Example: principal entered as a positive number when the model expects a cash outflow to be negative (or vice versa).
  • Output impact: calculator totals can be inverted, and residual balance logic can produce an unexpected sign on remaining balance.

• Residual balance / last-installment logic errors

  • Example: the final payment row is hard-coded while earlier rows are computed from formulas.
  • Output impact: the final balance may not reach zero (or may overpay), distorting summary totals like total repayment and ending balance.

Pitfall to watch: A spreadsheet can look “clean” (no #N/A, no parse errors) while still producing wrong Payment Plan Math outputs due to date typing, percent/decimal scale, rounding inconsistencies, or fee inclusion logic.

To be practical, the checker focuses on verifying relationships between inputs, not just scanning for formula errors. In other words, it acts like a PH-aware sanity check that your spreadsheet assumptions match what DocketMath’s payment-plan-math expects.

When to run it

Run the checker at moments where a error would be costly or hard to diagnose later—ideally before you push inputs into DocketMath, and again right after you generate the schedule.

Use this checklist timeline:

1. Before you run Payment Plan Math

   • Confirm that:

     • LoanAmount / Principal is numeric, in the expected units (e.g., PHP number only—no currency symbols embedded in the value).
     • AnnualRate is entered consistently (percent vs. decimal). Pick one convention and stick to it.
     • StartDate and any FirstPaymentDate are real dates (not text).

2. Immediately after you map spreadsheet columns into DocketMath

   • Verify:

     • Payment frequency is consistent with your spreadsheet’s interval logic.
     • Fee components are included exactly once and categorized consistently (for example, don’t mix “financed fees” and “additional add-ons” in two places unless your model expects it).

3. After schedule generation but before you copy totals

   • Validate:

     • Sum of installment payments vs. the summary total (allowing for your model’s rounding behavior).
     • Final residual balance is ~0 (or exactly 0 if the model enforces that).
     • The number of installments matches your intended term length.

4. Before sharing results internally

   • Confirm formatting and reconciliation:

     • Dates display correctly.
     • Currency values follow the intended rounding policy consistently.

If you work with multi-case spreadsheets (multiple borrowers/contracts), run the checker per case—because one bad date or sign convention in one row can contaminate downstream totals.

Try the checker

You can run the Payment Plan Math flow in DocketMath here: /tools/payment-plan-math.

When you use the checker approach, treat it like a quick validation loop around your spreadsheet-to-model wiring.

Upload the spreadsheet, review the warnings, and then run the calculation once the inputs are clean: Try the checker.

Quick input checklist (PH)

• Principal / Loan Amount (PHP): numeric, correct sign (based on the model’s cashflow convention)
• Annual Interest Rate: consistent convention (e.g., 0.12 for 12% if the model expects decimal)
• Start Date: a real date value (not text)
• Payment Frequency: monthly vs. other—must match your spreadsheet’s schedule generation
• Term / Number of Payments: aligns with the number of rows you intend
• Fees: included once (processing/insurance/admin—however your model structures them)
• Rounding rules: consistent across tabs and across intermediate vs. final totals

How outputs should react to common input changes

Use these small “change tests” to confirm the spreadsheet is connected correctly:

• If you increase annual rate (e.g., 10% → 12%):

  • Monthly payment should typically increase, and/or total interest should increase.
• If you move StartDate forward by 15 days:

  • Day-count-driven interest (if your setup uses it) should change, and the first accrual period should shift.
• If you add a fee that is meant to increase the financed amount:

  • Payment totals should increase, and the principal repayment trajectory should reflect the higher baseline.
• If you change payment frequency (monthly → weekly/biweekly where supported in your setup):

  • Installment count should rise and maturity date should shift accordingly.

Recommended workflow

1. Run the checker against your spreadsheet tabs.
2. Map inputs into DocketMath’s payment-plan-math flow.
3. Compare a small set of computed rows (e.g., first 1–2 installments) against your spreadsheet’s corresponding rows to confirm wiring.
4. Only then copy/export the full schedule.

Also, a gentle reminder: this is guidance to improve spreadsheet accuracy—not legal advice. Payment plan implementations can vary by product and lender policy.

Related reading

• Payment Plan Math Guide for Alabama
• Payment Plan Math Guide for Arizona
• Payment Plan Math Guide for Colorado