Solved

SQL Server Stored Procedure

Posted on 2009-03-28
11
783 Views
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:

Account_Number
Current_Balance
Final_Maturity_Date  
Interest_Rate
Payment_Amount
Payment_Frequency
Payment_date

Thanks,
0
Comment
Question by:marper
  • 6
  • 5
11 Comments
 
LVL 11

Expert Comment

by:CraigYellick
Comment Utility
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
 

select 

   a.Account_Number

 , 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

0
 
LVL 11

Expert Comment

by:CraigYellick
Comment Utility
Also, don't we also need to know the date that the account's first payment was due?
0
 
LVL 4

Author Comment

by:marper
Comment Utility
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?
 
0
 
LVL 4

Author Comment

by:marper
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:CraigYellick
Comment Utility
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?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 4

Author Comment

by:marper
Comment Utility
Do not worry about my deadline. Of course, soon is better.
I really appricate your help and great effort.
0
 
LVL 11

Expert Comment

by:CraigYellick
Comment Utility
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?
0
 
LVL 4

Author Comment

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

Accepted Solution

by:
CraigYellick earned 250 total points
Comment Utility
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
 

select

   @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 

  end
 

end
 

select 'Months To Go' = @Months_ToGo 

Open in new window

0
 
LVL 4

Author Comment

by:marper
Comment Utility
CraigYelick,
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
Thanks,
 


0
 
LVL 11

Expert Comment

by:CraigYellick
Comment Utility
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.

   http://www.sqlservercentral.com/Forums/Topic485118-8-1.aspx
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now