Creating an amortization schedule in snowflake

Question

I have a view in snowflake that gives me the following:

• loan date
• loan amount
• maturity date
• payment frequency (weekly, biweekly, semimonthly, monthly)
• payment amount

I want to generate a sort of amortization schedule off of this, if you will. So if i have a loan with a date of 1/1/2022, and a maturity date of 3/9/2022 and a payment frequency of biweekly @ $50 per payment, i would want to see an output like:

LoanID	Payment Date	Payment Amount	Payment Frequency
abc123	1/15/2022	$50.00	biweekly
abc123	1/29/2022	$50.00	biweekly
abc123	2/12/2022	$50.00	biweekly
abc123	2/26/2022	$50.00	biweekly
abc123	3/09/2022	$50.00	biweekly

I'm assuming i need some sort of loop while payment date < maturity date and sum(payment amount) < loan amount, but i'm not sure how to set that up properly for a view with thousands of loans. ANY help you all can provide would be incredible and i'm very grateful!

Answer 1

You can get this by writing a Recursive CTE, just remember that the default is limited to 100 iterations, if you need more loops then check this MAX_RECURSIONS parameter.

This is just an example of code, you should extend it to include some extreme data protection;

Sample data:

CREATE OR REPLACE TABLE LoanTable (
    LoanID STRING,
    Loan_date DATE,
    Loan_amount NUMERIC(12,2),
    Maturity_date DATE,
    Payment_frequency STRING,
    Payment_amount NUMERIC(12,2)
);

INSERT INTO LoanTable
VALUES ('abc123', '1/1/2022', 250, '3/9/2022', 'biweekly', 50);

Query:

WITH Recursive_CTE AS (
  SELECT LoanID, 
         CASE Payment_frequency WHEN 'weekly'      THEN DATEADD(WEEK, 1, Loan_date)
                                WHEN 'biweekly'    THEN DATEADD(WEEK, 2, Loan_date)
                                WHEN 'semimonthly' THEN DATEADD(DAY, 15, Loan_date) -- I don't know how the semimonthly value is determined??
                                WHEN 'monthly'     THEN DATEADD(MONTH, 1, Loan_date) END AS Payment_Date,
         Payment_amount,
         Loan_amount - Payment_amount AS Left_to_pay,
         Payment_frequency,
         Maturity_date
    FROM LoanTable
   UNION ALL
  SELECT LoanID, 
         CASE Payment_frequency WHEN 'weekly'      THEN DATEADD(WEEK, 1, Payment_Date)
                                WHEN 'biweekly'    THEN DATEADD(WEEK, 2, Payment_Date)
                                WHEN 'semimonthly' THEN DATEADD(DAY, 15, Payment_Date) -- I don't know how the semimonthly value is determined??
                                WHEN 'monthly'     THEN DATEADD(MONTH, 1, Payment_Date) END AS Payment_Date,
         Payment_amount,
         IFF(Left_to_pay - Payment_amount < 0, Left_to_pay, Left_to_pay - Payment_amount) AS Left_to_pay,
         Payment_frequency,
         Maturity_date
    FROM Recursive_CTE  
   WHERE Left_to_pay > 0
)
SELECT LoanID, IFF(Payment_Date > Maturity_date, Maturity_date, Payment_Date) AS Payment_Date, Payment_amount, Left_to_pay, Payment_frequency
  FROM Recursive_CTE
 ORDER BY LoanID, Payment_Date;

Answer 2

Table generator is another approach.

Thanks to Simon for making this solution better. Respect!

WITH CTE_MY_DATE AS 
(SELECT  DATEADD(DAY, row_number() over (order by null)-1, '1900-01-01')::date AS MY_DATE FROM table(generator(rowcount => 18000))) 

SELECT
    date(MY_DATE) CALENDAR_DATE,
    concat( decode(extract ('dayofweek_iso', date(MY_DATE)),1,'Monday',2, 'Tuesday',3, 'Wednesday',4, 'Thursday',5, 'Friday',6, 'Saturday',7, 'Sunday'),TO_CHAR(date(MY_DATE), ', MMMM DD, YYYY')) FULL_DATE_DESC  
,row_number() over (partition by 1 order by calendar_date ) MOD_IS_COOL
FROM
    CTE_MY_DATE  
where
    CALENDAR_DATE 
        between '2022-01-02' and '2022-09-03' 
qualify 
    mod(MOD_IS_COOL, 14) = 0

Answer 3

Here's how to do Amortization via a JavaScript UDF with an example of how to call it. I had some trouble getting the JSON out of the function so returned it as a text string, stripped the double quotes, flattened it, and converted to a Table. Maybe someone better at JavaScript could modify it to return the table precleaned.

CREATE OR REPLACE FUNCTION AMORTIZATIONTABLE("AMOUNTFINANCED" FLOAT, "INTEREST" FLOAT, "PERIODS" FLOAT)
    RETURNS STRING
    LANGUAGE javascript
    AS $$
    
    const annuity = (AMOUNTFINANCED, INTEREST, PERIODS) => AMOUNTFINANCED * (INTEREST / (1 - (1 + INTEREST)**(-PERIODS)));
    
    const balance_t = (AMOUNTFINANCED, INTEREST, P) => {
        const period_movements = {
            base: AMOUNTFINANCED
        }
        
        period_movements.interest = AMOUNTFINANCED * INTEREST;
        period_movements.amortization = P - (AMOUNTFINANCED * INTEREST);
        period_movements.annuity = P;
        period_movements.final_value = Math.round((AMOUNTFINANCED - period_movements.amortization) * 100) / 100;
    
        return period_movements;
    }
    
    const display_mortgage = (AMOUNTFINANCED, INTEREST, PERIODS) => {
        var data = [];
        const payements = annuity(AMOUNTFINANCED, INTEREST, PERIODS);
        let movements = balance_t(AMOUNTFINANCED, INTEREST, payements);
        while (movements.final_value > -.01) {
            data.push(movements);
            movements = balance_t(movements.final_value, INTEREST, payements);
        }       
        
        return data;
    }
    
    data2 = display_mortgage(AMOUNTFINANCED, INTEREST, PERIODS);

    return JSON.stringify(data2);

    $$;



SELECT
    INDEX + 1 AS Period,
    a.VALUE:base AS CurrPrincipalBal,
    a.VALUE:annuity AS TotalPayment,
    a.VALUE:amortization AS PrincipalPmt,
    a.VALUE:interest AS InterestPmt,
    a.VALUE:final_value AS NewPrincipalBal
FROM
    (SELECT * FROM TABLE(flatten(INPUT => SELECT parse_json(REPLACE(AMORTIZATIONTABLE(20000.00, 0.04, 12.00),'"',''))))) AS a;

Answer 4

So I thought I could write this "cleaner" using a table generator.

To be fair I feel this is cleaner than the recursive CTE.

To things to note, you "max loan period possible" need to be inserted for the 1000 that I have.

The bimonthly, is done by find the number of days between the monthly options, and taking "half that" it's normal to use 15 for the odd days.

But like that:

WITH loans_table(loanid, loan_date, loan_amount,
                 maturity_date, payment_frequency, 
                 payment_amount) as (
    SELECT * FROM VALUES 
        ('abc123', '2022-01-01'::date, 250, '2022-03-09'::date, 'biweekly', 50)
), table_of_numbers as (
    SELECT row_number() over(order by null) as rn
    FROM TABLE(generator(ROWCOUNT => 1000))
    /* that 1000 should be larger than any loan perdiod length you have */
), loan_enrich as (
    SELECT 
        *
        ,CASE Payment_frequency 
            WHEN 'weekly'      THEN 7
            WHEN 'biweekly'    THEN 14
            WHEN 'semimonthly' THEN 14
            WHEN 'monthly'     THEN 28
        END as period_lo_days
        ,datediff('day', loan_date, maturity_date) as loan_days
        ,CEIL(loan_days / period_lo_days) as loan_periods
    FROM loans_table
)
SELECT 
    l.loanid,
    CASE payment_frequency 
        WHEN 'weekly' THEN dateadd('week', r.rn, l.loan_date)
        WHEN 'biweekly' THEN dateadd('week', r.rn * 2, l.loan_date)
        WHEN 'semimonthly' THEN 
            case r.rn%2
                when 0 then dateadd('month', r.rn/2, l.loan_date)
                when 1 then dateadd('days', floor(datediff('days', dateadd('month', (r.rn-1)/2, l.loan_date), dateadd('month', (r.rn+1)/2, l.loan_date))/2), dateadd('month', (r.rn-1)/2, l.loan_date))
            end
        WHEN 'monthly' THEN dateadd('month', r.rn, l.loan_date)
    END as payment_date,
    l.payment_amount,
    l.payment_frequency
FROM loan_enrich AS l
JOIN table_of_numbers AS r 
    ON l.loan_periods >= r.rn
ORDER BY 1, r.rn;

gives:

LOANID	PAYMENT_DATE	PAYMENT_AMOUNT	PAYMENT_FREQUENCY
abc123	2022-01-15	50	biweekly
abc123	2022-01-29	50	biweekly
abc123	2022-02-12	50	biweekly
abc123	2022-02-26	50	biweekly
abc123	2022-03-12	50	biweekly

So this can be boosted, to have semimonthly15 which is always 15 days later, and we can do some do some filtering incase the number of rows was more than we need, and we can show logic for handling a final payment that is smaller than the prior payments:

WITH loans_table(loanid, loan_date, loan_amount,
                 maturity_date, payment_frequency, 
                 payment_amount) as (
    SELECT * FROM VALUES 
        ('abc123', '2022-01-01'::date, 250, '2022-03-09'::date, 'biweekly', 50),
        ('abc124', '2022-01-01'::date, 249, '2022-03-09'::date, 'semimonthly', 50),
        ('abc125', '2022-01-01'::date, 249, '2022-03-09'::date, 'semimonthly15', 50)
), table_of_numbers as (
    SELECT row_number() over(order by null) as rn
    FROM TABLE(generator(ROWCOUNT => 1000))
    /* that 1000 should be larger than any loan perdiod length you have */
), loan_enrich as (
    SELECT 
        *
        ,CASE Payment_frequency 
            WHEN 'weekly'      THEN 7
            WHEN 'biweekly'    THEN 14
            WHEN 'semimonthly' THEN 14
            WHEN 'semimonthly15' THEN 14
            WHEN 'monthly'     THEN 28
        END as period_lo_days
        ,datediff('day', loan_date, maturity_date) as loan_days
        ,CEIL(loan_days / period_lo_days) as loan_periods
    FROM loans_table
)
SELECT 
    l.loanid,
    CASE payment_frequency 
        WHEN 'weekly' THEN dateadd('week', r.rn, l.loan_date)
        WHEN 'biweekly' THEN dateadd('week', r.rn * 2, l.loan_date)
        WHEN 'semimonthly' THEN 
            case r.rn%2
                when 0 then dateadd('month', r.rn/2, l.loan_date)
                when 1 then dateadd('days', floor(datediff('days', dateadd('month', (r.rn-1)/2, l.loan_date), dateadd('month', (r.rn+1)/2, l.loan_date))/2), dateadd('month', (r.rn-1)/2, l.loan_date))
            end
        WHEN 'semimonthly15' THEN 
            case r.rn%2
                when 0 then dateadd('month', r.rn/2, l.loan_date)
                when 1 then dateadd('days', 15, dateadd('month', (r.rn-1)/2, l.loan_date))
            end

        WHEN 'monthly' THEN dateadd('month', r.rn, l.loan_date)
    END as payment_date,
    l.payment_amount,
    l.payment_frequency,
    l.loan_amount,
    l.loan_amount - least(l.loan_amount, l.payment_amount * r.rn) as still_to_pay,
    least(l.loan_amount - least(l.loan_amount, l.payment_amount * (r.rn-1)), l.payment_amount) as this_payment
FROM loan_enrich AS l
JOIN table_of_numbers AS r 
    ON l.loan_periods >= r.rn
WHERE this_payment > 0
ORDER BY 1, r.rn

LOANID	PAYMENT_DATE	PAYMENT_AMOUNT	PAYMENT_FREQUENCY	LOAN_AMOUNT	STILL_TO_PAY	THIS_PAYMENT
abc123	2022-01-15	50	biweekly	250	200	50
abc123	2022-01-29	50	biweekly	250	150	50
abc123	2022-02-12	50	biweekly	250	100	50
abc123	2022-02-26	50	biweekly	250	50	50
abc123	2022-03-12	50	biweekly	250	0	50
abc124	2022-01-16	50	semimonthly	249	199	50
abc124	2022-02-01	50	semimonthly	249	149	50
abc124	2022-02-15	50	semimonthly	249	99	50
abc124	2022-03-01	50	semimonthly	249	49	50
abc124	2022-03-16	50	semimonthly	249	0	49
abc125	2022-01-16	50	semimonthly15	249	199	50
abc125	2022-02-01	50	semimonthly15	249	149	50
abc125	2022-02-16	50	semimonthly15	249	99	50
abc125	2022-03-01	50	semimonthly15	249	49	50
abc125	2022-03-16	50	semimonthly15	249	0	49