Goal Seek or VBA Solution?

[spacecaptainsuperguy]

I've created an amortization schedule for a loan that accrues interest daily, which works slightly different than all the generic amortization templates I've been able to find that accrue interest monthly. My challenge is I'm trying to set up a goal seek to find what the payment will be to correctly pay off the loan at the maturity date. Every time I do a goal seek it seems to randomly select a number and if that number (i.e. the payment amount) is higher than what the goal seek number should be, it thinks it has found the answer because the loan balance is zero at the maturity date. Of course it is zero because goal seek's answer paid the loan off months early.

I thought that I might could do a macro that simply starts counting upwards until the maturity date shows a zero balance, but since the range of payments can be as little as $100 or as high as $15,000, my simple and unsuccessful attempts are incredibly slow going penny by penny. Does anyone have any thoughts on how I can try to solve this problem or get goal seek to start at zero and count up from there?

Thanks in advance

 

[Dave Patton]

Can you post an extract of your spreadsheet say 10 rows that illustrate your challenge. The forum provides the tool XL2BB.

 

[spacecaptainsuperguy]

Can you post an extract of your spreadsheet say 10 rows that illustrate your challenge. The forum provides the tool XL2BB.

Hopefully this works...

Payment Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	6.00%	15	11/15/2018	10/15/2033	$1,695.75		200,000.00	Late Fee:	0.00%	10.00		-	Acct #	OBR-004	Input Fields
2	Int Rate	Term in Yrs	Funding Date	Maturity Date	Pymt	Add'l Pymt	Beg. Balance		Percent	Fixed		Bal @ Maturity
3					1,148.72							1,143.08
4	Date Received	Date Due	Days Late	Reference	Payment Code	Total Amount Received	Tot. Applied to Int.	Applied To Interest	Applied To Principal	Other	Applied To Late Charges	Unpaid Interest	Unpaid Interest Running Bal	Principal Balance	Specific Pymt Interest	Specific Pymt Principal
5	11/15/2018	11/15/2018	-			-		-	-	-	-	-	-	200,000.00
6	12/15/2018	12/15/2018	-			1,695.75	986.30	986.30	709.45		-	-	-	199,290.55
7	01/15/2019	1/15/2019	-			1,695.75	1,015.56	1,015.56	680.19		-	-	-	198,610.36
8	02/15/2019	2/15/2019	-			1,695.75	1,012.10	1,012.10	683.65		-	-	-	197,926.71
9	03/15/2019	3/15/2019	-			1,695.75	911.01	911.01	784.74		-	-	-	197,141.97
10	04/15/2019	4/15/2019	-			1,695.75	1,004.61	1,004.61	691.14		-	-	-	196,450.83
Amort Sched

Cell Formulas
Range	Formula
D1	D1	=INDEX(B5:B450,MATCH(EDATE(C1,B1*12),B5:B450))
L1	L1	=INDEX(N5:N450,MATCH(EDATE(C1,B1*12),B5:B450))
E3	E3	=INDEX(F6:F451,MATCH(EDATE(C1,B1*12),B6:B451))
L3	L3	=INDEX(N5:N450,MATCH(EDATE(C1,(B1*12)-2),B5:B450))
A5:A10	A5	=+B5
B5	B5	=C1
N5	N5	=G1
B6:B10	B6	=IFERROR(IF(EDATE(B5,1)<EDATE($C$1,$B$1*12),EDATE(B5,1),""),"")
C6:C10	C6	=IFERROR(DAYS(A6,B6),"")
F6:F10	F6	=IFERROR(IF($E$1+$F$1>N5,N5+(N5*($A$1/365)*DAYS(A6,A5)),$E$1+$F$1),0)
G6:G10	G6	=IF(L6<0,H6,H6+L6)
H6	H6	=IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)-SUM($L$5:L5)+K6,((N5*$A$1)/365)*DAYS(A6,A5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)+J6+K6,((N5*$A$1)/365)*DAYS(A6,A5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5),((N5*$A$1)/365)*DAYS(A6,A5),F6-J6-K6)))
I6:I10	I6	=IF(F6=0,P6,IF(L6<0,0,IF(H6+J6+K6+L6=F6,0,IF(H6+J6+K6+L6<F6,F6-H6-J6-K6-L6,0))))
H7:H10	H7	=IF(A7="",0,IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))-SUM($L$5:L6)+K7,((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+J7+K7,((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),F7-J7-K7))))
K6:K10	K6	=IF(AND(C6>10,C6<>""),IF($J$1=0,$E$1*$I$1,$J$1),0)
L6	L6	=IF(F6=0,O6,IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)-SUM($L$5:L5)+J6+K6,-SUM($L$5:L5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)+J6+K6,MIN(-SUM($L$5:L5),F6-H6-J6-K6),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5),((N5*$A$1)/365)*DAYS(A6,A5),-((N5*$A$1)/365)*DAYS(A6,A5)+H6))))
M6:M10	M6	=+L6+M5
N6:N10	N6	=+N5-I6
L7:L10	L7	=IF(F7=0,O7,IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))-SUM($L$5:L6)+J7+K7,-SUM($L$5:L6),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+J7+K7,MIN(-SUM($L$5:L6),F7-H7-J7-K7),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),-((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+H7))))

Named Ranges
Name	Refers To	Cells
'Amort Sched'!solver_adj	='Amort Sched'!$E$1	K6:K10, F6:F10
'Amort Sched'!solver_rhs1	='Amort Sched'!$E$1	K6:K10, F6:F10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:N450	Cell Value	=0	text	NO

 

[Dave Patton]

The Goal Seek can adjust the payments so the final balance is 0 or a specified value.

I will post a before and after example.

T202211a.xlsm
	A	B	C	D	E	F
1
2	Rate	2.50%
3		0.01%
4	Payment	400.00
5
6	Date	Num	Payment	Interest	Additional	Principal
7	1-Nov-22					6,000.00
8	30-Sep-22	1	400.00	-13.15		5,586.85
9	31-Oct-22	2	400.00	11.86		5,198.71
10	30-Nov-22	3	400.00	10.68	1,000.00	5,809.39
11	31-Dec-22	4	400.00	12.34		5,421.73
12	31-Jan-23	5	400.00	11.51		5,033.24
13	28-Feb-23	6	400.00	9.65		4,642.89
14	31-Mar-23	7	400.00	9.86		4,252.75
15	30-Apr-23	8	400.00	8.74		3,861.49
16	31-May-23	9	400.00	8.20		3,469.69
17	30-Jun-23	10	400.00	7.13		3,076.82
18	31-Jul-23	11	400.00	6.53	1,500.00	4,183.35
19	31-Aug-23	12	400.00	8.88		3,792.23
20	30-Sep-23	13	400.00	7.79		3,400.02
21	31-Oct-23	14	400.00	7.22		3,007.24
22	30-Nov-23	15	400.00	6.18		2,613.42
23	31-Dec-23	16	400.00	5.55		2,218.97
24
4b

Cell Formulas
Range	Formula
B3	B3	=B2/365
C8:C23	C8	=$B$4
D8:D23	D8	=ROUND(F7*$B$3*(A8-A7),2)
B9:B23	B9	=B8+1
F8:F23	F8	=F7+D8-C8+E8

T202211a.xlsm
	A	B	C	D	E	F
1
2	Rate	2.50%
3		0.01%
4	Payment	536.53
5
6	Date	Num	Payment	Interest	Additional	Principal
7	1-Nov-22					6,000.00
8	30-Sep-22	1	536.53	-13.15		5,450.32
9	31-Oct-22	2	536.53	11.57		4,925.37
10	30-Nov-22	3	536.53	10.12	1,000.00	5,398.96
11	31-Dec-22	4	536.53	11.46		4,873.90
12	31-Jan-23	5	536.53	10.35		4,347.72
13	28-Feb-23	6	536.53	8.34		3,819.54
14	31-Mar-23	7	536.53	8.11		3,291.12
15	30-Apr-23	8	536.53	6.76		2,761.36
16	31-May-23	9	536.53	5.86		2,230.69
17	30-Jun-23	10	536.53	4.58		1,698.75
18	31-Jul-23	11	536.53	3.61	1,500.00	2,665.83
19	31-Aug-23	12	536.53	5.66		2,134.97
20	30-Sep-23	13	536.53	4.39		1,602.83
21	31-Oct-23	14	536.53	3.40		1,069.71
22	30-Nov-23	15	536.53	2.20		535.38
23	31-Dec-23	16	536.53	1.14		0.00
4b

Cell Formulas
Range	Formula
B3	B3	=B2/365
C8:C23	C8	=$B$4
D8:D23	D8	=ROUND(F7*$B$3*(A8-A7),2)
B9:B23	B9	=B8+1
F8:F23	F8	=F7+D8-C8+E8

 

[spacecaptainsuperguy]

The Goal Seek can adjust the payments so the final balance is 0 or a specified value.

I will post a before and after example.

Thanks. Ya. I haven't had any problems with getting goal seek to work on amortization schedules like the ones you used that calculate monthly accrued interest. The one that I've built works for loans that accrue daily (i.e. interest is calculated from pay date to pay date and that, depending on when the payment is made, can result in the interest accrued being more than the regular payment resulting in unpaid interest. I don't know if that added complexity is giving Goal seek a challenge or what. The loans I'm usually dealing with are 20-30 year loans, but even if you do a 2 year loan, goal seek still gives me a bad number. In this example, I'm setting goal seek to set cell L1 to zero by changing E1 and here is what it gives me.

Payment Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	6.00%	2	11/15/2018	10/15/2020	$56.32		1,000.00	Late Fee:	0.00%	10.00		-	Acct #	OBR-004	Input Fields
2	Int Rate	Term in Yrs	Funding Date	Maturity Date	Pymt	Add'l Pymt	Beg. Balance		Percent	Fixed		Bal @ Maturity
3					-							-
4	Date Received	Date Due	Days Late	Reference	Payment Code	Total Amount Received	Tot. Applied to Int.	Applied To Interest	Applied To Principal	Other	Applied To Late Charges	Unpaid Interest	Unpaid Interest Running Bal	Principal Balance	Specific Pymt Interest	Specific Pymt Principal
5	11/15/2018	11/15/2018	-			-		-	-	-	-	-	-	1,000.00
6	12/15/2018	12/15/2018	-			56.32	4.93	4.93	51.39		-	-	-	948.61
7	01/15/2019	1/15/2019	-			56.32	4.83	4.83	51.49		-	-	-	897.13
8	02/15/2019	2/15/2019	-			56.32	4.57	4.57	51.75		-	-	-	845.38
9	03/15/2019	3/15/2019	-			56.32	3.89	3.89	52.43		-	-	-	792.95
10	04/15/2019	4/15/2019	-			56.32	4.04	4.04	52.28		-	-	-	740.67
11	05/15/2019	5/15/2019	-			56.32	3.65	3.65	52.67		-	-	-	688.00
12	06/15/2019	6/15/2019	-			56.32	3.51	3.51	52.81		-	-	-	635.19
13	07/15/2019	7/15/2019	-			56.32	3.13	3.13	53.19		-	-	-	582.00
14	08/15/2019	8/15/2019	-			56.32	2.97	2.97	53.35		-	-	-	528.65
15	09/15/2019	9/15/2019	-			56.32	2.69	2.69	53.63		-	-	-	475.02
16	10/15/2019	10/15/2019	-			56.32	2.34	2.34	53.98		-	-	-	421.04
17	11/15/2019	11/15/2019	-			56.32	2.15	2.15	54.17		-	-	-	366.87
18	12/15/2019	12/15/2019	-			56.32	1.81	1.81	54.51		-	-	-	312.36
19	01/15/2020	1/15/2020	-			56.32	1.59	1.59	54.73		-	-	-	257.63
20	02/15/2020	2/15/2020	-			56.32	1.31	1.31	55.01		-	-	-	202.62
21	03/15/2020	3/15/2020	-			56.32	0.97	0.97	55.35		-	-	-	147.27
22	04/15/2020	4/15/2020	-			56.32	0.75	0.75	55.57		-	-	-	91.70
23	05/15/2020	5/15/2020	-			56.32	0.45	0.45	55.87		-	-	-	35.83
24	06/15/2020	6/15/2020	-			36.01	0.18	0.18	35.83		-	-	-	-
25	07/15/2020	7/15/2020	-			-	-	-	-		-	-	-	-
26	08/15/2020	8/15/2020	-			-	-	-	-		-	-	-	-
27	09/15/2020	9/15/2020	-			-	-	-	-		-	-	-	-
28	10/15/2020	10/15/2020	-			-	-	-	-		-	-	-	-
Amort Sched

Cell Formulas
Range	Formula
D1	D1	=INDEX(B5:B450,MATCH(EDATE(C1,B1*12),B5:B450))
L1	L1	=INDEX(N5:N450,MATCH(EDATE(C1,B1*12),B5:B450))
E3	E3	=INDEX(F6:F451,MATCH(EDATE(C1,B1*12),B6:B451))
L3	L3	=INDEX(N5:N450,MATCH(EDATE(C1,(B1*12)-2),B5:B450))
A5:A28	A5	=+B5
B5	B5	=C1
N5	N5	=G1
B6:B28	B6	=IFERROR(IF(EDATE(B5,1)<EDATE($C$1,$B$1*12),EDATE(B5,1),""),"")
C6:C28	C6	=IFERROR(DAYS(A6,B6),"")
F6:F28	F6	=IFERROR(IF($E$1+$F$1>N5,N5+(N5*($A$1/365)*DAYS(A6,A5)),$E$1+$F$1),0)
G6:G28	G6	=IF(L6<0,H6,H6+L6)
H6	H6	=IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)-SUM($L$5:L5)+K6,((N5*$A$1)/365)*DAYS(A6,A5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)+J6+K6,((N5*$A$1)/365)*DAYS(A6,A5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5),((N5*$A$1)/365)*DAYS(A6,A5),F6-J6-K6)))
I6:I28	I6	=IF(F6=0,P6,IF(L6<0,0,IF(H6+J6+K6+L6=F6,0,IF(H6+J6+K6+L6<F6,F6-H6-J6-K6-L6,0))))
H7:H28	H7	=IF(A7="",0,IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))-SUM($L$5:L6)+K7,((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+J7+K7,((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),F7-J7-K7))))
K6:K28	K6	=IF(AND(C6>10,C6<>""),IF($J$1=0,$E$1*$I$1,$J$1),0)
L6	L6	=IF(F6=0,O6,IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)-SUM($L$5:L5)+J6+K6,-SUM($L$5:L5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)+J6+K6,MIN(-SUM($L$5:L5),F6-H6-J6-K6),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5),((N5*$A$1)/365)*DAYS(A6,A5),-((N5*$A$1)/365)*DAYS(A6,A5)+H6))))
M6:M28	M6	=+L6+M5
N6:N28	N6	=+N5-I6
L7:L28	L7	=IF(F7=0,O7,IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))-SUM($L$5:L6)+J7+K7,-SUM($L$5:L6),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+J7+K7,MIN(-SUM($L$5:L6),F7-H7-J7-K7),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),-((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+H7))))

Named Ranges
Name	Refers To	Cells
'Amort Sched'!solver_adj	='Amort Sched'!$E$1	K6:K28, F6:F28
'Amort Sched'!solver_rhs1	='Amort Sched'!$E$1	K6:K28, F6:F28

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:N450	Cell Value	=0	text	NO

 

[Dave Patton]

You cannot change the payments that are already made.
I have not reviewed your formulas.
I changed the dates.
I made the payments fixed per E1.
I ran Goal Seek to Change N21 to 0 by revising E1.
This shows the payments that would be required.

Hopefully someone else can visualize your question.

T202211a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	0.06	2	15-11-22	45366	130.36843		2000	Late Fee:	0	10		0	Acct #	OBR-004
2	Int Rate	Term in Yrs	Funding Date	Maturity Date	Pymt	Add'l Pymt	Beg. Balance		Percent	Fixed		Bal @ Maturity
3					130.36843							0
4	Date Received	Date Due	Days Late	Reference	Payment Code	Total Amount Received	Tot. Applied to Int.	Applied To Interest	Applied To Principal	Other	Applied To Late Charges	Unpaid Interest	Unpaid Interest Running Bal	Principal Balance
5	15-Nov-22	15-Nov-22	0			0		0	0	0	0	0	0	2000
6	15-Dec-22	15-Dec-22	0			130.36843	9.8630137	9.8630137	120.50541		0	0	0	1879.4946
7	15-Jan-23	15-Jan-23	0			130.36843	9.5776984	9.5776984	120.79073		0	0	0	1758.7039
8	15-Feb-23	15-Feb-23	0			130.36843	8.9621621	8.9621621	121.40626		0	0	0	1637.2976
9	15-Mar-23	15-Mar-23	0			130.36843	7.5360547	7.5360547	122.83237		0	0	0	1514.4652
10	15-Apr-23	15-Apr-23	0			130.36843	7.7175488	7.7175488	122.65088		0	0	0	1391.8143
11	15-May-23	15-May-23	0			130.36843	6.863742	6.863742	123.50468		0	0	0	1268.3097
12	15-Jun-23	15-Jun-23	0			130.36843	6.4631671	6.4631671	123.90526		0	0	0	1144.4044
13	15-Jul-23	15-Jul-23	0			130.36843	5.6436382	5.6436382	124.72479		0	0	0	1019.6796
14	15-Aug-23	15-Aug-23	0			130.36843	5.1961756	5.1961756	125.17225		0	0	0	894.50737
15	15-Sep-23	15-Sep-23	0			130.36843	4.5583115	4.5583115	125.81011		0	0	0	768.69726
16	15-Oct-23	15-Oct-23	0			130.36843	3.7908358	3.7908358	126.57759		0	0	0	642.11967
17	15-Nov-23	15-Nov-23	0			130.36843	3.2721714	3.2721714	127.09625		0	0	0	515.02341
18	15-Dec-23	15-Dec-23	0			130.36843	2.5398415	2.5398415	127.82858		0	0	0	387.19483
19	15-Jan-24	15-Jan-24	0			130.36843	1.9731024	1.9731024	128.39532		0	0	0	258.7995
20	15-Feb-24	15-Feb-24	0			130.36843	1.3188139	1.3188139	129.04961		0	0	0	129.74989
21	15-Mar-24	15-Mar-24	0			130.36843	0.6185337	0.6185337	129.74989		0	0	0	0
4bb

Cell Formulas
Range	Formula
D1	D1	=INDEX(B5:B447,MATCH(EDATE(C1,B1*12),B5:B447))
L1	L1	=INDEX(N5:N447,MATCH(EDATE(C1,B1*12),B5:B447))
E3	E3	=INDEX(F6:F448,MATCH(EDATE(C1,B1*12),B6:B448))
L3	L3	=INDEX(N5:N447,MATCH(EDATE(C1,(B1*12)-2),B5:B447))
A5:A21	A5	=+B5
B5	B5	=C1
N5	N5	=G1
B6:B21	B6	=IFERROR(IF(EDATE(B5,1)<EDATE($C$1,$B$1*12),EDATE(B5,1),""),"")
C6:C21	C6	=IFERROR(DAYS(A6,B6),"")
F6	F6	=$E$1
G6:G21	G6	=IF(L6<0,H6,H6+L6)
H6	H6	=IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)-SUM($L$5:L5)+K6,((N5*$A$1)/365)*DAYS(A6,A5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)+J6+K6,((N5*$A$1)/365)*DAYS(A6,A5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5),((N5*$A$1)/365)*DAYS(A6,A5),F6-J6-K6)))
I6:I21	I6	=IF(F6=0,P6,IF(L6<0,0,IF(H6+J6+K6+L6=F6,0,IF(H6+J6+K6+L6<F6,F6-H6-J6-K6-L6,0))))
F7:F21	F7	=IFERROR(IF($E$1+$F$1>N6,N6+(N6*($A$1/365)*DAYS(A7,A6)),$E$1+$F$1),0)
H7:H21	H7	=IF(A7="",0,IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))-SUM($L$5:L6)+K7,((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+J7+K7,((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),F7-J7-K7))))
K6:K21	K6	=IF(AND(C6>10,C6<>""),IF($J$1=0,$E$1*$I$1,$J$1),0)
L6	L6	=IF(F6=0,O6,IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)-SUM($L$5:L5)+J6+K6,-SUM($L$5:L5),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5)+J6+K6,MIN(-SUM($L$5:L5),F6-H6-J6-K6),IF(F6>=((N5*$A$1)/365)*DAYS(A6,A5),((N5*$A$1)/365)*DAYS(A6,A5),-((N5*$A$1)/365)*DAYS(A6,A5)+H6))))
M6:M21	M6	=+L6+M5
N6:N21	N6	=+N5-I6
L7:L21	L7	=IF(F7=0,O7,IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))-SUM($L$5:L6)+J7+K7,-SUM($L$5:L6),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+J7+K7,MIN(-SUM($L$5:L6),F7-H7-J7-K7),IF(F7>=((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6)),-((N6*$A$1)/365)*IF(F6=0,DAYS(A7,A5),DAYS(A7,A6))+H7))))

 

[spacecaptainsuperguy]

You cannot change the payments that are already made.
I have not reviewed your formulas.
I changed the dates.
I made the payments fixed per E1.
I ran Goal Seek to Change N21 to 0 by revising E1.
This shows the payments that would be required.

Hopefully someone else can visualize your question.

Fascinating. My formula also linked to the payment amount in E1 however I set up this amortization schedule to mimic our mortgage software so I could play around with payments/scenarios/etc, all of which has been working perfectly. The formula is not as simple as just =E1, since I was using it to check that the payment wasn't larger than the principal balance and to factor in any accrued interest as of the payment date.

But what this tells me is that that formula, in some way, is messing with Goal Seek's (or even Solver for that matter) ability to correctly solve the problem. We didn't get it "fixed" but perhaps it will put me on the path to get a solution I can work with.

Thanks so much for your time & your help!