CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformfinanceamortization
Marco K
Marco K

Reputation: 35

Cross-referecing columns in Snowflake SQL

I'm trying to build an amortization schedule using Snowflake-SQL however I need two columns to reference each other in order to calculate the active and the present value. In excel, it would be something like this:

present value active value

In SQL, I tried doing it like this but it doesn't work:

,LAG(present_value) OVER (PARTITION BY ti.request_id ORDER BY ti.reference_date) AS active_value
,active_value - c.installment_amount AS present_value

How am I able to replicate what I did using excel in SQL?

Upvotes: 1

Views: 146

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26043

so with a janky CTE for the seed data:

with data(y,start_date, amount, interest_y_c4, payment_c9, interest_d_c4_p1) as (
    select *
       ,(0.0007223821155291760::double) + 1.00::double
    from values
    (1,'2021-11-10', 1690.96::double, 0.263669472168149::double, 304.90::double)
), payment_days(x,days, acc_days) as (
    select *
        ,sum(column2) over(order by column1)
    from values
    (1, 0),
    (2, 28),
    (3, 35),
    (4, 28),
    (5, 24),
    (6, 31),
    (7, 30)
), rc as (

and a recursive CTE this can be solved:

with RECURSIVE rec_cte as (
    select
        d.y,
        pd.x + 1 as x,
        pd.days,
        dateadd('days',pd.acc_days, d.start_date)::date as payment_date,
        d.amount as active_value,
        0.0::double as interest,
        d.amount as present_value
    from data as d
    join payment_days as pd
        on pd.x = 1

    UNION ALL

    select 
        d.y,
        pd.x + 1 as x,
        pd.days,
        dateadd('days',pd.acc_days, d.start_date)::date as payment_date,
        round(r.present_value * pow(d.interest_d_c4_p1, pd.days),10) as _active_value,
        (_active_value - r.present_value) as interest,
        greatest(0, _active_value - d.payment_c9) as present_value

    from rec_cte as r
    join data as d
        on r.y = d.y
    join payment_days as pd
        on r.x = pd.x
)
select y as customer_id
    ,days
    ,payment_date
    ,floor(active_value,2) as active_value
    ,floor(interest,2) as interest
    ,floor(present_value,2) as present_value
from rec_cte

giving:

CUSTOMER_ID DAYS PAYMENT_DATE ACTIVE_VALUE INTEREST PRESENT_VALUE
1 0 2021-11-10 1,690.96 0 1,690.96
1 28 2021-12-08 1,725.49 34.53 1,420.59
1 35 2022-01-12 1,456.96 36.36 1,152.06
1 28 2022-02-09 1,175.59 23.53 870.69
1 24 2022-03-05 885.91 15.22 581.01
1 31 2022-04-05 594.16 13.15 289.26
1 30 2022-05-05 295.6 6.33 0

I was not 100% of you daily interest rate or payment, so solving those took that most time. So next please just include them.

I also would not use double money.. but my first take at use number(20,10) was not so successful..

Upvotes: 0

Related Questions