Mortgage Rates: What the Same House Costs Now

How it works — every step, readable

1. 0130-year fixed mortgage rate, weekly

   SELECT * FROM read_csv_auto('FRED_MORTGAGE30US.csv', header=true)

2. 02Assumptions — edit these

   SELECT * FROM (VALUES ('Home price', 400000, 'USD'), ('Down payment', 20, '%')) AS t(assumption, value, unit)

3. 03Monthly payment on the same house

   aggregates rows into summary totals · buckets values by condition · sorts the output

   WITH a AS (SELECT MAX(CASE WHEN assumption = 'Home price' THEN CAST(value AS DOUBLE) END) * (1 - MAX(CASE WHEN assumption = 'Down payment' THEN CAST(value AS DOUBLE) END) / 100.0) AS loan FROM input_2),
   m AS (SELECT date_trunc('month', date) AS month, AVG(mortgage30us) AS rate FROM input_1 GROUP BY 1)
   SELECT month, ROUND((loan * (rate) / 1200.0 / (1 - POWER(1 + (rate) / 1200.0, -360))), 2) AS monthly_payment FROM m, a ORDER BY month

4. 04The punch lines

   buckets values by condition · appends result sets (e.g. a TOTAL row) · filters to the relevant rows

   WITH a AS (SELECT MAX(CASE WHEN assumption = 'Home price' THEN CAST(value AS DOUBLE) END) AS price,
     MAX(CASE WHEN assumption = 'Home price' THEN CAST(value AS DOUBLE) END) * (1 - MAX(CASE WHEN assumption = 'Down payment' THEN CAST(value AS DOUBLE) END) / 100.0) AS loan FROM input_2),
   r AS (SELECT arg_max(mortgage30us, date) AS now_r, max(date) AS d1, min(mortgage30us) AS low_r, arg_min(date, mortgage30us) AS low_d,
     avg(mortgage30us) FILTER (WHERE year(date) = 2019) AS r2019 FROM input_1),
   p AS (SELECT *, (loan * (now_r) / 1200.0 / (1 - POWER(1 + (now_r) / 1200.0, -360))) AS p_now, (loan * (low_r) / 1200.0 / (1 - POWER(1 + (low_r) / 1200.0, -360))) AS p_low, (loan * (r2019) / 1200.0 / (1 - POWER(1 + (r2019) / 1200.0, -360))) AS p_2019 FROM r, a)
   SELECT area, finding, next_step FROM (
     SELECT 1 AS o, 'VERDICT' AS area, 'THE SAME HOUSE COSTS ' || ('$' || format('{:,}', CAST(ROUND(p_now - p_low) AS BIGINT))) || ' MORE A MONTH THAN AT THE 2021 LOW' AS finding, '' AS next_step FROM p
     UNION ALL SELECT 2, 'Rate today', printf('%.2f%%', now_r), 'Week of ' || strftime(d1, '%b %-d, %Y') FROM p
     UNION ALL SELECT 3, 'Payment today', ('$' || format('{:,}', CAST(ROUND(p_now) AS BIGINT))) || ' a month', 'Principal and interest on ' || ('$' || format('{:,}', CAST(ROUND(loan) AS BIGINT))) FROM p
     UNION ALL SELECT 4, 'At the low', ('$' || format('{:,}', CAST(ROUND(p_low) AS BIGINT))) || ' a month', printf('%.2f%%', low_r) || ' in ' || strftime(low_d, '%B %Y') FROM p
     UNION ALL SELECT 5, 'Over 30 years', ('$' || format('{:,}', CAST(ROUND((p_now - p_low) * 360) AS BIGINT))) || ' more interest', 'Than the same loan at the low' FROM p
     UNION ALL SELECT 6, 'Versus 2019', ('$' || format('{:,}', CAST(ROUND(p_now - p_2019) AS BIGINT))) || ' a month more', '2019 average rate ' || printf('%.2f%%', r2019) FROM p
   ) ORDER BY o