Mortgage & Loan Calculator

How it works — every step, readable

1. 01Assumptions — edit these

   SELECT * FROM (VALUES
     ('Home price', 500000, 'USD'),
     ('Down payment', 20, '%'),
     ('Interest rate', 6.5, '%'),
     ('Loan term', 30, 'years'),
     ('Property tax', 1.2, '% of price per year'),
     ('Home insurance', 1800, 'USD per year')
   ) AS t(assumption, value, unit)

2. 02Your monthly payment

   buckets values by condition · appends result sets (e.g. a TOTAL row) · sorts the output

   WITH p AS (
     SELECT
       MAX(CASE WHEN assumption = 'Home price' THEN CAST(value AS DOUBLE) END) AS price,
       MAX(CASE WHEN assumption = 'Down payment' THEN CAST(value AS DOUBLE) END) / 100.0 AS down,
       MAX(CASE WHEN assumption = 'Interest rate' THEN CAST(value AS DOUBLE) END) / 100.0 / 12.0 AS r,
       CAST(ROUND(MAX(CASE WHEN assumption = 'Loan term' THEN CAST(value AS DOUBLE) END) * 12) AS INTEGER) AS n,
       COALESCE(MAX(CASE WHEN assumption = 'Property tax' THEN CAST(value AS DOUBLE) END), 0) / 100.0 AS tax,
       COALESCE(MAX(CASE WHEN assumption = 'Home insurance' THEN CAST(value AS DOUBLE) END), 0) AS ins
     FROM input_1
   ), c AS (
     SELECT *, price * (1 - down) AS loan,
       CASE WHEN r = 0 THEN price * (1 - down) / n ELSE price * (1 - down) * r / (1 - POWER(1 + r, -n)) END AS pmt
     FROM p
   )
   SELECT metric, amount, unit FROM (
     SELECT 1 AS o, 'Monthly payment (all-in)' AS metric, ROUND(pmt + price * tax / 12 + ins / 12, 2) AS amount, 'USD' AS unit FROM c
     UNION ALL SELECT 2, 'Principal & interest', ROUND(pmt, 2), 'USD' FROM c
     UNION ALL SELECT 3, 'Taxes & insurance', ROUND(price * tax / 12 + ins / 12, 2), 'USD' FROM c
     UNION ALL SELECT 4, 'Loan amount', ROUND(loan, 2), 'USD' FROM c
     UNION ALL SELECT 5, 'Total interest paid', ROUND(pmt * n - loan, 2), 'USD' FROM c
     UNION ALL SELECT 6, 'Down payment (cash at closing)', ROUND(price * down, 2), 'USD' FROM c
   ) ORDER BY o

3. 03Principal vs interest, year by year

   combines data from multiple inputs · buckets values by condition · filters to the relevant rows

   WITH p AS (
     SELECT
       MAX(CASE WHEN assumption = 'Home price' THEN CAST(value AS DOUBLE) END) AS price,
       MAX(CASE WHEN assumption = 'Down payment' THEN CAST(value AS DOUBLE) END) / 100.0 AS down,
       MAX(CASE WHEN assumption = 'Interest rate' THEN CAST(value AS DOUBLE) END) / 100.0 / 12.0 AS r,
       CAST(ROUND(MAX(CASE WHEN assumption = 'Loan term' THEN CAST(value AS DOUBLE) END) * 12) AS INTEGER) AS n,
       COALESCE(MAX(CASE WHEN assumption = 'Property tax' THEN CAST(value AS DOUBLE) END), 0) / 100.0 AS tax,
       COALESCE(MAX(CASE WHEN assumption = 'Home insurance' THEN CAST(value AS DOUBLE) END), 0) AS ins
     FROM input_1
   ), c AS (
     SELECT *, price * (1 - down) AS loan,
       CASE WHEN r = 0 THEN price * (1 - down) / n ELSE price * (1 - down) * r / (1 - POWER(1 + r, -n)) END AS pmt
     FROM p
   )
   SELECT
     y AS year,
     ROUND((CASE WHEN r = 0 THEN loan - pmt * (12 * (y - 1)) ELSE loan * POWER(1 + r, 12 * (y - 1)) - pmt * (POWER(1 + r, 12 * (y - 1)) - 1) / r END) - GREATEST((CASE WHEN r = 0 THEN loan - pmt * (12 * y) ELSE loan * POWER(1 + r, 12 * y) - pmt * (POWER(1 + r, 12 * y) - 1) / r END), 0), 2) AS principal_paid,
     ROUND(LEAST(12, n - 12 * (y - 1)) * pmt - ((CASE WHEN r = 0 THEN loan - pmt * (12 * (y - 1)) ELSE loan * POWER(1 + r, 12 * (y - 1)) - pmt * (POWER(1 + r, 12 * (y - 1)) - 1) / r END) - GREATEST((CASE WHEN r = 0 THEN loan - pmt * (12 * y) ELSE loan * POWER(1 + r, 12 * y) - pmt * (POWER(1 + r, 12 * y) - 1) / r END), 0)), 2) AS interest_paid,
     ROUND(GREATEST((CASE WHEN r = 0 THEN loan - pmt * (12 * y) ELSE loan * POWER(1 + r, 12 * y) - pmt * (POWER(1 + r, 12 * y) - 1) / r END), 0), 2) AS ending_balance
   FROM c CROSS JOIN generate_series(1, 50) AS g(y)
   WHERE 12 * (y - 1) < n
   ORDER BY y