Debt Free Date Formula

[OrangeJuice]

Hi there,

I am building an avalanche method debt worksheet and want to create a formula that autopopulates the debt free date at the top of the sheet.

I am thinking it has to be something where "when column x (total debt owing) = 0, spit out the value in column a (the month column) - but I am not sure how to write that in excel terms!

please let me know if you need a better example or explanation!

thanks in advance

 

[FormR]

when column x (total debt owing) = 0, spit out the value in column a (the month column)

Hi, something like this maybe..

Book2
	A																							X	Y	Z	AA
1	month																							total debt owing			Formula
2	January																							8			May
3	February																							4
4	March																							6
5	April																							4
6	May																							0
7	June																							0
8	July																							0
9	August																							0
10	September																							0
11	October																							0
12	November																							0
13	December																							0
Sheet1

Cell Formulas
Range	Formula
AA2	AA2	=INDEX($A$2:$A$13,MATCH(0,$X$2:$X$13,0))

 

[OrangeJuice]

Yes I think this is the right idea! However, when I use that, it is giving me an N/A error.

Some other details:
- Column A is in Date format
- Column A uses merged cells
- I am using Microsoft Office 2019 (wondering if that might limit what I can do?)

Column A			Column X
Month	Description	Credit Card	Total
Starting Balance	$ 1,234	$ 14,484
December-24	Minimum Payment	$ 25.00	$ 125.00
Extra Payment	$ 270.00	$ 270.00
Interest	$ 23.65	$ 257.40
Running Balance	$ 962.65	$ 14,346.40
January-25	Minimum Payment	$ 25.00	$ 125.00
Extra Payment	$ 270.00	$ 270.00
Interest	$ 18.45	$ 254.54
Running Balance	$ 686.10	$ 14,205.95
February-25	Minimum Payment	$ 25.00	$ 125.00
Extra Payment	$ 270.00	$ 270.00
Interest	$ 13.15	$ 251.63
Running Balance	$ 404.25	$ 14,062.57

 

[OrangeJuice]

sorry, it didnt copy and past well above.

Below is a screen grab that I think reflects more what I am trying to do

 

[FormR]

Column A uses merged cells

Hi, you should avoid merged cells at all cost!

Below is a screen grab that I think reflects more what I am trying to do

We can't tell from the screen shot which rows or columns that data is in. If you've shown "column X" in that screen shot then none of the rows are zero. How about you post a new sample data set where we can see what the rows and columns are, and for that sample data, let us know what result you expect and importantly the logic that should be applied to reach that expected result.

See if you can use the XL2BB add-in to post your sample data.

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[OrangeJuice]

For some reason I can't seem to get the XL2BB to work, whenever I open it I get a "Cant Open this in Protected View" message and when I search for it in the add ins store, it doesnt show up

I have re copied and pasted a clearer (I hope) example below

Column A					Column X
Month	Description	Credit Card 1	Credit Card 2	Credit Card 3	Total
Interest Rate	23%	23%	22%
Minimum Payment	$ 25	$ 25	$ 25
Starting Balance	$ 1,000	$ 1,000	$ 1,000	$ 3,000
December-24	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
Extra Payment	$ 270.00	$ -	$ -	$ 270.00
Interest	$ 19.17	$ 19.17	$ 18.33	$ 56.67
Running Balance	$ 724.17	$ 994.17	$ 993.33	$ 2,711.67
January-25	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
Extra Payment	$ 270.00	$ -	$ -	$ 270.00
Interest	$ 13.88	$ 19.05	$ 18.21	$ 51.15
Running Balance	$ 443.05	$ 988.22	$ 986.54	$ 2,417.81
February-25	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
Extra Payment	$ 270.00	$ -	$ -	$ 270.00
Interest	$ 8.49	$ 18.94	$ 18.09	$ 45.52
Running Balance	$ 156.54	$ 982.16	$ 979.63	$ 2,118.33
March-25	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
Extra Payment	$ 134.54	$ 135.46	$ -	$ 270.00
Interest	$ 3.00	$ 18.82	$ 17.96	$ 39.78
Running Balance	$ 0.00	$ 840.53	$ 972.59	$ 1,813.12
April-25	Minimum Payment	$ -	$ 25.00	$ 25.00	$ 50.00
Extra Payment	$ 0.00	$ 270.00	$ -	$ 270.00
Interest	$ 0.00	$ 16.11	$ 17.83	$ 33.94
Running Balance	$ (0.00)	$ 561.64	$ 965.42	$ 1,527.06
May-25	Minimum Payment	$ -	$ 25.00	$ 25.00	$ 50.00
Extra Payment	$ (0.00)	$ 270.00	$ -	$ 270.00
Interest	$ (0.00)	$ 10.76	$ 17.70	$ 28.46
Running Balance	$ (0.00)	$ 277.40	$ 958.12	$ 1,235.52
June-25	Minimum Payment	$ -	$ 25.00	$ 25.00	$ 50.00
Extra Payment	$ (0.00)	$ 270.00	$ -	$ 270.00
Interest	$ (0.00)	$ 5.32	$ 17.57	$ 22.88
Running Balance	$ 0.00	$ (12.28)	$ 950.69	$ 938.40
July-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
Extra Payment	$ 0.00	$ (12.52)	$ 270.00	$ 257.48
Interest	$ 0.00	$ (0.24)	$ 17.43	$ 17.19
Running Balance	$ (0.00)	$ (0.00)	$ 673.12	$ 673.12
August-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
Extra Payment	$ (0.00)	$ (0.00)	$ 270.00	$ 270.00
Interest	$ (0.00)	$ (0.00)	$ 12.34	$ 12.34
Running Balance	$ 0.00	$ -	$ 390.46	$ 390.46
September-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
Extra Payment	$ 0.00	$ -	$ 270.00	$ 270.00
Interest	$ 0.00	$ -	$ 7.16	$ 7.16
Running Balance	$ (0.00)	$ -	$ 102.61	$ 102.61
October-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
Extra Payment	$ (0.00)	$ -	$ 79.50	$ 79.50
Interest	$ (0.00)	$ -	$ 1.88	$ 1.88
Running Balance	$ 0.00	$ -	$ (0.00)	$ (0.00)
November-25	Minimum Payment	$ -	$ -	$ -	$ -
Extra Payment	$ 0.00	$ -	$ (0.00)	$ (0.00)
Interest	$ 0.00	$ -	$ (0.00)	$ (0.00)
Running Balance	$ (0.00)	$ -	$ (0.00)	$ (0.00)

So ideally this formula would see that in October 2025 (Column A), Running Balance of all Debt is 0

 

[OrangeJuice]

I think I figured out how to use XL2BB

Debt Free Date Example.xlsx
	C	D	E	F	G	H
1
2				Extra Payment	$ 270.00
3	Debt Free Date	#N/A		Minimum Payment	$ 75.00
4
5	Column A					Column X
6	Month	Description	Credit Card 1	Credit Card 2	Credit Card 3	Total
7		Interest Rate	23%	23%	22%
8		Minimum Payment	$ 25	$ 25	$ 25
9		Starting Balance	$ 1,000	$ 1,000	$ 1,000	$ 3,000
10	December-24	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
11		Extra Payment	$ 270.00	$ -	$ -	$ 270.00
12		Interest	$ 19.17	$ 19.17	$ 18.33	$ 56.67
13		Running Balance	$ 724.17	$ 994.17	$ 993.33	$ 2,711.67
14	January-25	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
15		Extra Payment	$ 270.00	$ -	$ -	$ 270.00
16		Interest	$ 13.88	$ 19.05	$ 18.21	$ 51.15
17		Running Balance	$ 443.05	$ 988.22	$ 986.54	$ 2,417.81
18	February-25	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
19		Extra Payment	$ 270.00	$ -	$ -	$ 270.00
20		Interest	$ 8.49	$ 18.94	$ 18.09	$ 45.52
21		Running Balance	$ 156.54	$ 982.16	$ 979.63	$ 2,118.33
22	March-25	Minimum Payment	$ 25.00	$ 25.00	$ 25.00	$ 75.00
23		Extra Payment	$ 134.54	$ 135.46	$ -	$ 270.00
24		Interest	$ 3.00	$ 18.82	$ 17.96	$ 39.78
25		Running Balance	$ 0.00	$ 840.53	$ 972.59	$ 1,813.12
26	April-25	Minimum Payment	$ -	$ 25.00	$ 25.00	$ 50.00
27		Extra Payment	$ 0.00	$ 270.00	$ -	$ 270.00
28		Interest	$ 0.00	$ 16.11	$ 17.83	$ 33.94
29		Running Balance	$ (0.00)	$ 561.64	$ 965.42	$ 1,527.06
30	May-25	Minimum Payment	$ -	$ 25.00	$ 25.00	$ 50.00
31		Extra Payment	$ (0.00)	$ 270.00	$ -	$ 270.00
32		Interest	$ (0.00)	$ 10.76	$ 17.70	$ 28.46
33		Running Balance	$ (0.00)	$ 277.40	$ 958.12	$ 1,235.52
34	June-25	Minimum Payment	$ -	$ 25.00	$ 25.00	$ 50.00
35		Extra Payment	$ (0.00)	$ 270.00	$ -	$ 270.00
36		Interest	$ (0.00)	$ 5.32	$ 17.57	$ 22.88
37		Running Balance	$ 0.00	$ (12.28)	$ 950.69	$ 938.40
38	July-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
39		Extra Payment	$ 0.00	$ (12.52)	$ 270.00	$ 257.48
40		Interest	$ 0.00	$ (0.24)	$ 17.43	$ 17.19
41		Running Balance	$ (0.00)	$ (0.00)	$ 673.12	$ 673.12
42	August-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
43		Extra Payment	$ (0.00)	$ (0.00)	$ 270.00	$ 270.00
44		Interest	$ (0.00)	$ (0.00)	$ 12.34	$ 12.34
45		Running Balance	$ 0.00	$ -	$ 390.46	$ 390.46
46	September-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
47		Extra Payment	$ 0.00	$ -	$ 270.00	$ 270.00
48		Interest	$ 0.00	$ -	$ 7.16	$ 7.16
49		Running Balance	$ (0.00)	$ -	$ 102.61	$ 102.61
50	October-25	Minimum Payment	$ -	$ -	$ 25.00	$ 25.00
51		Extra Payment	$ (0.00)	$ -	$ 79.50	$ 79.50
52		Interest	$ (0.00)	$ -	$ 1.88	$ 1.88
53		Running Balance	$ 0.00	$ -	$ (0.00)	$ (0.00)
54	November-25	Minimum Payment	$ -	$ -	$ -	$ -
55		Extra Payment	$ 0.00	$ -	$ (0.00)	$ (0.00)
56		Interest	$ 0.00	$ -	$ (0.00)	$ (0.00)
57		Running Balance	$ (0.00)	$ -	$ (0.00)	$ (0.00)
58	December-25	Minimum Payment	$ -	$ -	$ -	$ -
59		Extra Payment	$ (0.00)	$ -	$ (0.00)	$ (0.00)
60		Interest	$ (0.00)	$ -	$ (0.00)	$ (0.00)
61		Running Balance	$ 0.00	$ -	$ 0.00	$ 0.00
Debt Schedule

Cell Formulas
Range	Formula
D3	D3	=INDEX($H$9:$H$1665,MATCH(0,$C$10:$C$1665),0)
G3	G3	=SUM(E8:G8)
E10:G10,E14:G14,E18:G18,E22:G22,E26:G26,E30:G30,E34:G34,E38:G38,E42:G42,F46:G46,F50:G50,F54:G54,F58:G58	E10	=IF(E9>0.01,E$8,0)
E11,E15,E19,E23,E27,E31,E35,E39	E11	=IF(E9>$G$2,$G$2,(E9-E10+E12))
F60:G60,E12:G12,E16:G16,E20:G20,E24:G24,E28:G28,E32:G32,E36:G36,E40:G40,F44:G44,F48:G48,F52:G52,F56:G56	E12	=IFERROR(E9*(E$7/12),0)
E61:G61,E13:G13,E17:G17,E21:G21,E25:G25,E29:G29,E33:G33,E37:G37,E41:G41,E57:G57,E53:G53,E49:G49,E45:G45	E13	=E9-E10-E11+E12
E44,E60,E56,E52,E48	E44	=E41*($E$7/12)
E46,E58,E54,E50	E46	=IF(E45>0.01,$E$8,0)
C14,C18,C22,C26,C30,C34,C38,C42,C46,C50,C54,C58	C14	=EDATE(C10,1)
G11,G15,G19,G23,G27,G31,G35,G39,G43,G47,G51,G55	G11	=IF(OR(E11>0.01,F11>0.01),$G$2-SUM(E11:F11),IF(G9>$G$2,$G$2,G9-G10+G12))
F11,F15,F19,F23,F27,F31,F35,F39,F43,F47,F51,F55,F59	F11	=IF(F9>0,$G$2-E11,IF(F9>$G$2,$G$2,F9-F10+F12))
H9:H61	H9	=SUM(E9:G9)

 

[FormR]

Hi, you have the ranges in the INDEX and MATCH functions the wrong way around, you are not using the same size range in both, have a misplaced parenthesis and we also need to jump through some hoops to adjust for the merged cells. Using the layout you presented in post#7 try:

=INDEX($C$10:$C$1665,(INT((MATCH(0,$H$10:$H$1665,0)-1)/4)*4)+1)

Book1
	C	D	E	F	G	H
1
2				Extra Payment	270
3	Debt Free Date	Oct-25		Minimum Payment	75
4
5	Column A					Column X
6	Month	Description	Credit Card 1	Credit Card 2	Credit Card 3	Total
7		Interest Rate	23%	23%	22%
8		Minimum Payment	25	25	25
9		Starting Balance	1,000	1,000	1,000
10	Dec-24	Minimum Payment	25	25	25	75
11		Extra Payment	270	-	-	270
12		Interest	19.17	19.17	18.33	56.67
13		Running Balance	724.17	994.17	993.33	2,711.67
14	Jan-25	Minimum Payment	25	25	25	75
15		Extra Payment	270	-	-	270
16		Interest	13.88	19.05	18.21	51.15
17		Running Balance	443.05	988.22	986.54	2,417.81
18	Feb-25	Minimum Payment	25	25	25	75
19		Extra Payment	270	-	-	270
20		Interest	8.49	18.94	18.09	45.52
21		Running Balance	156.54	982.16	979.63	2,118.33
22	Mar-25	Minimum Payment	25	25	25	75
23		Extra Payment	134.54	135.46	-	270
24		Interest	3	18.82	17.96	39.78
25		Running Balance	0	840.53	972.59	1,813.12
26	Apr-25	Minimum Payment	-	25	25	50
27		Extra Payment	0	270	-	270
28		Interest	0	16.11	17.83	33.94
29		Running Balance	0	561.64	965.42	1,527.06
30	May-25	Minimum Payment	-	25	25	50
31		Extra Payment	0	270	-	270
32		Interest	0	10.76	17.7	28.46
33		Running Balance	0	277.4	958.12	1,235.52
34	Jun-25	Minimum Payment	-	25	25	50
35		Extra Payment	0	270	-	270
36		Interest	0	5.32	17.57	22.88
37		Running Balance	0	-12.28	950.69	938.4
38	Jul-25	Minimum Payment	-	-	25	25
39		Extra Payment	0	-12.52	270	257.48
40		Interest	0	-0.24	17.43	17.19
41		Running Balance	0	0	673.12	673.12
42	Aug-25	Minimum Payment	-	-	25	25
43		Extra Payment	0	0	270	270
44		Interest	0	0	12.34	12.34
45		Running Balance	0	-	390.46	390.46
46	Sep-25	Minimum Payment	-	-	25	25
47		Extra Payment	0	-	270	270
48		Interest	0	-	7.16	7.16
49		Running Balance	0	-	102.61	102.61
50	Oct-25	Minimum Payment	-	-	25	25
51		Extra Payment	0	-	79.5	79.5
52		Interest	0	-	1.88	1.88
53		Running Balance	0	-	0	0.0
54	Nov-25	Minimum Payment	-	-	-	-
55		Extra Payment	0	-	0	0
56		Interest	0	-	0	0
57		Running Balance	0	-	0	0
58	Dec-25	Minimum Payment	-	-	-	-
59		Extra Payment	0	-	0	0
60		Interest	0	-	0	0
61		Running Balance	0	-	0	0
Sheet1

Cell Formulas
Range	Formula
D3	D3	=INDEX($C$10:$C$1665,(INT((MATCH(0,$H$10:$H$1665,0)-1)/4)*4)+1)

 

[OrangeJuice]

This worked great!! Thank you so much!!!