SQL Server 2022

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

create table loans(LoanNumber int,LoanStart date,LoanEnd date
     ,LoanAmount float(53),YrlyInt float(53)
     ,LoanMonth int,PeriodsPerYear float(53));
insert into loans values
 (11111,'2023-07-10','2027-07-06',	11341.98,	6.65,	48 ,12)
,(22222,'2022-03-03','2027-02-25',	11242.78,	3.25,	60 ,12)
,(33333,'2020-07-22','2024-07-25',	10496.91,	3.43,	48 ,12)
,(4    ,'2020-05-11','2027-05-11',  57799.04, 0.0742,   54 ,12.0)
,(5    ,'2018-03-06','2027-05-11',  13885.00, 0.029,   48 ,12.0)
select * from loans;


LoanNumber
LoanStart
LoanEnd
LoanAmount
YrlyInt
LoanMonth
PeriodsPerYear


11111
2023-07-10
2027-07-06
11341.98
6.65
48
12

22222
2022-03-03
2027-02-25
11242.78
3.25
60
12

33333
2020-07-22
2024-07-25
10496.91
3.43
48
12

4
2020-05-11
2027-05-11
57799.04
0.0742
54
12

5
2018-03-06
2027-05-11
13885
0.029
48
12


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
;


LoanNumber
pmt_num
payment_date
beg_balance
scheduled_pmt
amt_to_intrest
amt_to_principal
end_balance


4
1
2020-05-11
57799.04
1262.25
357.39
904.86
56894.18

4
2
2020-06-11
56894.18
1262.25
351.80
910.45
55983.73

4
3
2020-07-11
55983.73
1262.25
346.17
916.08
55067.64

4
4
2020-08-11
55067.64
1262.25
340.50
921.75
54145.90

4
5
2020-09-11
54145.90
1262.25
334.80
927.45
53218.45

4
6
2020-10-11
53218.45
1262.25
329.07
933.18
52285.27

4
7
2020-11-11
52285.27
1262.25
323.30
938.95
51346.32

4
8
2020-12-11
51346.32
1262.25
317.49
944.76
50401.56

4
9
2021-01-11
50401.56
1262.25
311.65
950.60
49450.96

4
10
2021-02-11
49450.96
1262.25
305.77
956.48
48494.48

4
11
2021-03-11
48494.48
1262.25
299.86
962.39
47532.09

4
12
2021-04-11
47532.09
1262.25
293.91
968.34
46563.75

4
13
2021-05-11
46563.75
1262.25
287.92
974.33
45589.42

4
14
2021-06-11
45589.42
1262.25
281.89
980.35
44609.06

4
15
2021-07-11
44609.06
1262.25
275.83
986.42
43622.65

4
16
2021-08-11
43622.65
1262.25
269.73
992.52
42630.13

4
17
2021-09-11
42630.13
1262.25
263.60
998.65
41631.48

4
18
2021-10-11
41631.48
1262.25
257.42
1004.83
40626.65

4
19
2021-11-11
40626.65
1262.25
251.21
1011.04
39615.61

4
20
2021-12-11
39615.61
1262.25
244.96
1017.29
38598.31

4
21
2022-01-11
38598.31
1262.25
238.67
1023.58
37574.73

4
22
2022-02-11
37574.73
1262.25
232.34
1029.91
36544.82

4
23
2022-03-11
36544.82
1262.25
225.97
1036.28
35508.54

4
24
2022-04-11
35508.54
1262.25
219.56
1042.69
34465.85

4
25
2022-05-11
34465.85
1262.25
213.11
1049.14
33416.72

4
26
2022-06-11
33416.72
1262.25
206.63
1055.62
32361.09

4
27
2022-07-11
32361.09
1262.25
200.10
1062.15
31298.94

4
28
2022-08-11
31298.94
1262.25
193.53
1068.72
30230.23

4
29
2022-09-11
30230.23
1262.25
186.92
1075.33
29154.90

4
30
2022-10-11
29154.90
1262.25
180.27
1081.97
28072.93

4
31
2022-11-11
28072.93
1262.25
173.58
1088.66
26984.26

4
32
2022-12-11
26984.26
1262.25
166.85
1095.40
25888.86

4
33
2023-01-11
25888.86
1262.25
160.08
1102.17
24786.69

4
34
2023-02-11
24786.69
1262.25
153.26
1108.98
23677.71

4
35
2023-03-11
23677.71
1262.25
146.41
1115.84
22561.87

4
36
2023-04-11
22561.87
1262.25
139.51
1122.74
21439.13

4
37
2023-05-11
21439.13
1262.25
132.57
1129.68
20309.44

4
38
2023-06-11
20309.44
1262.25
125.58
1136.67
19172.77

4
39
2023-07-11
19172.77
1262.25
118.55
1143.70
18029.07

4
40
2023-08-11
18029.07
1262.25
111.48
1150.77
16878.31

4
41
2023-09-11
16878.31
1262.25
104.36
1157.89
15720.42

4
42
2023-10-11
15720.42
1262.25
97.20
1165.04
14555.38

4
43
2023-11-11
14555.38
1262.25
90.00
1172.25
13383.13

4
44
2023-12-11
13383.13
1262.25
82.75
1179.50
12203.63

4
45
2024-01-11
12203.63
1262.25
75.46
1186.79
11016.84

4
46
2024-02-11
11016.84
1262.25
68.12
1194.13
9822.71

4
47
2024-03-11
9822.71
1262.25
60.74
1201.51
8621.20

4
48
2024-04-11
8621.20
1262.25
53.31
1208.94
7412.26

5
1
2018-03-06
13885.00
306.72
33.56
273.17
13611.83

5
2
2018-04-06
13611.83
306.72
32.90
273.83
13338.01

5
3
2018-05-06
13338.01
306.72
32.23
274.49
13063.52

5
4
2018-06-06
13063.52
306.72
31.57
275.15
12788.37

5
5
2018-07-06
12788.37
306.72
30.91
275.82
12512.55

5
6
2018-08-06
12512.55
306.72
30.24
276.48
12236.07

5
7
2018-09-06
12236.07
306.72
29.57
277.15
11958.92

5
8
2018-10-06
11958.92
306.72
28.90
277.82
11681.09

5
9
2018-11-06
11681.09
306.72
28.23
278.49
11402.60

5
10
2018-12-06
11402.60
306.72
27.56
279.17
11123.44

5
11
2019-01-06
11123.44
306.72
26.88
279.84
10843.60

5
12
2019-02-06
10843.60
306.72
26.21
280.52
10563.08

5
13
2019-03-06
10563.08
306.72
25.53
281.19
10281.89

5
14
2019-04-06
10281.89
306.72
24.85
281.87
10000.01

5
15
2019-05-06
10000.01
306.72
24.17
282.56
9717.46

5
16
2019-06-06
9717.46
306.72
23.48
283.24
9434.22

5
17
2019-07-06
9434.22
306.72
22.80
283.92
9150.30

5
18
2019-08-06
9150.30
306.72
22.11
284.61
8865.69

5
19
2019-09-06
8865.69
306.72
21.43
285.30
8580.39

5
20
2019-10-06
8580.39
306.72
20.74
285.99
8294.41

5
21
2019-11-06
8294.41
306.72
20.04
286.68
8007.73

5
22
2019-12-06
8007.73
306.72
19.35
287.37
7720.36

5
23
2020-01-06
7720.36
306.72
18.66
288.06
7432.29

5
24
2020-02-06
7432.29
306.72
17.96
288.76
7143.53

5
25
2020-03-06
7143.53
306.72
17.26
289.46
6854.08

5
26
2020-04-06
6854.08
306.72
16.56
290.16
6563.92

5
27
2020-05-06
6563.92
306.72
15.86
290.86
6273.06

5
28
2020-06-06
6273.06
306.72
15.16
291.56
5981.50

5
29
2020-07-06
5981.50
306.72
14.46
292.27
5689.23

5
30
2020-08-06
5689.23
306.72
13.75
292.97
5396.26

5
31
2020-09-06
5396.26
306.72
13.04
293.68
5102.58

5
32
2020-10-06
5102.58
306.72
12.33
294.39
4808.19

5
33
2020-11-06
4808.19
306.72
11.62
295.10
4513.08

5
34
2020-12-06
4513.08
306.72
10.91
295.82
4217.27

5
35
2021-01-06
4217.27
306.72
10.19
296.53
3920.74

5
36
2021-02-06
3920.74
306.72
9.48
297.25
3623.49

5
37
2021-03-06
3623.49
306.72
8.76
297.97
3325.53

5
38
2021-04-06
3325.53
306.72
8.04
298.69
3026.84

5
39
2021-05-06
3026.84
306.72
7.31
299.41
2727.43

5
40
2021-06-06
2727.43
306.72
6.59
300.13
2427.30

5
41
2021-07-06
2427.30
306.72
5.87
300.86
2126.45

5
42
2021-08-06
2126.45
306.72
5.14
301.58
1824.86

5
43
2021-09-06
1824.86
306.72
4.41
302.31
1522.55

5
44
2021-10-06
1522.55
306.72
3.68
303.04
1219.51

5
45
2021-11-06
1219.51
306.72
2.95
303.77
915.74

5
46
2021-12-06
915.74
306.72
2.21
304.51
611.23

5
47
2022-01-06
611.23
306.72
1.48
305.24
305.98

5
48
2022-02-06
305.98
306.72
0.74
305.98
0.00

11111
1
2023-07-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
2
2023-08-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
3
2023-09-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
4
2023-10-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
5
2023-11-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
6
2023-12-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
7
2024-01-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
8
2024-02-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
9
2024-03-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
10
2024-04-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
11
2024-05-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
12
2024-06-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
13
2024-07-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
14
2024-08-10
11341.98
6285.35
6285.35
0.00
11341.98

11111
15
2024-09-10
11341.98
6285.35
6285.35
0.00
11341.97

11111
16
2024-10-10
11341.97
6285.35
6285.34
0.00
11341.97

11111
17
2024-11-10
11341.97
6285.35
6285.34
0.00
11341.97

11111
18
2024-12-10
11341.97
6285.35
6285.34
0.01
11341.96

11111
19
2025-01-10
11341.96
6285.35
6285.34
0.01
11341.95

11111
20
2025-02-10
11341.95
6285.35
6285.33
0.02
11341.93

11111
21
2025-03-10
11341.93
6285.35
6285.32
0.03
11341.90

11111
22
2025-04-10
11341.90
6285.35
6285.30
0.04
11341.86

11111
23
2025-05-10
11341.86
6285.35
6285.28
0.07
11341.79

11111
24
2025-06-10
11341.79
6285.35
6285.24
0.10
11341.69

11111
25
2025-07-10
11341.69
6285.35
6285.19
0.16
11341.53

11111
26
2025-08-10
11341.53
6285.35
6285.10
0.25
11341.29

11111
27
2025-09-10
11341.29
6285.35
6284.96
0.38
11340.90

11111
28
2025-10-10
11340.90
6285.35
6284.75
0.60
11340.30

11111
29
2025-11-10
11340.30
6285.35
6284.42
0.93
11339.37

11111
30
2025-12-10
11339.37
6285.35
6283.90
1.45
11337.93

11111
31
2026-01-10
11337.93
6285.35
6283.10
2.25
11335.68

11111
32
2026-02-10
11335.68
6285.35
6281.86
3.49
11332.19

11111
33
2026-03-10
11332.19
6285.35
6279.92
5.42
11326.77

11111
34
2026-04-10
11326.77
6285.35
6276.92
8.43
11318.34

11111
35
2026-05-10
11318.34
6285.35
6272.24
13.10
11305.23

11111
36
2026-06-10
11305.23
6285.35
6264.98
20.36
11284.87

11111
37
2026-07-10
11284.87
6285.35
6253.70
31.65
11253.22

11111
38
2026-08-10
11253.22
6285.35
6236.16
49.19
11204.03

11111
39
2026-09-10
11204.03
6285.35
6208.90
76.45
11127.59

11111
40
2026-10-10
11127.59
6285.35
6166.54
118.81
11008.78

11111
41
2026-11-10
11008.78
6285.35
6100.70
184.65
10824.13

11111
42
2026-12-10
10824.13
6285.35
5998.37
286.98
10537.15

11111
43
2027-01-10
10537.15
6285.35
5839.34
446.01
10091.14

11111
44
2027-02-10
10091.14
6285.35
5592.17
693.17
9397.97

11111
45
2027-03-10
9397.97
6285.35
5208.04
1077.31
8320.66

11111
46
2027-04-10
8320.66
6285.35
4611.03
1674.31
6646.35

11111
47
2027-05-10
6646.35
6285.35
3683.19
2602.16
4044.19

11111
48
2027-06-10
4044.19
6285.35
2241.15
4044.19
0.00

22222
1
2022-03-03
11242.78
3044.92
3044.92
0.00
11242.78

22222
2
2022-04-03
11242.78
3044.92
3044.92
0.00
11242.78

22222
3
2022-05-03
11242.78
3044.92
3044.92
0.00
11242.77

22222
4
2022-06-03
11242.77
3044.92
3044.92
0.00
11242.77

22222
5
2022-07-03
11242.77
3044.92
3044.92
0.00
11242.77

22222
6
2022-08-03
11242.77
3044.92
3044.92
0.01
11242.76

22222
7
2022-09-03
11242.76
3044.92
3044.91
0.01
11242.75

22222
8
2022-10-03
11242.75
3044.92
3044.91
0.01
11242.74

22222
9
2022-11-03
11242.74
3044.92
3044.91
0.01
11242.73

22222
10
2022-12-03
11242.73
3044.92
3044.91
0.01
11242.72

22222
11
2023-01-03
11242.72
3044.92
3044.90
0.02
11242.70

22222
12
2023-02-03
11242.70
3044.92
3044.90
0.02
11242.67

22222
13
2023-03-03
11242.67
3044.92
3044.89
0.03
11242.64

22222
14
2023-04-03
11242.64
3044.92
3044.88
0.04
11242.60

22222
15
2023-05-03
11242.60
3044.92
3044.87
0.05
11242.55

22222
16
2023-06-03
11242.55
3044.92
3044.86
0.06
11242.49

22222
17
2023-07-03
11242.49
3044.92
3044.84
0.08
11242.41

22222
18
2023-08-03
11242.41
3044.92
3044.82
0.10
11242.31

22222
19
2023-09-03
11242.31
3044.92
3044.79
0.13
11242.18

22222
20
2023-10-03
11242.18
3044.92
3044.76
0.16
11242.01

22222
21
2023-11-03
11242.01
3044.92
3044.71
0.21
11241.81

22222
22
2023-12-03
11241.81
3044.92
3044.66
0.27
11241.54

22222
23
2024-01-03
11241.54
3044.92
3044.58
0.34
11241.20

22222
24
2024-02-03
11241.20
3044.92
3044.49
0.43
11240.77

22222
25
2024-03-03
11240.77
3044.92
3044.38
0.54
11240.23

22222
26
2024-04-03
11240.23
3044.92
3044.23
0.69
11239.54

22222
27
2024-05-03
11239.54
3044.92
3044.04
0.88
11238.66

22222
28
2024-06-03
11238.66
3044.92
3043.80
1.12
11237.54

22222
29
2024-07-03
11237.54
3044.92
3043.50
1.42
11236.12

22222
30
2024-08-03
11236.12
3044.92
3043.11
1.81
11234.31

22222
31
2024-09-03
11234.31
3044.92
3042.63
2.30
11232.01

22222
32
2024-10-03
11232.01
3044.92
3042.00
2.92
11229.10

22222
33
2024-11-03
11229.10
3044.92
3041.21
3.71
11225.39

22222
34
2024-12-03
11225.39
3044.92
3040.21
4.71
11220.68

22222
35
2025-01-03
11220.68
3044.92
3038.93
5.99
11214.69

22222
36
2025-02-03
11214.69
3044.92
3037.31
7.61
11207.08

22222
37
2025-03-03
11207.08
3044.92
3035.25
9.67
11197.41

22222
38
2025-04-03
11197.41
3044.92
3032.63
12.29
11185.12

22222
39
2025-05-03
11185.12
3044.92
3029.30
15.62
11169.50

22222
40
2025-06-03
11169.50
3044.92
3025.07
19.85
11149.65

22222
41
2025-07-03
11149.65
3044.92
3019.70
25.22
11124.43

22222
42
2025-08-03
11124.43
3044.92
3012.87
32.05
11092.38

22222
43
2025-09-03
11092.38
3044.92
3004.19
40.74
11051.64

22222
44
2025-10-03
11051.64
3044.92
2993.15
51.77
10999.87

22222
45
2025-11-03
10999.87
3044.92
2979.13
65.79
10934.08

22222
46
2025-12-03
10934.08
3044.92
2961.31
83.61
10850.47

22222
47
2026-01-03
10850.47
3044.92
2938.67
106.25
10744.22

22222
48
2026-02-03
10744.22
3044.92
2909.89
135.03
10609.19

33333
1
2020-07-22
10496.91
3000.38
3000.37
0.02
10496.89

33333
2
2020-08-22
10496.89
3000.38
3000.36
0.02
10496.87

33333
3
2020-09-22
10496.87
3000.38
3000.36
0.03
10496.84

33333
4
2020-10-22
10496.84
3000.38
3000.35
0.04
10496.81

33333
5
2020-11-22
10496.81
3000.38
3000.34
0.05
10496.76

33333
6
2020-12-22
10496.76
3000.38
3000.32
0.06
10496.70

33333
7
2021-01-22
10496.70
3000.38
3000.31
0.08
10496.62

33333
8
2021-02-22
10496.62
3000.38
3000.28
0.10
10496.52

33333
9
2021-03-22
10496.52
3000.38
3000.26
0.13
10496.39

33333
10
2021-04-22
10496.39
3000.38
3000.22
0.17
10496.23

33333
11
2021-05-22
10496.23
3000.38
3000.17
0.21
10496.01

33333
12
2021-06-22
10496.01
3000.38
3000.11
0.27
10495.74

33333
13
2021-07-22
10495.74
3000.38
3000.03
0.35
10495.39

33333
14
2021-08-22
10495.39
3000.38
2999.93
0.45
10494.93

33333
15
2021-09-22
10494.93
3000.38
2999.80
0.58
10494.35

33333
16
2021-10-22
10494.35
3000.38
2999.64
0.75
10493.60

33333
17
2021-11-22
10493.60
3000.38
2999.42
0.96
10492.64

33333
18
2021-12-22
10492.64
3000.38
2999.15
1.24
10491.40

33333
19
2022-01-22
10491.40
3000.38
2998.79
1.59
10489.81

33333
20
2022-02-22
10489.81
3000.38
2998.34
2.05
10487.77

33333
21
2022-03-22
10487.77
3000.38
2997.75
2.63
10485.14

33333
22
2022-04-22
10485.14
3000.38
2997.00
3.38
10481.76

33333
23
2022-05-22
10481.76
3000.38
2996.04
4.35
10477.41

33333
24
2022-06-22
10477.41
3000.38
2994.79
5.59
10471.81

33333
25
2022-07-22
10471.81
3000.38
2993.19
7.19
10464.62

33333
26
2022-08-22
10464.62
3000.38
2991.14
9.25
10455.38

33333
27
2022-09-22
10455.38
3000.38
2988.50
11.89
10443.49

33333
28
2022-10-22
10443.49
3000.38
2985.10
15.29
10428.20

33333
29
2022-11-22
10428.20
3000.38
2980.73
19.66
10408.55

33333
30
2022-12-22
10408.55
3000.38
2975.11
25.27
10383.28

33333
31
2023-01-22
10383.28
3000.38
2967.89
32.50
10350.78

33333
32
2023-02-22
10350.78
3000.38
2958.60
41.79
10308.99

33333
33
2023-03-22
10308.99
3000.38
2946.65
53.73
10255.26

33333
34
2023-04-22
10255.26
3000.38
2931.30
69.09
10186.17

33333
35
2023-05-22
10186.17
3000.38
2911.55
88.84
10097.34

33333
36
2023-06-22
10097.34
3000.38
2886.16
114.23
9983.11

33333
37
2023-07-22
9983.11
3000.38
2853.50
146.88
9836.23

33333
38
2023-08-22
9836.23
3000.38
2811.52
188.86
9647.37

33333
39
2023-09-22
9647.37
3000.38
2757.54
242.85
9404.52

33333
40
2023-10-22
9404.52
3000.38
2688.13
312.26
9092.26

33333
41
2023-11-22
9092.26
3000.38
2598.87
401.51
8690.75

33333
42
2023-12-22
8690.75
3000.38
2484.11
516.28
8174.47

33333
43
2024-01-22
8174.47
3000.38
2336.54
663.85
7510.62

33333
44
2024-02-22
7510.62
3000.38
2146.79
853.60
6657.02

33333
45
2024-03-22
6657.02
3000.38
1902.80
1097.58
5559.44

33333
46
2024-04-22
5559.44
3000.38
1589.07
1411.31
4148.13

33333
47
2024-05-22
4148.13
3000.38
1185.67
1814.71
2333.42

33333
48
2024-06-22
2333.42
3000.38
666.97
2333.42
0.00


  -- select 10 pN,cast( 0.0749 as float)pIr, cast(17864.78 as float) pPv
  --        ,12 pNpy,cast('2020-05-11' as date)pBeg_dt
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;

LoanNumber	LoanStart	LoanEnd	LoanAmount	YrlyInt	LoanMonth	PeriodsPerYear
11111	2023-07-10	2027-07-06	11341.98	6.65	48	12
22222	2022-03-03	2027-02-25	11242.78	3.25	60	12
33333	2020-07-22	2024-07-25	10496.91	3.43	48	12
4	2020-05-11	2027-05-11	57799.04	0.0742	54	12
5	2018-03-06	2027-05-11	13885	0.029	48	12

LoanNumber	pmt_num	payment_date	beg_balance	scheduled_pmt	amt_to_intrest	amt_to_principal	end_balance
4	1	2020-05-11	57799.04	1262.25	357.39	904.86	56894.18
4	2	2020-06-11	56894.18	1262.25	351.80	910.45	55983.73
4	3	2020-07-11	55983.73	1262.25	346.17	916.08	55067.64
4	4	2020-08-11	55067.64	1262.25	340.50	921.75	54145.90
4	5	2020-09-11	54145.90	1262.25	334.80	927.45	53218.45
4	6	2020-10-11	53218.45	1262.25	329.07	933.18	52285.27
4	7	2020-11-11	52285.27	1262.25	323.30	938.95	51346.32
4	8	2020-12-11	51346.32	1262.25	317.49	944.76	50401.56
4	9	2021-01-11	50401.56	1262.25	311.65	950.60	49450.96
4	10	2021-02-11	49450.96	1262.25	305.77	956.48	48494.48
4	11	2021-03-11	48494.48	1262.25	299.86	962.39	47532.09
4	12	2021-04-11	47532.09	1262.25	293.91	968.34	46563.75
4	13	2021-05-11	46563.75	1262.25	287.92	974.33	45589.42
4	14	2021-06-11	45589.42	1262.25	281.89	980.35	44609.06
4	15	2021-07-11	44609.06	1262.25	275.83	986.42	43622.65
4	16	2021-08-11	43622.65	1262.25	269.73	992.52	42630.13
4	17	2021-09-11	42630.13	1262.25	263.60	998.65	41631.48
4	18	2021-10-11	41631.48	1262.25	257.42	1004.83	40626.65
4	19	2021-11-11	40626.65	1262.25	251.21	1011.04	39615.61
4	20	2021-12-11	39615.61	1262.25	244.96	1017.29	38598.31
4	21	2022-01-11	38598.31	1262.25	238.67	1023.58	37574.73
4	22	2022-02-11	37574.73	1262.25	232.34	1029.91	36544.82
4	23	2022-03-11	36544.82	1262.25	225.97	1036.28	35508.54
4	24	2022-04-11	35508.54	1262.25	219.56	1042.69	34465.85
4	25	2022-05-11	34465.85	1262.25	213.11	1049.14	33416.72
4	26	2022-06-11	33416.72	1262.25	206.63	1055.62	32361.09
4	27	2022-07-11	32361.09	1262.25	200.10	1062.15	31298.94
4	28	2022-08-11	31298.94	1262.25	193.53	1068.72	30230.23
4	29	2022-09-11	30230.23	1262.25	186.92	1075.33	29154.90
4	30	2022-10-11	29154.90	1262.25	180.27	1081.97	28072.93
4	31	2022-11-11	28072.93	1262.25	173.58	1088.66	26984.26
4	32	2022-12-11	26984.26	1262.25	166.85	1095.40	25888.86
4	33	2023-01-11	25888.86	1262.25	160.08	1102.17	24786.69
4	34	2023-02-11	24786.69	1262.25	153.26	1108.98	23677.71
4	35	2023-03-11	23677.71	1262.25	146.41	1115.84	22561.87
4	36	2023-04-11	22561.87	1262.25	139.51	1122.74	21439.13
4	37	2023-05-11	21439.13	1262.25	132.57	1129.68	20309.44
4	38	2023-06-11	20309.44	1262.25	125.58	1136.67	19172.77
4	39	2023-07-11	19172.77	1262.25	118.55	1143.70	18029.07
4	40	2023-08-11	18029.07	1262.25	111.48	1150.77	16878.31
4	41	2023-09-11	16878.31	1262.25	104.36	1157.89	15720.42
4	42	2023-10-11	15720.42	1262.25	97.20	1165.04	14555.38
4	43	2023-11-11	14555.38	1262.25	90.00	1172.25	13383.13
4	44	2023-12-11	13383.13	1262.25	82.75	1179.50	12203.63
4	45	2024-01-11	12203.63	1262.25	75.46	1186.79	11016.84
4	46	2024-02-11	11016.84	1262.25	68.12	1194.13	9822.71
4	47	2024-03-11	9822.71	1262.25	60.74	1201.51	8621.20
4	48	2024-04-11	8621.20	1262.25	53.31	1208.94	7412.26
5	1	2018-03-06	13885.00	306.72	33.56	273.17	13611.83
5	2	2018-04-06	13611.83	306.72	32.90	273.83	13338.01
5	3	2018-05-06	13338.01	306.72	32.23	274.49	13063.52
5	4	2018-06-06	13063.52	306.72	31.57	275.15	12788.37
5	5	2018-07-06	12788.37	306.72	30.91	275.82	12512.55
5	6	2018-08-06	12512.55	306.72	30.24	276.48	12236.07
5	7	2018-09-06	12236.07	306.72	29.57	277.15	11958.92
5	8	2018-10-06	11958.92	306.72	28.90	277.82	11681.09
5	9	2018-11-06	11681.09	306.72	28.23	278.49	11402.60
5	10	2018-12-06	11402.60	306.72	27.56	279.17	11123.44
5	11	2019-01-06	11123.44	306.72	26.88	279.84	10843.60
5	12	2019-02-06	10843.60	306.72	26.21	280.52	10563.08
5	13	2019-03-06	10563.08	306.72	25.53	281.19	10281.89
5	14	2019-04-06	10281.89	306.72	24.85	281.87	10000.01
5	15	2019-05-06	10000.01	306.72	24.17	282.56	9717.46
5	16	2019-06-06	9717.46	306.72	23.48	283.24	9434.22
5	17	2019-07-06	9434.22	306.72	22.80	283.92	9150.30
5	18	2019-08-06	9150.30	306.72	22.11	284.61	8865.69
5	19	2019-09-06	8865.69	306.72	21.43	285.30	8580.39
5	20	2019-10-06	8580.39	306.72	20.74	285.99	8294.41
5	21	2019-11-06	8294.41	306.72	20.04	286.68	8007.73
5	22	2019-12-06	8007.73	306.72	19.35	287.37	7720.36
5	23	2020-01-06	7720.36	306.72	18.66	288.06	7432.29
5	24	2020-02-06	7432.29	306.72	17.96	288.76	7143.53
5	25	2020-03-06	7143.53	306.72	17.26	289.46	6854.08
5	26	2020-04-06	6854.08	306.72	16.56	290.16	6563.92
5	27	2020-05-06	6563.92	306.72	15.86	290.86	6273.06
5	28	2020-06-06	6273.06	306.72	15.16	291.56	5981.50
5	29	2020-07-06	5981.50	306.72	14.46	292.27	5689.23
5	30	2020-08-06	5689.23	306.72	13.75	292.97	5396.26
5	31	2020-09-06	5396.26	306.72	13.04	293.68	5102.58
5	32	2020-10-06	5102.58	306.72	12.33	294.39	4808.19
5	33	2020-11-06	4808.19	306.72	11.62	295.10	4513.08
5	34	2020-12-06	4513.08	306.72	10.91	295.82	4217.27
5	35	2021-01-06	4217.27	306.72	10.19	296.53	3920.74
5	36	2021-02-06	3920.74	306.72	9.48	297.25	3623.49
5	37	2021-03-06	3623.49	306.72	8.76	297.97	3325.53
5	38	2021-04-06	3325.53	306.72	8.04	298.69	3026.84
5	39	2021-05-06	3026.84	306.72	7.31	299.41	2727.43
5	40	2021-06-06	2727.43	306.72	6.59	300.13	2427.30
5	41	2021-07-06	2427.30	306.72	5.87	300.86	2126.45
5	42	2021-08-06	2126.45	306.72	5.14	301.58	1824.86
5	43	2021-09-06	1824.86	306.72	4.41	302.31	1522.55
5	44	2021-10-06	1522.55	306.72	3.68	303.04	1219.51
5	45	2021-11-06	1219.51	306.72	2.95	303.77	915.74
5	46	2021-12-06	915.74	306.72	2.21	304.51	611.23
5	47	2022-01-06	611.23	306.72	1.48	305.24	305.98
5	48	2022-02-06	305.98	306.72	0.74	305.98	0.00
11111	1	2023-07-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	2	2023-08-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	3	2023-09-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	4	2023-10-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	5	2023-11-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	6	2023-12-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	7	2024-01-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	8	2024-02-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	9	2024-03-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	10	2024-04-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	11	2024-05-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	12	2024-06-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	13	2024-07-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	14	2024-08-10	11341.98	6285.35	6285.35	0.00	11341.98
11111	15	2024-09-10	11341.98	6285.35	6285.35	0.00	11341.97
11111	16	2024-10-10	11341.97	6285.35	6285.34	0.00	11341.97
11111	17	2024-11-10	11341.97	6285.35	6285.34	0.00	11341.97
11111	18	2024-12-10	11341.97	6285.35	6285.34	0.01	11341.96
11111	19	2025-01-10	11341.96	6285.35	6285.34	0.01	11341.95
11111	20	2025-02-10	11341.95	6285.35	6285.33	0.02	11341.93
11111	21	2025-03-10	11341.93	6285.35	6285.32	0.03	11341.90
11111	22	2025-04-10	11341.90	6285.35	6285.30	0.04	11341.86
11111	23	2025-05-10	11341.86	6285.35	6285.28	0.07	11341.79
11111	24	2025-06-10	11341.79	6285.35	6285.24	0.10	11341.69
11111	25	2025-07-10	11341.69	6285.35	6285.19	0.16	11341.53
11111	26	2025-08-10	11341.53	6285.35	6285.10	0.25	11341.29
11111	27	2025-09-10	11341.29	6285.35	6284.96	0.38	11340.90
11111	28	2025-10-10	11340.90	6285.35	6284.75	0.60	11340.30
11111	29	2025-11-10	11340.30	6285.35	6284.42	0.93	11339.37
11111	30	2025-12-10	11339.37	6285.35	6283.90	1.45	11337.93
11111	31	2026-01-10	11337.93	6285.35	6283.10	2.25	11335.68
11111	32	2026-02-10	11335.68	6285.35	6281.86	3.49	11332.19
11111	33	2026-03-10	11332.19	6285.35	6279.92	5.42	11326.77
11111	34	2026-04-10	11326.77	6285.35	6276.92	8.43	11318.34
11111	35	2026-05-10	11318.34	6285.35	6272.24	13.10	11305.23
11111	36	2026-06-10	11305.23	6285.35	6264.98	20.36	11284.87
11111	37	2026-07-10	11284.87	6285.35	6253.70	31.65	11253.22
11111	38	2026-08-10	11253.22	6285.35	6236.16	49.19	11204.03
11111	39	2026-09-10	11204.03	6285.35	6208.90	76.45	11127.59
11111	40	2026-10-10	11127.59	6285.35	6166.54	118.81	11008.78
11111	41	2026-11-10	11008.78	6285.35	6100.70	184.65	10824.13
11111	42	2026-12-10	10824.13	6285.35	5998.37	286.98	10537.15
11111	43	2027-01-10	10537.15	6285.35	5839.34	446.01	10091.14
11111	44	2027-02-10	10091.14	6285.35	5592.17	693.17	9397.97
11111	45	2027-03-10	9397.97	6285.35	5208.04	1077.31	8320.66
11111	46	2027-04-10	8320.66	6285.35	4611.03	1674.31	6646.35
11111	47	2027-05-10	6646.35	6285.35	3683.19	2602.16	4044.19
11111	48	2027-06-10	4044.19	6285.35	2241.15	4044.19	0.00
22222	1	2022-03-03	11242.78	3044.92	3044.92	0.00	11242.78
22222	2	2022-04-03	11242.78	3044.92	3044.92	0.00	11242.78
22222	3	2022-05-03	11242.78	3044.92	3044.92	0.00	11242.77
22222	4	2022-06-03	11242.77	3044.92	3044.92	0.00	11242.77
22222	5	2022-07-03	11242.77	3044.92	3044.92	0.00	11242.77
22222	6	2022-08-03	11242.77	3044.92	3044.92	0.01	11242.76
22222	7	2022-09-03	11242.76	3044.92	3044.91	0.01	11242.75
22222	8	2022-10-03	11242.75	3044.92	3044.91	0.01	11242.74
22222	9	2022-11-03	11242.74	3044.92	3044.91	0.01	11242.73
22222	10	2022-12-03	11242.73	3044.92	3044.91	0.01	11242.72
22222	11	2023-01-03	11242.72	3044.92	3044.90	0.02	11242.70
22222	12	2023-02-03	11242.70	3044.92	3044.90	0.02	11242.67
22222	13	2023-03-03	11242.67	3044.92	3044.89	0.03	11242.64
22222	14	2023-04-03	11242.64	3044.92	3044.88	0.04	11242.60
22222	15	2023-05-03	11242.60	3044.92	3044.87	0.05	11242.55
22222	16	2023-06-03	11242.55	3044.92	3044.86	0.06	11242.49
22222	17	2023-07-03	11242.49	3044.92	3044.84	0.08	11242.41
22222	18	2023-08-03	11242.41	3044.92	3044.82	0.10	11242.31
22222	19	2023-09-03	11242.31	3044.92	3044.79	0.13	11242.18
22222	20	2023-10-03	11242.18	3044.92	3044.76	0.16	11242.01
22222	21	2023-11-03	11242.01	3044.92	3044.71	0.21	11241.81
22222	22	2023-12-03	11241.81	3044.92	3044.66	0.27	11241.54
22222	23	2024-01-03	11241.54	3044.92	3044.58	0.34	11241.20
22222	24	2024-02-03	11241.20	3044.92	3044.49	0.43	11240.77
22222	25	2024-03-03	11240.77	3044.92	3044.38	0.54	11240.23
22222	26	2024-04-03	11240.23	3044.92	3044.23	0.69	11239.54
22222	27	2024-05-03	11239.54	3044.92	3044.04	0.88	11238.66
22222	28	2024-06-03	11238.66	3044.92	3043.80	1.12	11237.54
22222	29	2024-07-03	11237.54	3044.92	3043.50	1.42	11236.12
22222	30	2024-08-03	11236.12	3044.92	3043.11	1.81	11234.31
22222	31	2024-09-03	11234.31	3044.92	3042.63	2.30	11232.01
22222	32	2024-10-03	11232.01	3044.92	3042.00	2.92	11229.10
22222	33	2024-11-03	11229.10	3044.92	3041.21	3.71	11225.39
22222	34	2024-12-03	11225.39	3044.92	3040.21	4.71	11220.68
22222	35	2025-01-03	11220.68	3044.92	3038.93	5.99	11214.69
22222	36	2025-02-03	11214.69	3044.92	3037.31	7.61	11207.08
22222	37	2025-03-03	11207.08	3044.92	3035.25	9.67	11197.41
22222	38	2025-04-03	11197.41	3044.92	3032.63	12.29	11185.12
22222	39	2025-05-03	11185.12	3044.92	3029.30	15.62	11169.50
22222	40	2025-06-03	11169.50	3044.92	3025.07	19.85	11149.65
22222	41	2025-07-03	11149.65	3044.92	3019.70	25.22	11124.43
22222	42	2025-08-03	11124.43	3044.92	3012.87	32.05	11092.38
22222	43	2025-09-03	11092.38	3044.92	3004.19	40.74	11051.64
22222	44	2025-10-03	11051.64	3044.92	2993.15	51.77	10999.87
22222	45	2025-11-03	10999.87	3044.92	2979.13	65.79	10934.08
22222	46	2025-12-03	10934.08	3044.92	2961.31	83.61	10850.47
22222	47	2026-01-03	10850.47	3044.92	2938.67	106.25	10744.22
22222	48	2026-02-03	10744.22	3044.92	2909.89	135.03	10609.19
33333	1	2020-07-22	10496.91	3000.38	3000.37	0.02	10496.89
33333	2	2020-08-22	10496.89	3000.38	3000.36	0.02	10496.87
33333	3	2020-09-22	10496.87	3000.38	3000.36	0.03	10496.84
33333	4	2020-10-22	10496.84	3000.38	3000.35	0.04	10496.81
33333	5	2020-11-22	10496.81	3000.38	3000.34	0.05	10496.76
33333	6	2020-12-22	10496.76	3000.38	3000.32	0.06	10496.70
33333	7	2021-01-22	10496.70	3000.38	3000.31	0.08	10496.62
33333	8	2021-02-22	10496.62	3000.38	3000.28	0.10	10496.52
33333	9	2021-03-22	10496.52	3000.38	3000.26	0.13	10496.39
33333	10	2021-04-22	10496.39	3000.38	3000.22	0.17	10496.23
33333	11	2021-05-22	10496.23	3000.38	3000.17	0.21	10496.01
33333	12	2021-06-22	10496.01	3000.38	3000.11	0.27	10495.74
33333	13	2021-07-22	10495.74	3000.38	3000.03	0.35	10495.39
33333	14	2021-08-22	10495.39	3000.38	2999.93	0.45	10494.93
33333	15	2021-09-22	10494.93	3000.38	2999.80	0.58	10494.35
33333	16	2021-10-22	10494.35	3000.38	2999.64	0.75	10493.60
33333	17	2021-11-22	10493.60	3000.38	2999.42	0.96	10492.64
33333	18	2021-12-22	10492.64	3000.38	2999.15	1.24	10491.40
33333	19	2022-01-22	10491.40	3000.38	2998.79	1.59	10489.81
33333	20	2022-02-22	10489.81	3000.38	2998.34	2.05	10487.77
33333	21	2022-03-22	10487.77	3000.38	2997.75	2.63	10485.14
33333	22	2022-04-22	10485.14	3000.38	2997.00	3.38	10481.76
33333	23	2022-05-22	10481.76	3000.38	2996.04	4.35	10477.41
33333	24	2022-06-22	10477.41	3000.38	2994.79	5.59	10471.81
33333	25	2022-07-22	10471.81	3000.38	2993.19	7.19	10464.62
33333	26	2022-08-22	10464.62	3000.38	2991.14	9.25	10455.38
33333	27	2022-09-22	10455.38	3000.38	2988.50	11.89	10443.49
33333	28	2022-10-22	10443.49	3000.38	2985.10	15.29	10428.20
33333	29	2022-11-22	10428.20	3000.38	2980.73	19.66	10408.55
33333	30	2022-12-22	10408.55	3000.38	2975.11	25.27	10383.28
33333	31	2023-01-22	10383.28	3000.38	2967.89	32.50	10350.78
33333	32	2023-02-22	10350.78	3000.38	2958.60	41.79	10308.99
33333	33	2023-03-22	10308.99	3000.38	2946.65	53.73	10255.26
33333	34	2023-04-22	10255.26	3000.38	2931.30	69.09	10186.17
33333	35	2023-05-22	10186.17	3000.38	2911.55	88.84	10097.34
33333	36	2023-06-22	10097.34	3000.38	2886.16	114.23	9983.11
33333	37	2023-07-22	9983.11	3000.38	2853.50	146.88	9836.23
33333	38	2023-08-22	9836.23	3000.38	2811.52	188.86	9647.37
33333	39	2023-09-22	9647.37	3000.38	2757.54	242.85	9404.52
33333	40	2023-10-22	9404.52	3000.38	2688.13	312.26	9092.26
33333	41	2023-11-22	9092.26	3000.38	2598.87	401.51	8690.75
33333	42	2023-12-22	8690.75	3000.38	2484.11	516.28	8174.47
33333	43	2024-01-22	8174.47	3000.38	2336.54	663.85	7510.62
33333	44	2024-02-22	7510.62	3000.38	2146.79	853.60	6657.02
33333	45	2024-03-22	6657.02	3000.38	1902.80	1097.58	5559.44
33333	46	2024-04-22	5559.44	3000.38	1589.07	1411.31	4148.13
33333	47	2024-05-22	4148.13	3000.38	1185.67	1814.71	2333.42
33333	48	2024-06-22	2333.42	3000.38	666.97	2333.42	0.00