T-SQL Loan Repayments Schedule - PLAN v ACTUAL

• Daniel Taylor-446457

  Mr or Mrs. 500

  Points: 501

  More actions

  November 13, 2018 at 3:44 am

  #416485

  Hi All,

  I have used the forum with some great success in the past an hope you can help with this.  I’m not sure if its even possible?

  In summary; I have a Loan Repayment Schedule table: ([test_DealsRepaymentsPLAN]) where the Interest and Principle repayments are split out based on repayment chunks.  Note the ratio columns.

  Repayments themselves go into table: ([test_DealsRepaymentsACTUAL]). Repayments arrive in variable chunks and dates.  What I would like to do is work out the Interest and Principle split ‘whatever’ the repayment amount is.  I have included some sample data in the table.

  My thoughts are that I need the ACTUAL repayments to reference the ratios in the PLAN table & know how much has been paid ‘so far’ and run a calculation based on those values

  I hope I’ve given enough for far greater minds than mine to help solve – if not please ask and I will elaborate.

  Many thanks
  Dan

  /*
Script creates 2 tables

  1.PLAN where a repayment schedule for a loan is stored. (this  table is fixed and created when a Loan is paid out)
2.ACTUAL where variable repayments are entered  (this is the table where i'd like values to be calculated)

  The challenge is to be able to create a T-SQL script that can 'look' at the ACTUAL RepaymentAmount 
and work out what proportion is INTEREST and PRINCIPLE repayment - Based ratios in the PLAN table 

  the ACTUAL tables has some sample data where it is worked out as required. however I cannot figure how to populate dynamically & where payments overlap ratios.

  (*Note the date of repayments are irrelevant)
*/

  --DROP objects if they exist
IF OBJECT_ID('dbo.test_DealsRepaymentsPLAN', 'U') IS NOT NULL 
DROP TABLE dbo.Scores; 

  IF OBJECT_ID('dbo.test_DealsRepaymentsACTUAL', 'U') IS NOT NULL 
DROP TABLE dbo.Scores; 
--=============================
--Create PLAN payments table with Ratios
--=============================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_DealsRepaymentsPLAN](
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [DealID] [int] NOT NULL,
    [repayment_number] [int] NULL,
    [repayments_remaining] [int] NULL,
    [repayment_date] [date] NULL,
    [RepaymentAmount] [money] NULL,
    [interest_on_repayment] [float] NULL,
    [principle_on_repayment] [float] NULL,
    [ratio_interest] [float] NULL,
    [ratio_repay] [float] NULL,
    [new_balance] [money] NULL,
    [sum_of_repayments_made] [money] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsPLAN] ON 
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (1, 5, 1, 12, CAST(N'2013-01-06' AS Date), 3235.2000, 1357.23, 1877.97, 0.41951965875370922, 0.58048034124629089, 35587.2000, 3235.2000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (2, 5, 2, 11, CAST(N'2013-02-06' AS Date), 3235.2000, 1244.13, 1991.07, 0.38456045994065285, 0.6154395400593472, 32352.0000, 6470.4000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (3, 5, 3, 10, CAST(N'2013-03-06' AS Date), 3235.2000, 1131.03, 2104.17, 0.34960126112759643, 0.65039873887240363, 29116.8000, 9705.6000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (4, 5, 4, 9, CAST(N'2013-04-06' AS Date), 3235.2000, 1017.92, 2217.28, 0.3146389713155292, 0.68536102868447091, 25881.6000, 12940.8000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (5, 5, 5, 8, CAST(N'2013-05-06' AS Date), 3235.2000, 904.82, 2330.38, 0.27967977250247283, 0.72032022749752722, 22646.4000, 16176.0000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (6, 5, 6, 7, CAST(N'2013-06-06' AS Date), 3235.2000, 791.72, 2443.48, 0.24472057368941644, 0.75527942631058365, 19411.2000, 19411.2000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (7, 5, 7, 6, CAST(N'2013-07-06' AS Date), 3235.2000, 678.62, 2556.58, 0.20976137487636004, 0.79023862512364, 16176.0000, 22646.4000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (8, 5, 8, 5, CAST(N'2013-08-06' AS Date), 3235.2000, 565.51, 2669.69, 0.17479908506429279, 0.82520091493570724, 12940.8000, 25881.6000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (9, 5, 9, 4, CAST(N'2013-09-06' AS Date), 3235.2000, 452.41, 2782.79, 0.13983988625123642, 0.86016011374876367, 9705.6000, 29116.8000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (10, 5, 10, 3, CAST(N'2013-10-06' AS Date), 3235.2000, 339.31, 2895.89, 0.10488068743818002, 0.89511931256182, 6470.4000, 32352.0000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (11, 5, 11, 2, CAST(N'2013-11-06' AS Date), 3235.2000, 226.21, 3008.99, 0.069921488625123651, 0.93007851137487629, 3235.2000, 35587.2000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (12, 5, 12, 1, CAST(N'2013-12-06' AS Date), 3235.2000, 113.1, 3122.1, 0.034959198813056383, 0.96504080118694369, 0.0000, 38822.4000)
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsPLAN] OFF
GO

  --==========================================
--Create ACTUAL payments where variable Repayment Amounts can be entered
--==========================================
CREATE TABLE [dbo].[test_DealsRepaymentsACTUAL](
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [RepaymentAmount] [money] NULL,        --user inputs this variable value
    [ratio_Interest] [float] NULL,
    [Interest_Amount] [money] NULL,
    [ratio_Principle] [float] NULL,
    [Principle_Amount] [money] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsACTUAL] ON 
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (1, 3235.2000, 0.419519658753709, 1357.2300, 0.580480341246291, 1877.9700)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (2, 235.2000, 0.38456046, 90.4486, 0.61543954, 144.7514)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (3, 3000.0000, 0.38456046, 1153.6814, 0.61543954, 1846.3186)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (4, 4000.0000, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (5, 2000.0000, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (6, 3000.0000, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsACTUAL] OFF
GO

• Mike01

  SSChampion

  Points: 11393

  More actions

  November 14, 2018 at 9:13 am

  #2013487

  Can you provide what you are expecting for results?  Also, how do the tables relate?

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  November 19, 2018 at 2:38 pm

  #2014038

  Daniel Taylor-446457 - Tuesday, November 13, 2018 3:44 AM

  Hi All,

  I have used the forum with some great success in the past an hope you can help with this.  I’m not sure if its even possible?

  In summary; I have a Loan Repayment Schedule table: ([test_DealsRepaymentsPLAN]) where the Interest and Principle repayments are split out based on repayment chunks.  Note the ratio columns.

  Repayments themselves go into table: ([test_DealsRepaymentsACTUAL]). Repayments arrive in variable chunks and dates.  What I would like to do is work out the Interest and Principle split ‘whatever’ the repayment amount is.  I have included some sample data in the table.

  My thoughts are that I need the ACTUAL repayments to reference the ratios in the PLAN table & know how much has been paid ‘so far’ and run a calculation based on those values

  I hope I’ve given enough for far greater minds than mine to help solve – if not please ask and I will elaborate.

  Many thanks
  Dan

  /*
Script creates 2 tables

  1.PLAN where a repayment schedule for a loan is stored. (this  table is fixed and created when a Loan is paid out)
2.ACTUAL where variable repayments are entered  (this is the table where i'd like values to be calculated)

  The challenge is to be able to create a T-SQL script that can 'look' at the ACTUAL RepaymentAmount 
and work out what proportion is INTEREST and PRINCIPLE repayment - Based ratios in the PLAN table 

  the ACTUAL tables has some sample data where it is worked out as required. however I cannot figure how to populate dynamically & where payments overlap ratios.

  (*Note the date of repayments are irrelevant)
*/

  --DROP objects if they exist
IF OBJECT_ID('dbo.test_DealsRepaymentsPLAN', 'U') IS NOT NULL 
DROP TABLE dbo.Scores; 

  IF OBJECT_ID('dbo.test_DealsRepaymentsACTUAL', 'U') IS NOT NULL 
DROP TABLE dbo.Scores; 
--=============================
--Create PLAN payments table with Ratios
--=============================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_DealsRepaymentsPLAN](
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [DealID] [int] NOT NULL,
    [repayment_number] [int] NULL,
    [repayments_remaining] [int] NULL,
    [repayment_date] [date] NULL,
    [RepaymentAmount] [money] NULL,
    [interest_on_repayment] [float] NULL,
    [principle_on_repayment] [float] NULL,
    [ratio_interest] [float] NULL,
    [ratio_repay] [float] NULL,
    [new_balance] [money] NULL,
    [sum_of_repayments_made] [money] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsPLAN] ON 
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (1, 5, 1, 12, CAST(N'2013-01-06' AS Date), 3235.2000, 1357.23, 1877.97, 0.41951965875370922, 0.58048034124629089, 35587.2000, 3235.2000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (2, 5, 2, 11, CAST(N'2013-02-06' AS Date), 3235.2000, 1244.13, 1991.07, 0.38456045994065285, 0.6154395400593472, 32352.0000, 6470.4000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (3, 5, 3, 10, CAST(N'2013-03-06' AS Date), 3235.2000, 1131.03, 2104.17, 0.34960126112759643, 0.65039873887240363, 29116.8000, 9705.6000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (4, 5, 4, 9, CAST(N'2013-04-06' AS Date), 3235.2000, 1017.92, 2217.28, 0.3146389713155292, 0.68536102868447091, 25881.6000, 12940.8000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (5, 5, 5, 8, CAST(N'2013-05-06' AS Date), 3235.2000, 904.82, 2330.38, 0.27967977250247283, 0.72032022749752722, 22646.4000, 16176.0000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (6, 5, 6, 7, CAST(N'2013-06-06' AS Date), 3235.2000, 791.72, 2443.48, 0.24472057368941644, 0.75527942631058365, 19411.2000, 19411.2000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (7, 5, 7, 6, CAST(N'2013-07-06' AS Date), 3235.2000, 678.62, 2556.58, 0.20976137487636004, 0.79023862512364, 16176.0000, 22646.4000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (8, 5, 8, 5, CAST(N'2013-08-06' AS Date), 3235.2000, 565.51, 2669.69, 0.17479908506429279, 0.82520091493570724, 12940.8000, 25881.6000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (9, 5, 9, 4, CAST(N'2013-09-06' AS Date), 3235.2000, 452.41, 2782.79, 0.13983988625123642, 0.86016011374876367, 9705.6000, 29116.8000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (10, 5, 10, 3, CAST(N'2013-10-06' AS Date), 3235.2000, 339.31, 2895.89, 0.10488068743818002, 0.89511931256182, 6470.4000, 32352.0000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (11, 5, 11, 2, CAST(N'2013-11-06' AS Date), 3235.2000, 226.21, 3008.99, 0.069921488625123651, 0.93007851137487629, 3235.2000, 35587.2000)
GO
INSERT [dbo].[test_DealsRepaymentsPLAN] ([RowID], [DealID], [repayment_number], [repayments_remaining], [repayment_date], [RepaymentAmount], [interest_on_repayment], [principle_on_repayment], [ratio_interest], [ratio_repay], [new_balance], [sum_of_repayments_made]) VALUES (12, 5, 12, 1, CAST(N'2013-12-06' AS Date), 3235.2000, 113.1, 3122.1, 0.034959198813056383, 0.96504080118694369, 0.0000, 38822.4000)
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsPLAN] OFF
GO

  --==========================================
--Create ACTUAL payments where variable Repayment Amounts can be entered
--==========================================
CREATE TABLE [dbo].[test_DealsRepaymentsACTUAL](
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [RepaymentAmount] [money] NULL,        --user inputs this variable value
    [ratio_Interest] [float] NULL,
    [Interest_Amount] [money] NULL,
    [ratio_Principle] [float] NULL,
    [Principle_Amount] [money] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsACTUAL] ON 
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (1, 3235.2000, 0.419519658753709, 1357.2300, 0.580480341246291, 1877.9700)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (2, 235.2000, 0.38456046, 90.4486, 0.61543954, 144.7514)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (3, 3000.0000, 0.38456046, 1153.6814, 0.61543954, 1846.3186)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (4, 4000.0000, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (5, 2000.0000, NULL, NULL, NULL, NULL)
GO
INSERT [dbo].[test_DealsRepaymentsACTUAL] ([RowID], [RepaymentAmount], [ratio_Interest], [Interest_Amount], [ratio_Principle], [Principle_Amount]) VALUES (6, 3000.0000, NULL, NULL, NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[test_DealsRepaymentsACTUAL] OFF
GO


  We'll need a lot more detail.    Most financial institutions have a somewhat complex setup for loan repayments.  Typical is a daily interest amount, which is calculated by dividing the loan interest rate by some fixed number of days; usually either 365 or 360; and then ensuring that the interest portion of the repayment equals the interest charged each day since the last payment.  The principal portion is whatever remains of said payment amount.   However, that setup is also for the more complex interest arrangement.   There are still some companies that use simple interest, which is typically some fixed amount of interest for a given loan, on a per day basis, where you only need the number of days between the last payment and this one to calculate the interest amount, based on that fixed amount for that particular loan.    Boiling that down, we need to know exactly how you are supposed to calculate loan interest, and it's basis (days?)   With this sample data, we would like to know what the results should be, so that we can compare and be sure that our attempts produce the correct results.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)