SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;


id
StartDate
LoanAmount
InterestRate
PeriodsPerYear


1
2023-01-01
10000
0.07
12

2
2023-12-01
20000
0.05
12


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;
select * from loans l
cross apply calcLoan( 4,l.InterestRate,LoanAmount,l.PeriodsPerYear,l.StartDate)


id
StartDate
LoanAmount
InterestRate
PeriodsPerYear
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
2025-03-07
10000.00
2536.56
58.33
2478.23
7521.77

1
2023-01-01
10000
0.07
12
2
2025-04-07
7521.77
2536.56
43.88
2492.69
5029.08

1
2023-01-01
10000
0.07
12
3
2025-05-07
5029.08
2536.56
29.34
2507.23
2521.85

1
2023-01-01
10000
0.07
12
4
2025-06-07
2521.85
2536.56
14.71
2521.85
0.00

2
2023-12-01
20000
0.05
12
1
2025-03-07
20000.00
5052.19
83.33
4968.86
15031.14

2
2023-12-01
20000
0.05
12
2
2025-04-07
15031.14
5052.19
62.63
4989.56
10041.58

2
2023-12-01
20000
0.05
12
3
2025-05-07
10041.58
5052.19
41.84
5010.35
5031.23

2
2023-12-01
20000
0.05
12
4
2025-06-07
5031.23
5052.19
20.96
5031.23
0.00


select 1 id,calcLoan( 6,0.02,20000.00,12)
Msg 195 Level 15 State 10 Line 1
'calcLoan' is not a recognized built-in function name.

id	StartDate	LoanAmount	InterestRate	PeriodsPerYear	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	2025-03-07	10000.00	2536.56	58.33	2478.23	7521.77
1	2023-01-01	10000	0.07	12	2	2025-04-07	7521.77	2536.56	43.88	2492.69	5029.08
1	2023-01-01	10000	0.07	12	3	2025-05-07	5029.08	2536.56	29.34	2507.23	2521.85
1	2023-01-01	10000	0.07	12	4	2025-06-07	2521.85	2536.56	14.71	2521.85	0.00
2	2023-12-01	20000	0.05	12	1	2025-03-07	20000.00	5052.19	83.33	4968.86	15031.14
2	2023-12-01	20000	0.05	12	2	2025-04-07	15031.14	5052.19	62.63	4989.56	10041.58
2	2023-12-01	20000	0.05	12	3	2025-05-07	10041.58	5052.19	41.84	5010.35	5031.23
2	2023-12-01	20000	0.05	12	4	2025-06-07	5031.23	5052.19	20.96	5031.23	0.00