Loan repayment schedule 💰
Result Set
Regardless of the database, the result set should look like:
| loan_id | repayment_number | repayment_date | interest | principal | total | balance |
|---|---|---|---|---|---|---|
| 1 | 1 | 2024-02-01 | 1600.00 | 12682.06 | 14282.06 | 67317.94 |
| 1 | 2 | 2024-03-01 | 1346.36 | 12935.70 | 14282.06 | 54382.24 |
| 1 | 3 | 2024-04-01 | 1087.64 | 13194.42 | 14282.06 | 41187.82 |
| 1 | 4 | 2024-05-01 | 823.76 | 13458.30 | 14282.06 | 27729.52 |
| 1 | 5 | 2024-06-01 | 554.59 | 13727.47 | 14282.06 | 14002.05 |
| 1 | 6 | 2024-07-01 | 280.04 | 14002.05 | 14282.09 | 0.00 |
| 2 | 1 | 2024-02-02 | 1125.00 | 5751.00 | 6876.00 | 69249.00 |
| 2 | 2 | 2024-03-02 | 1038.74 | 5837.26 | 6876.00 | 63411.74 |
| 2 | 3 | 2024-04-02 | 951.18 | 5924.82 | 6876.00 | 57486.92 |
| 2 | 4 | 2024-05-02 | 862.30 | 6013.70 | 6876.00 | 51473.22 |
| 2 | 5 | 2024-06-02 | 772.10 | 6103.90 | 6876.00 | 45369.32 |
| 2 | 6 | 2024-07-02 | 680.54 | 6195.46 | 6876.00 | 39173.86 |
| 2 | 7 | 2024-08-02 | 587.61 | 6288.39 | 6876.00 | 32885.47 |
| 2 | 8 | 2024-09-02 | 493.28 | 6382.72 | 6876.00 | 26502.75 |
| 2 | 9 | 2024-10-02 | 397.54 | 6478.46 | 6876.00 | 20024.29 |
| 2 | 10 | 2024-11-02 | 300.36 | 6575.64 | 6876.00 | 13448.65 |
| 2 | 11 | 2024-12-02 | 201.73 | 6674.27 | 6876.00 | 6774.38 |
| 2 | 12 | 2025-01-02 | 101.62 | 6774.38 | 6876.00 | 0.00 |
| 3 | 1 | 2024-02-03 | 1000.00 | 3707.35 | 4707.35 | 96292.65 |
| 3 | 2 | 2024-03-03 | 962.93 | 3744.42 | 4707.35 | 92548.23 |
| 3 | 3 | 2024-04-03 | 925.48 | 3781.87 | 4707.35 | 88766.36 |
| 3 | 4 | 2024-05-03 | 887.66 | 3819.69 | 4707.35 | 84946.67 |
| 3 | 5 | 2024-06-03 | 849.47 | 3857.88 | 4707.35 | 81088.79 |
| 3 | 6 | 2024-07-03 | 810.89 | 3896.46 | 4707.35 | 77192.33 |
| 3 | 7 | 2024-08-03 | 771.92 | 3935.43 | 4707.35 | 73256.90 |
| 3 | 8 | 2024-09-03 | 732.57 | 3974.78 | 4707.35 | 69282.12 |
| 3 | 9 | 2024-10-03 | 692.82 | 4014.53 | 4707.35 | 65267.59 |
| 3 | 10 | 2024-11-03 | 652.68 | 4054.67 | 4707.35 | 61212.92 |
| 3 | 11 | 2024-12-03 | 612.13 | 4095.22 | 4707.35 | 57117.70 |
| 3 | 12 | 2025-01-03 | 571.18 | 4136.17 | 4707.35 | 52981.53 |
| 3 | 13 | 2025-02-03 | 529.82 | 4177.53 | 4707.35 | 48804.00 |
| 3 | 14 | 2025-03-03 | 488.04 | 4219.31 | 4707.35 | 44584.69 |
| 3 | 15 | 2025-04-03 | 445.85 | 4261.50 | 4707.35 | 40323.19 |
| 3 | 16 | 2025-05-03 | 403.23 | 4304.12 | 4707.35 | 36019.07 |
| 3 | 17 | 2025-06-03 | 360.19 | 4347.16 | 4707.35 | 31671.91 |
| 3 | 18 | 2025-07-03 | 316.72 | 4390.63 | 4707.35 | 27281.28 |
| 3 | 19 | 2025-08-03 | 272.81 | 4434.54 | 4707.35 | 22846.74 |
| 3 | 20 | 2025-09-03 | 228.47 | 4478.88 | 4707.35 | 18367.86 |
| 3 | 21 | 2025-10-03 | 183.68 | 4523.67 | 4707.35 | 13844.19 |
| 3 | 22 | 2025-11-03 | 138.44 | 4568.91 | 4707.35 | 9275.28 |
| 3 | 23 | 2025-12-03 | 92.75 | 4614.60 | 4707.35 | 4660.68 |
| 3 | 24 | 2026-01-03 | 46.61 | 4660.68 | 4707.29 | 0.00 |
Expand for the DDL
solution(loan_id, repayment_number, repayment_date, interest, principal, total, balance) as (
values
(1, 1, '2024-02-01'::date, 1600.00, 12682.06, 14282.06, 67317.94),
(1, 2, '2024-03-01'::date, 1346.36, 12935.70, 14282.06, 54382.24),
(1, 3, '2024-04-01'::date, 1087.64, 13194.42, 14282.06, 41187.82),
(1, 4, '2024-05-01'::date, 823.76, 13458.30, 14282.06, 27729.52),
(1, 5, '2024-06-01'::date, 554.59, 13727.47, 14282.06, 14002.05),
(1, 6, '2024-07-01'::date, 280.04, 14002.05, 14282.09, 0.00),
(2, 1, '2024-02-02'::date, 1125.00, 5751.00, 6876.00, 69249.00),
(2, 2, '2024-03-02'::date, 1038.74, 5837.26, 6876.00, 63411.74),
(2, 3, '2024-04-02'::date, 951.18, 5924.82, 6876.00, 57486.92),
(2, 4, '2024-05-02'::date, 862.30, 6013.70, 6876.00, 51473.22),
(2, 5, '2024-06-02'::date, 772.10, 6103.90, 6876.00, 45369.32),
(2, 6, '2024-07-02'::date, 680.54, 6195.46, 6876.00, 39173.86),
(2, 7, '2024-08-02'::date, 587.61, 6288.39, 6876.00, 32885.47),
(2, 8, '2024-09-02'::date, 493.28, 6382.72, 6876.00, 26502.75),
(2, 9, '2024-10-02'::date, 397.54, 6478.46, 6876.00, 20024.29),
(2, 10, '2024-11-02'::date, 300.36, 6575.64, 6876.00, 13448.65),
(2, 11, '2024-12-02'::date, 201.73, 6674.27, 6876.00, 6774.38),
(2, 12, '2025-01-02'::date, 101.62, 6774.38, 6876.00, 0.00),
(3, 1, '2024-02-03'::date, 1000.00, 3707.35, 4707.35, 96292.65),
(3, 2, '2024-03-03'::date, 962.93, 3744.42, 4707.35, 92548.23),
(3, 3, '2024-04-03'::date, 925.48, 3781.87, 4707.35, 88766.36),
(3, 4, '2024-05-03'::date, 887.66, 3819.69, 4707.35, 84946.67),
(3, 5, '2024-06-03'::date, 849.47, 3857.88, 4707.35, 81088.79),
(3, 6, '2024-07-03'::date, 810.89, 3896.46, 4707.35, 77192.33),
(3, 7, '2024-08-03'::date, 771.92, 3935.43, 4707.35, 73256.90),
(3, 8, '2024-09-03'::date, 732.57, 3974.78, 4707.35, 69282.12),
(3, 9, '2024-10-03'::date, 692.82, 4014.53, 4707.35, 65267.59),
(3, 10, '2024-11-03'::date, 652.68, 4054.67, 4707.35, 61212.92),
(3, 11, '2024-12-03'::date, 612.13, 4095.22, 4707.35, 57117.70),
(3, 12, '2025-01-03'::date, 571.18, 4136.17, 4707.35, 52981.53),
(3, 13, '2025-02-03'::date, 529.82, 4177.53, 4707.35, 48804.00),
(3, 14, '2025-03-03'::date, 488.04, 4219.31, 4707.35, 44584.69),
(3, 15, '2025-04-03'::date, 445.85, 4261.50, 4707.35, 40323.19),
(3, 16, '2025-05-03'::date, 403.23, 4304.12, 4707.35, 36019.07),
(3, 17, '2025-06-03'::date, 360.19, 4347.16, 4707.35, 31671.91),
(3, 18, '2025-07-03'::date, 316.72, 4390.63, 4707.35, 27281.28),
(3, 19, '2025-08-03'::date, 272.81, 4434.54, 4707.35, 22846.74),
(3, 20, '2025-09-03'::date, 228.47, 4478.88, 4707.35, 18367.86),
(3, 21, '2025-10-03'::date, 183.68, 4523.67, 4707.35, 13844.19),
(3, 22, '2025-11-03'::date, 138.44, 4568.91, 4707.35, 9275.28),
(3, 23, '2025-12-03'::date, 92.75, 4614.60, 4707.35, 4660.68),
(3, 24, '2026-01-03'::date, 46.61, 4660.68, 4707.29, 0.00)
)
Solution
Some SQL solutions per database are provided below.
DuckDB
with recursive
monthly_repayment_value as (
select
loan_id,
power(1 + interest_rate, repayments) as amortised_rate,
round(
(loan_value * interest_rate * amortised_rate) / (amortised_rate - 1),
2
) as monthly_repayment
from loans
),
schedule as (
select
/* loan details */
loans.loan_id,
loans.interest_rate,
loans.repayments,
monthly_repayment_value.monthly_repayment,
/* repayment details */
0 as repayment_number,
loans.start_date as repayment_date,
0::decimal(10, 2) as starting_balance,
0::decimal(10, 2) as interest,
0::decimal(10, 2) as principal,
0::decimal(10, 2) as total,
loans.loan_value as remaining_balance
from loans
inner join monthly_repayment_value
using (loan_id)
union all
select
loan_id,
interest_rate,
repayments,
monthly_repayment,
repayment_number + 1,
repayment_date + interval '1 month',
remaining_balance,
round(remaining_balance * interest_rate, 2) as interest_,
monthly_repayment - interest_ as principal_,
monthly_repayment,
remaining_balance - principal_
from schedule
where repayment_number < repayments
)
select
loan_id,
repayment_number,
repayment_date,
interest,
/* adjust the final repayment with the rounding error */
if(repayment_number = repayments, starting_balance, principal) as principal,
if(repayment_number = repayments, starting_balance + interest, total) as total,
if(repayment_number = repayments, 0, remaining_balance) as balance
from schedule
where repayment_number > 0
order by
loan_id,
repayment_number