calculating interest/F.Year without helper column-without using lambda

[arnav yadav]

Book1
	A	B	C	D	E
1	Finncial year	April-March
2
3	Loan Amount	52,800,000.00
4	Annual Interest Rate	5.50%
5	Loan Period In Year	2.00
6	Number of payment Per year	12.00
7
8	starting Date of Payment	28/02/2023
9	No of Payment In current Finncial year	2
10	Interest paymanet/month(Helper Column)
11	$ 242,000.00		Total Interest/financial year
12	$ 232,438.02
13	$ 222,832.21		2022-2023	2023-2024	2024-2025
14	$ 213,182.38		$ 474,438.02	$ 2,027,266.11	$ 576,311.36
15	$ 203,488.31
16	$ 193,749.82
17	$ 183,966.69
18	$ 174,138.72
19	$ 164,265.71
20	$ 154,347.44
21	$ 144,383.72
22	$ 134,374.33
23	$ 124,319.06
24	$ 114,217.71
25	$ 104,070.06
26	$ 93,875.90
27	$ 83,635.02
28	$ 73,347.19
29	$ 63,012.22
30	$ 52,629.88
31	$ 42,199.95
32	$ 31,722.22
33	$ 21,196.46
34	10,622.46
Sheet1

Cell Formulas
Range	Formula
B8	B8	=EOMONTH(TODAY(),0)
A11:A34	A11	=-IPMT(B4/12,SEQUENCE(B5*B6),B5*B6,B3)
C14:E14	C14	=SUBTOTAL(9,OFFSET($A$11,,,SEQUENCE(,B5+1,2,12)))-IFERROR(SUBTOTAL(9,OFFSET($A$11,,,LET(x,SEQUENCE(,3,0,12),IF(x,x-10,x)))),0)
Dynamic array formulas.

 

[Dave Patton]

Interest.xlsm
	C	D	E
11	Total Interest/financial year
12
13	2022-2023	2023-2024	2024-2025
14	474,438.02	2,027,266.11	576,311.36
15	474,438.02	2,027,266.11	576,311.36
16
1a

Cell Formulas
Range	Formula
C14:E14	C14	=SUBTOTAL(9,OFFSET($A$11,,,SEQUENCE(,B5+1,2,12)))-IFERROR(SUBTOTAL(9,OFFSET($A$11,,,LET(x,SEQUENCE(,3,0,12),IF(x,x-10,x)))),0)
C15	C15	=SUM(-IPMT(B4/12,SEQUENCE(2),B5*B6,B3))
D15	D15	=SUM(-IPMT(B4/12,SEQUENCE(14),B5*B6,B3))-C15
E15	E15	=SUM(-IPMT(B4/12,SEQUENCE(24),B5*B6,B3))-SUM(C15:D15)
Dynamic array formulas.

 

[Dave Patton]

or try the following

Interest.xlsm
	C	D	E
16	474,438.02	2,027,266.11	576,311.36
1a

Cell Formulas
Range	Formula
C16	C16	=SUM(-IPMT(B4/12,SEQUENCE(B9),B5*B6,B3))
D16	D16	=SUM(-IPMT(B4/12,SEQUENCE(12,,3),B5*B6,B3))
E16	E16	=SUM(-IPMT(B4/12,SEQUENCE(24-14,,15),B5*B6,B3))

 

[arnav yadav]

or try the following

Interest.xlsm
	C	D	E
16	474,438.02	2,027,266.11	576,311.36
1a

Cell Formulas
Range	Formula
C16	C16	=SUM(-IPMT(B4/12,SEQUENCE(B9),B5*B6,B3))
D16	D16	=SUM(-IPMT(B4/12,SEQUENCE(12,,3),B5*B6,B3))
E16	E16	=SUM(-IPMT(B4/12,SEQUENCE(24-14,,15),B5*B6,B3))

can it be done with single cell spill array formula

 

[Dave Patton]

without helper column-without using lambda!!

If you think one of the posts has given you an answer, please mark it as the answer.

Best wishes!