SQL Server Stored Procedure

Posted on 2009-03-28
Medium Priority
Last Modified: 2012-05-06
Could you guys help me with this code?
I hope it's not too hard.
I need an SQL Server stored procedure that does the follwing calculation:

Teble A:
"      Account_Number
"      Current_Balance
"      Final_Maturity_Date  
"       Interest_Rate (%)
Table B:
"      Account_Number
"      Payment_Amount
"      Payment_Frequency
"      Payment_date

Table A and B join based on an Account_Number

Calculation to be done (This example is for monthly payments only)
"      Take  Current_Balance from Table A and calculate the interest amount for 1 period
"      Calc1 = a.Current_Balance x (( a.Interest_Rate/ 12) / 100)
"      Add this to the balance
o      New_Balance = a.Current_balance + Calc1
"      Subtract the payment amount from the updated balance
o      Ending_Monthly_Balance = New_Balance  b.Payment_amount
"      Add 1 month to the b.Payment_date
"      Go to next month and repeat above calculation
"      Stop when balance becomes zero or negative
"      Compare the payment date calculated to the a.Final_Maturity_Date to determine number of months difference and return that number.

Input arguments:


Question by:marper
  • 6
  • 5
LVL 11

Expert Comment

by:Craig Yellick
ID: 24013142
I ran out of time while working on this so I'll post some t-sql code that creates a test environment we can use to discuss this. Two observations come to mind: 1) in the sample data below, I have created a situation where an account (1003) has never yet made a payment so we handle that correctly. 2) I assume that when an account skips a monthly payment (1002) the interest is applied, and if an account makes two or more payments in a single month (1001) the sum of those payments are applied for that month.

It would be a great help if you posted INSERT statements that populate the tables with all of the cases you must support.
create table Account(
   Account_Number integer
  ,Current_Balance money
  ,Final_Maturity_Date datetime
  ,Interest_Rate decimal(6,4)
create table Payment(
   Account_Number integer
  ,Payment_Amount money
  ,Payment_Frequency integer
  ,Payment_date datetime
insert Account values (1001, 123456, '1/1/2020', 5.125)
insert Account values (1002, 987654, '1/1/2030', 6.5)
insert Account values (1003, 121212, '1/1/2015', 5.75)
-- Account 1001 made an extra payment
insert Payment values(1001, 123, 0, '1/1/2009')
insert Payment values(1001, 123, 0, '2/1/2009')
insert Payment values(1001, 123, 0, '2/15/2009')
insert Payment values(1001, 123, 0, '3/1/2009')
-- Account 1002 skipped a payment
insert Payment values(1002, 987, 0, '1/1/2009')
insert Payment values(1002, 987, 0, '3/1/2009')
-- Account 1003 has not yet made a payment
 , a.Current_Balance
 , Periodic_Interest = 
     Current_Balance * ((Interest_Rate/100)/12)
 , New_Balance = 
     Current_Balance + (Current_Balance * ((Interest_Rate/100)/12))
 , Payment_Amount = isnull(p.Payment_Amount,0)
from Account as a
left outer join Payment as p on p.Account_Number = a.Account_Number

Open in new window

LVL 11

Expert Comment

by:Craig Yellick
ID: 24013223
Also, don't we also need to know the date that the account's first payment was due?

Author Comment

ID: 24013671
CraigYellick, Thanks for your help so far. However, the code you pasted got cut off, and I could not see much value out of it.
As far as tables, they are way more complex in the real life. I just made a simple version to serve this question's purpose.
As far as the date when the paymnet is due, I agree, that needs to be a paramethar too and in this case it's Paymnet_Date field in the B table.
Could you copy the whole code in this window somehow?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 24013686
actually, you were right. The payment_date is a date when an actuall payment is made so you assumed correctlly. To answer your question, let's make a simple assumtion that all payments are due on the last date of a month.
LVL 11

Expert Comment

by:Craig Yellick
ID: 24021842
The code is not cut off, I ran out of time while working on a solution. I only posted the code that sets up tables and test data values so we all have a common understanding of what is going on. The solution is going to be complicated due to the special cases like missing payments and multiple payments.

Regarding dates, it's not so important to know which day of the month payments are due (unless you charge interest calculated by day) but it is important to know when the account was opened and started owing money. In the sample data I posted, account 1003 has not yet made a payment. Is that because no payment is due yet, or has the account missed several payments already? We need to know the starting date when payments are expected.

I should have some time to work on this today. Are you up against a deadline?

Author Comment

ID: 24021900
Do not worry about my deadline. Of course, soon is better.
I really appricate your help and great effort.
LVL 11

Expert Comment

by:Craig Yellick
ID: 24023055
I was re-reading your question and want to make sure that I understand this item:  "Compare the payment date calculated to the a.Final_Maturity_Date to determine number of months difference and return that number".

So ultimately the procedure only needs to return one number, and not the table of projected payments?

Author Comment

ID: 24023148
Yes, that's correct. It needs to return 0 or number of months if different than in the Final Maturity Dte field.
LVL 11

Accepted Solution

Craig Yellick earned 1000 total points
ID: 24028637
Here's a first try at the logic. I was trying to be fancy and accomplish this with a SELECT statement but gave up. It's implemented as a loop.

To make this a lot easier to test it is not implemented as a procedure. Instead just set the input variables and see the result. If all is well it's easy to convert to a procedure with formal parameters. I didn't see any reason to pass more than the account number and expected payment amount since the remainder can be looked up.  

Note that the combined declare/set works with SQL Server 2008. If you're using an earlier version you'll have to break the declare/set lines into two separate statements.
/* Input Parameters */
declare @Account_Number int = 1001
declare @Payment_Amount money = 1234
/* Look ups */
declare @Current_Balance money 
declare @Final_Maturity_Date datetime
declare @Interest_Rate decimal(6,4)
declare @Payment_Date datetime
   @Current_Balance     = Current_Balance 
  ,@Final_Maturity_Date = Final_Maturity_Date 
  ,@Interest_Rate       = Interest_Rate 
from Account where Account_Number = @Account_Number 
set @Payment_Date = (
  select max(Payment_Date) from Payment
  where Account_Number = @Account_Number 
/* Starting Assumptions */
declare @Monthly_Rate decimal(6,4) = @Interest_Rate /100 /12
declare @Balance_Due money = @Current_Balance
declare @Months_ToGo int = datediff(month, @Payment_Date, @Final_Maturity_Date)
/* Only process if loan is not already mature */
if @Months_ToGo > 0 begin
  /* Reset months to go, we will figure it out below */
  set @Months_ToGo = 0
  /* Loop through remaining payments.
     Assumption is that interest was already part 
     of the most recent payment so additional interest
     is added starting the next payment.
  declare @Interest_Due money = 0
  while @Balance_Due > 0 begin
    set @Months_ToGo = @Months_ToGo + 1
    set @Balance_Due = @Balance_Due - (@Payment_Amount - @Interest_Due)
    set @Interest_Due = @Balance_Due * @Monthly_Rate 
select 'Months To Go' = @Months_ToGo 

Open in new window


Author Comment

ID: 24029267
This is great. It will serv me as a great starting point and you almost finish the whole job.
I have one question for you at this point...
Where did you implement the original logic I specified when I said that:
Take  Current_Balance from Table A and calculate the interest amount for 1 period: Calc1 = a.Current_Balance x (( a.Interest_Rate/ 12) / 100)
  Add this to the balance
  New_Balance = a.Current_balance + Calc1

LVL 11

Expert Comment

by:Craig Yellick
ID: 24029388
The account's interest rate is looked up. The following line comes up with the monthly interest rate:

   declare @Monthly_Rate decimal(6,4) = @Interest_Rate /100 /12

I suspect that the logic for applying payments and interest is wrong but at least we have a starting point. In the body of the loop the balance due is declining by the payment amount minus the interest owed.  That is, the balance declines by the principal portion of the payment and not the interest. Since the principal went down a little, the next month's interest due will be slighly less.

    set @Balance_Due = @Balance_Due - (@Payment_Amount - @Interest_Due)
    set @Interest_Due = @Balance_Due * @Monthly_Rate

Per the comment just ahead of the loop, I assume that that interest due was already part of the first payment. This is probably wrong but I figured you can adjust the logic to meet your particular situation. Usually there are a suite of financial functions that calculate this sort of thing and have all the appropriate conditions handled. For example, the PMT function in Excel. Since this is not present in SQL Server we have to work it out on our own.

Here's a discussion of various financial functions implemented in T-SQL. Might be a better fit.


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question