Skip to main content

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[0],
        months[ITERATION_NUMBER]    = months[0],
        precision[ITERATION_NUMBER] = precision[0],
        tmp[ITERATION_NUMBER]       = NVL(remaining[ITERATION_NUMBER - 1], amount[0]),
        interest[ITERATION_NUMBER]  = tmp[CV()] * roi[0],
        principal[ITERATION_NUMBER] = emi[0] - 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;


Comments