Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

SOLVED: Calculating interest using SQL


I am using PostgreSQL, and have a table for a Billing cycle and another for payments made in a billing cycle.

I am trying to figure out how to calculate interest based on how much Amount was left after each billing cycle's last payment date. Problem is that every time a repayment is made, the interest has to be calculated on the amount remaining after that.

My thoughts on building this query are like this. Build data for all dates from last pay date of the billing cycle to today. Using partitioning, get the remaining amount for the first date. For second date, use amount from previous row and add interest to it, and then calculate interest on this one.

Unfortunately I am stuck just at the thought and can't figure out how to make this into a query!

Here's some sample data to make things easier to understand.

Billing Cycles:

id | ends_at
1 | 2017-11-30
2 | 2017-11-30


amount | billing_cycle_id | type | created_at
6000.0000 | 1 | payment | 2017-11-15 18:40:22.151713
2000.0000 | 1 |repayment| 2017-11-19 11:45:15.6167
2000.0000 | 1 |repayment| 2017-12-02 11:46:40.757897

So if we see, user made a repayment on the 19th, so amount due for interest post ends date(30th Nov 2017), is only 4000. So, from 30th to the 2nd, interest will be calculated daily on 4000. However, from the 2nd, interest needs to be calculated on 2000 only.

Interest Calculations(Today being 2017-12-04):

date | amount | interest
2017-12-01 | 4000 | 100 // First day of pending dues.
2017-12-02 | 2100 | 52.5 // Second day of pending dues.
2017-12-03 | 2152.5 | 53.8125 // Third day of pending dues.
2017-12-04 |2206.3125| // Fourth's day interest will be added tomorrow

Posted in S.E.F
via StackOverflow & StackExchange Atomic Web Robots
This Question have been answered

This post first appeared on Stack Solved, please read the originial post: here

Share the post

SOLVED: Calculating interest using SQL


Subscribe to Stack Solved

Get updates delivered right to your inbox!

Thank you for your subscription