### Loan calendar with SQL MODEL

I

I was learning about this many years ago as a part of preparation for SQL Expert certification. Let's create simple loan calendar using MODEL clause. Looks a bit complicated at first. And especially if you check the CONNECT BY alternative below.

Can you tell me advantages of MODEL? Is there anything only MODEL can do? Well, you can access any row you calculated on previous lines. You will also look cool and nobody will be able to fix this query after you.

```WITH s AS (
SELECT
100000          AS amount,
12.9 / 100 / 12 AS roi,         -- 12.9% p.a.
36              AS months,
2               AS precision
FROM DUAL
)
SELECT
n,
emi,
ROUND(interest, precision)  AS interest,
ROUND(principal, precision) AS principal,
ROUND(remaining, precision) AS remaining
FROM (
SELECT
n + 1 AS n,
months,
emi,
precision,
DECODE(n + 1, months, interest - remaining, interest)   AS interest,
DECODE(n + 1, months, principal + remaining, principal) AS principal,
DECODE(n + 1, months, 0, remaining)                     AS remaining
FROM (
SELECT
0 AS n,
s.*,
ROUND(amount * roi / (1 - POWER(1 + roi, -months)), precision) AS emi
FROM s
)
MODEL
DIMENSION BY (n)
MEASURES (
amount, roi, months, precision, emi,
0 AS interest,
0 AS principal,
0 AS remaining,
0 AS tmp
)
RULES UPSERT ITERATE (1200)  -- max periods
(
emi[ITERATION_NUMBER]       = emi,
months[ITERATION_NUMBER]    = months,
precision[ITERATION_NUMBER] = precision,
tmp[ITERATION_NUMBER]       = NVL(remaining[ITERATION_NUMBER - 1], amount),
interest[ITERATION_NUMBER]  = tmp[CV()] * roi,
principal[ITERATION_NUMBER] = emi - interest[CV()],
remaining[ITERATION_NUMBER] = tmp[CV()] - principal[CV()]
)
) x
WHERE n <= months
ORDER BY n;
```

CONNECT BY alternative (same results):

```WITH s AS (
SELECT
100000          AS amount,
12.9 / 100 / 12 AS roi,         -- 12.9% p.a.
36              AS months,
2               AS precision
FROM DUAL
)
SELECT LEVEL AS n,
ROUND(amount * (roi / (1 - POWER(v, months))), precision)                                       AS emi,
ROUND(amount * (roi / (1 - POWER(v, months))) * (1 - POWER(v, months - LEVEL + 1)), precision)  AS interest,
ROUND(amount * (roi / (1 - POWER(v, months))) * POWER(v, months - LEVEL + 1), precision)        AS principal
FROM (
SELECT
s.*,
1 / (1 + roi) AS v
FROM s
)
CONNECT BY LEVEL <= months;
```