Get Opening Balance from Spilled Range

[gi0rg0s]

I am trying to create a loan movement table using dynamic arrays as in the table below. Period column is spilled using =SEQUENCE(A1), so that it can be adjusted. My problem is that I cannot reference the prior period closing balance in the current period opening balance (red text), without creating a circular reference error. I tried using XLOOKUP, OFFSET and INDEX. The circular reference doesn't really exist, because the previous row is returned, but because Closing Balance range depends on Opening Balance range and vice versa, excel assumes it does.

Period	Opening Balance	Interest	Payment	Closing Balance
1	10.000	100	(1.000)	9.100
2	9.100	100	(1.000)	8.100
...	...	...	...	...

Notes:

1. I would rather not enable iterative calculations if they can be avoided.
2. I can easily solve this using excel tables, but the table size cannot dynamically expand (at least not natively) and the number of periods may vary.
3. Interest and Payment columns are proper spilled ranges in sheet, but not relevant here

 

[Jeffrey Mahoney]

First of all I don't understand the values in the table. Is interest a dollar amount or a percentage? 100%/12?

I would make the Closing balance just a simple formula to calculate the one line

Book5
	A	B	C	D	E
1	Period	Opening Balance	Interest	Payment	Closing Balance
2	1	10.00	100	-1	9.08
3	2	9.08	100	-1	8.17
4	...	...	...	...	...
Sheet1

Cell Formulas
Range	Formula
B3	B3	=XLOOKUP(A3-1,A2:A4,E2:E4,0,0)
E2:E3	E2	=B2+((C2/100)/12)+D2

 

[Jeffrey Mahoney]

Sorry that formula in E2 should be
=B2+(((C2/100)/12)*B2)+D2

 

[gi0rg0s]

Hi Jeffrey, thanks for helping and sorry for not explaining well. The values do not make sense as noted in note 3. Here is how my sheet looks. What I want is to reference F2 in C3 using a spill formula in C2.

I can use a workaround to solve this simple scenario by spilling loan formulas (if anyone needs the solution, here it), but not more complex ones. And there are other instances where I am modeling monthly cash flows (not on a loan) where the workaround just doesn't work. A solution would be greatly appreciated.

00. Full Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	24	Period	Opening	Interest	Payment	Closing		Period	Opening	Interest	Payment	Closing
2	3%	1	100.000	250	(4.298)	95.952		1	100.000	250	(4.298)	95.952
3	100.000	2	Closing	#VALUE!	(4.298)	#VALUE!		2	95.952	240	(4.298)	91.894
4	-4.298,12 €	3	Closing	#VALUE!	(4.298)	#VALUE!		3	91.894	230	(4.298)	87.825
5		4	Closing	#VALUE!	(4.298)	#VALUE!		4	87.825	220	(4.298)	83.747
6		5	Closing	#VALUE!	(4.298)	#VALUE!		5	83.747	209	(4.298)	79.658
7		6	Closing	#VALUE!	(4.298)	#VALUE!		6	79.658	199	(4.298)	75.559
8		7	Closing	#VALUE!	(4.298)	#VALUE!		7	75.559	189	(4.298)	71.450
9		8	Closing	#VALUE!	(4.298)	#VALUE!		8	71.450	179	(4.298)	67.330
10		9	Closing	#VALUE!	(4.298)	#VALUE!		9	67.330	168	(4.298)	63.200
11		10	Closing	#VALUE!	(4.298)	#VALUE!		10	63.200	158	(4.298)	59.060
12		11	Closing	#VALUE!	(4.298)	#VALUE!		11	59.060	148	(4.298)	54.910
13		12	Closing	#VALUE!	(4.298)	#VALUE!		12	54.910	137	(4.298)	50.749
14		13	Closing	#VALUE!	(4.298)	#VALUE!		13	50.749	127	(4.298)	46.578
15		14	Closing	#VALUE!	(4.298)	#VALUE!		14	46.578	116	(4.298)	42.396
16		15	Closing	#VALUE!	(4.298)	#VALUE!		15	42.396	106	(4.298)	38.204
17		16	Closing	#VALUE!	(4.298)	#VALUE!		16	38.204	96	(4.298)	34.001
18		17	Closing	#VALUE!	(4.298)	#VALUE!		17	34.001	85	(4.298)	29.788
19		18	Closing	#VALUE!	(4.298)	#VALUE!		18	29.788	74	(4.298)	25.565
20		19	Closing	#VALUE!	(4.298)	#VALUE!		19	25.565	64	(4.298)	21.330
21		20	Closing	#VALUE!	(4.298)	#VALUE!		20	21.330	53	(4.298)	17.086
22		21	Closing	#VALUE!	(4.298)	#VALUE!		21	17.086	43	(4.298)	12.830
23		22	Closing	#VALUE!	(4.298)	#VALUE!		22	12.830	32	(4.298)	8.564
24		23	Closing	#VALUE!	(4.298)	#VALUE!		23	8.564	21	(4.298)	4.287
25		24	Closing	#VALUE!	(4.298)	#VALUE!		24	4.287	11	(4.298)	0
26
Sheet1

Cell Formulas
Range	Formula
B2:B25	B2	=SEQUENCE(A1)
C2:C25	C2	=IF(B2#=1,A3,F1)
D2:D25	D2	=C2#*$A$2/12
E2:E25	E2	=A4+B2#-B2#
F2:F25	F2	=C2#+D2#+E2#
H2:H25	H2	=SEQUENCE(A1)
I2:I25	I2	=L2#-K2#-J2#
J2:J25	J2	=IPMT(A2/12,H2#,A1,-A3)
K2:K25	K2	=A4+H2#-H2#
L2:L25	L2	=SCAN(A3,J2#+K2#,LAMBDA(a,b,a+b))
A4	A4	=PMT(A2/12,A1,A3)
Dynamic array formulas.