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.

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,

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,

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
```

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?

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?

I really appricate your help and great effort.

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

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,

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

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.

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.

Open in new window