sql amortization Schedule Multiple Loans

Question

I have the following code (which is not mine and found on a website which that person did a great job with this)This code does exactly what I need except I am still a newbie and don't understand how I can pass multiple loans through it. I have tried several ways to insert the set command for each declaration and each time I get errors. Any help would be appreciative.

\-------Section I think  my table values go in-------

I am updating this section in response to the answer given. So the Fiddle does what I need the way you have it setup but my only issue is I have read only access for reporting and running sql scripts for data. I can not create function to pass through. Sorry I should have mentioned that. My other issue it that all my loans will be based on 12 months period with varying total months. Sample table data below:

LoanNumber	LoanStart	LoanEnd	LoanAmount	YrlyInt	LoanMonths
11111	07/10/2023	07/06/2027	11341.98	6.65	48
22222	03/03/2022	02/25/2027	11242.78	3.25	60
33333	07/22/2020	07/25/2024	10496.91	3.43	48

I couldn't figure out the logic of how to make it a static period and then use my loanmonths for the actual calculations. SQL fiddle does it right but when I try to change it to my table data that is where it goes buggy.

How it should be:

enter image description here

How mine is: enter image description here

[enter image description here][3]

[enter image description here][4]

[enter image description here][5]

Desired Output here. [3]: https://i.sstatic.net/J3j6D.png [4]: https://i.sstatic.net/bg32u.png [5]: https://i.sstatic.net/BQSHh.png

ValNik · Accepted Answer

See example.
Loans:

create table loans(id int,StartDate date,LoanAmount float,InterestRate float
     ,PeriodsPerYear int);
insert into loans values (1,'2023-01-01',10000.0,0.07,12);
insert into loans values (2,'2023-12-01',20000.0,0.05,12);
select * from loans;

Function

create or alter function calcLoan (
  @pN int =null
  ,@pIr float =null
  ,@pPv float =null
  ,@pNpy int =12
  ,@pBegDate date= null)
RETURNS @query TABLE (
    pmt_num int
  , payment_date date
  , beg_balance decimal(19,2)
  , scheduled_pmt decimal(19,2)
  , amt_to_intrest decimal(19,2)
  , amt_to_principal decimal(19,2)
  , end_balance decimal(19,2)
  )
Begin
DECLARE  @n int = 48,-- total # of payments 
@ir float = .0749,-- annual interest rate (note: enter as a decimal... So %5 would be entered as 0.05...) 
@pv float = 17864.78,-- present value (original loan amount) 
@npy int = 12,-- # of periods per year  
@beg_dt date = GETDATE(); -- the date of the first payment.  

if isnull(@pN,0)>0  set @n=@pN;
if isnull(@pIr,0.0)<>0.0  set @ir=@pIr;
if isnull(@pPv,0.0)<>0.0  set @pv=@pPv;
if isnull(@pNpy,0.0)<>0.0  set @npy=@pNpy;
if @pBegDate is null  set @beg_dt=@pBegDate;

WITH  cte_n1 (n) AS (
  SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
)
,cte_n2 (n) AS (
  SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b
)
, cte_Tally (n) AS (
  SELECT TOP (@n) 
     ROW_NUMBER() OVER (ORDER BY a.n) 
  FROM cte_n2 a CROSS JOIN cte_n2 b ORDER BY a.n 
) 
insert into @query
SELECT  pmt_num = t.n
  , pd.payment_date
  , beg_balance = CONVERT(decimal(19,2), pv.beg_balance)
  , scheduled_pmt = CONVERT(decimal(19, 2), pmt.pmt_calc)
  , amt_to_intrest = CONVERT(decimal(19,2), ipmt.ipmt)
  , amt_to_principal = CONVERT(decimal(19,2), ppmt.ppmt)
  , end_balance = CONVERT(decimal(19,2), pv.end_balance) 
FROM  cte_Tally t 
CROSS APPLY ( VALUES (
        @pv / (POWER(1 + (@ir / @npy), @n) - 1) 
            * ((@ir / @npy) * POWER(1 + (@ir / @npy), @n))
         )) pmt (pmt_calc) 
CROSS APPLY ( VALUES (
            ABS(-@pv * POWER(1 + (@ir / @npy), t.n-1) 
                + pmt.pmt_calc * (POWER(1 + (@ir / @npy), t.n-1) -1) / (@ir / @npy))
          , ABS(-@pv * POWER(1 + (@ir / @npy), t.n) 
                + pmt.pmt_calc * (POWER(1 + (@ir / @npy), t.n) -1) / (@ir / @npy)) 
         )) pv (beg_balance, end_balance) 
CROSS APPLY ( VALUES (pv.beg_balance * (@ir / @npy)
         ) ) ipmt (ipmt) 
CROSS APPLY ( VALUES (pmt.pmt_calc - ipmt.ipmt
             ) ) ppmt (ppmt) 
CROSS APPLY ( VALUES ( 
             CASE WHEN @npy <= 12 THEN DATEADD(MONTH, (12 / @npy) * (t.n - 1), @beg_dt) 
                  WHEN @npy = 26 THEN DATEADD(WEEK, 2 * (t.n - 1), @beg_dt) 
             ELSE DATEADD(DAY, (365 / @npy) * (t.n - 1), @beg_dt) 
             END 
       ) ) pd (payment_date)
;
return;
end;

Use of function

select * from loans l
cross apply calcLoan( 6,l.InterestRate,LoanAmount,l.PeriodsPerYear,l.StartDate)

Output

id	StartDate	LoanAmount	Interest Rate	Periods PerYear	pmt_num	payment_date	beg_ balance	scheduled _pmt	amt_to_ intrest	amt_to_ principal	end_ balance
1	2023-01-01	10000	0.07	12	1	2024-03-06	10000.00	2536.56	58.33	2478.23	7521.77
1	2023-01-01	10000	0.07	12	2	2024-04-06	7521.77	2536.56	43.88	2492.69	5029.08
1	2023-01-01	10000	0.07	12	3	2024-05-06	5029.08	2536.56	29.34	2507.23	2521.85
1	2023-01-01	10000	0.07	12	4	2024-06-06	2521.85	2536.56	14.71	2521.85	0.00
2	2023-12-01	20000	0.05	12	1	2024-03-06	20000.00	5052.19	83.33	4968.86	15031.14
2	2023-12-01	20000	0.05	12	2	2024-04-06	15031.14	5052.19	62.63	4989.56	10041.58
2	2023-12-01	20000	0.05	12	3	2024-05-06	10041.58	5052.19	41.84	5010.35	5031.23
2	2023-12-01	20000	0.05	12	4	2024-06-06	5031.23	5052.19	20.96	5031.23	0.00

Example fiddle

See another example, where stored procedure converted to query
Fiddle

There we can put parameters for query from table as

WITH  params as (
  select LoanMonth pN,YrlyInt as pIr, LoanAmount pPv
        , PeriodsPerYear pNpy,LoanStart pBeg_dt
        ,LoanNumber
  from loans 
  -- filter loans 
  -- where LoanNumber=4  or LoanNumber in(5555,1111)
  )

Or parameters can be external

WITH  params as (
  select @LoanMonth pN,@YrlyInt as pIr, @LoanAmount pPv
        , @PeriodsPerYear pNpy,@LoanStart pBeg_dt
        ,@LoanNumber as LoanNumber
  )

WITH  params as (
  select LoanMonth pN,YrlyInt as pIr, LoanAmount pPv
        , PeriodsPerYear pNpy,LoanStart pBeg_dt
        ,LoanNumber
  from loans -- where LoanNumber=4
  )
, cte_Tally (n) AS (
  select rn
  from(
  SELECT pN, ROW_NUMBER() OVER (ORDER BY a.n) rn
  FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a (n)
  CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b (n)
  CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c (n)
  CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d (n)
  cross apply (select pN from params)e
  ) x
  where rn<=pN
) 

SELECT  LoanNumber
  , pmt_num = t.n
  , pd.payment_date
  , beg_balance = CONVERT(decimal(19,2), pv.beg_balance)
  , scheduled_pmt = CONVERT(decimal(19, 2), pmt.pmt_calc)
  , amt_to_intrest = CONVERT(decimal(19,2), ipmt.ipmt)
  , amt_to_principal = CONVERT(decimal(19,2), ppmt.ppmt)
  , end_balance = CONVERT(decimal(19,2), pv.end_balance) 
FROM  cte_Tally t cross apply params
CROSS APPLY ( VALUES (
        pPv / (POWER(1 + (pIr / pNpy), pN) - 1) 
            * ((pIr / pNpy) * POWER(1 + (pIr / pNpy), pN))
         )) pmt (pmt_calc) 
CROSS APPLY ( VALUES (
            ABS(-pPv * POWER(1 + (pIr / pNpy), t.n-1) 
                + pmt.pmt_calc * (POWER(1 + (pIr / pNpy), t.n-1) -1) / (pIr / pNpy))
          , ABS(-pPv * POWER(1 + (pIr / pNpy), t.n) 
                + pmt.pmt_calc * (POWER(1 + (pIr / pNpy), t.n) -1) / (pIr / pNpy)) 
         )) pv (beg_balance, end_balance) 
CROSS APPLY ( VALUES (pv.beg_balance * (pIr / pNpy)
         ) ) ipmt (ipmt) 
CROSS APPLY ( VALUES (pmt.pmt_calc - ipmt.ipmt
             ) ) ppmt (ppmt) 
CROSS APPLY ( VALUES ( 
             CASE WHEN pNpy <= 12 THEN DATEADD(MONTH, (12 / pNpy) * (t.n - 1), pBeg_dt) 
                  WHEN pNpy = 26 THEN DATEADD(WEEK, 2 * (t.n - 1), pBeg_dt) 
             ELSE DATEADD(DAY, (365 / pNpy) * (t.n - 1), pBeg_dt) 
             END 
       ) ) pd (payment_date)
order by LoanNumber,pmt_num
;

sql amortization Schedule Multiple Loans

Answers (1)

Related Questions