SQL Server Stored Procedure

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:


Who is Participating?
Craig YellickDatabase ArchitectCommented:
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

Craig YellickDatabase ArchitectCommented:
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

Craig YellickDatabase ArchitectCommented:
Also, don't we also need to know the date that the account's first payment was due?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marperAuthor Commented:
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?
marperAuthor Commented:
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.
Craig YellickDatabase ArchitectCommented:
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?
marperAuthor Commented:
Do not worry about my deadline. Of course, soon is better.
I really appricate your help and great effort.
Craig YellickDatabase ArchitectCommented:
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?
marperAuthor Commented:
Yes, that's correct. It needs to return 0 or number of months if different than in the Final Maturity Dte field.
marperAuthor Commented:
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

Craig YellickDatabase ArchitectCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.