Months remaining on Amortization

[jeff2813]

Hi there, i have an amortization table that shows the # of payments remaining in cell H13. The formula shows the payments remaining until the balance is zero but i would like it to change to the actual months remaining. EX. Today date is Jan 27, 2020. 2 payments have taken place as of today so i would like it say 10 payments remaining. The balance remaining changes to reflect a balance of today i just cant get the payments to change. Not sure if another formula is needed or just a modification. Thank You

Book1
	A	B	C	D	E	F	G	H
1	Loan Amortization Schedule
2
3
4
5	Loan
6			Loan Amount	10,000.00			Rate (per period)	0.417%
7			Annual Interest Rate	5.00%			Total Payments	10,272.89
8			Term of Loan in Years	1			Total Interest	272.89
9			First Payment Date	12/20/2019			Est. Interest Savings	-
10			Payment Frequency	Monthly				.
11			Compound Period	Monthly				.
12			Payment Type	End of Period		Balance Today		$8,367.80
13			Rounding	On		# Of Remaining Months		12
20								[42]
21			Monthly Payment	856.07
22
57
58	Amortization Schedule
59	No.	Due Date	Payment Due	Additional Payment		Interest	Principal	Balance
60		12/6/2019						10,000.00
61	1	12/20/2019	856.07			41.67	814.40	9,185.60
62	2	1/20/2020	856.07			38.27	817.80	8,367.80
63	3	2/20/2020	856.07			34.87	821.20	7,546.60
64	4	3/20/2020	856.07			31.44	824.63	6,721.97
65	5	4/20/2020	856.07			28.01	828.06	5,893.91
66	6	5/20/2020	856.07			24.56	831.51	5,062.40
67	7	6/20/2020	856.07			21.09	834.98	4,227.42
68	8	7/20/2020	856.07			17.61	838.46	3,388.96
69	9	8/20/2020	856.07			14.12	841.95	2,547.01
70	10	9/20/2020	856.07			10.61	845.46	1,701.55
71	11	10/20/2020	856.07			7.09	848.98	852.57
72	12	11/20/2020	856.12			3.55	852.57	0.00
73
74
Borrowell

Cell Formulas
Range	Formula
D11	D11	=D10
H6	H6	=((1+D7/D15)^(D15/D14))-1
H7	H7	=SUM(F61:F840)+SUM(G61:G840)
H8	H8	=SUM(F60:F840)
H9	H9	=IF(AND(SUM(D61:D840)=0,roundOpt)," - ",(nper*(-PMT(rate,nper,loan_amount,,pmtType))-loan_amount)-H8)
H10	H10	=IF(AND(NOT(H840=""),H840>0.004),"ERROR: Limit is "&OFFSET(A841,-1,0,1,1)&" payments",".")
H11	H11	=IF(D15>D14,"Warning: negative amortization",".")
H12	H12	=VLOOKUP(TODAY(),B22:H149,7)
H13	H13	=IF(IF(D6*D7*D8*D9>0,1,0),MATCH(0.01,H61:H1001,-1)+1,"")
C21	C21	=D10&" Payment"
D21	D21	=IF(roundOpt,ROUND(-PMT(rate,nper,$D$6,,pmtType),2),-PMT(rate,nper,$D$6,,pmtType))
H60	H60	=$D$6
A61:A74	A61	=IF(H60="","",IF(roundOpt,IF(OR(A60>=nper,ROUND(H60,2)<=0),"",A60+1),IF(OR(A60>=nper,H60<=0),"",A60+1)))
B61:B74	B61	=IF(A61="","",IF(OR(periods_per_year=26,periods_per_year=52),IF(periods_per_year=26,IF(A61=1,fpdate,B60+14),IF(periods_per_year=52,IF(A61=1,fpdate,B60+7),"n/a")),IF(periods_per_year=24,DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)/2+IF(AND(DAY(fpdate)>=15,MOD(A61,2)=0),1,0),IF(MOD(A61,2)=0,IF(DAY(fpdate)>=15,DAY(fpdate)-14,DAY(fpdate)+14),DAY(fpdate))),IF(DAY(DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)*months_per_period,DAY(fpdate)))<>DAY(fpdate),DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)*months_per_period+1,0),DATE(YEAR(fpdate),MONTH(fpdate)+(A61-1)*months_per_period,DAY(fpdate))))))
C61:C74	C61	=IF(A61="","",IF(roundOpt,IF(OR(A61=nper,payment>ROUND((1+rate)*H60,2)),ROUND((1+rate)*H60,2),payment),IF(OR(A61=nper,payment>(1+rate)*H60),(1+rate)*H60,payment)))
F61:F74	F61	=IF(A61="","",IF(AND(A61=1,pmtType=1),0,IF(roundOpt,ROUND(rate*H60,2),rate*H60)))
G61:G74	G61	=IF(A61="","",C61-F61+D61)
H61:H74	H61	=IF(A61="","",H60-G61)

 

[AhoyNC]

Maybe in H13:

=A72-INDEX(A61:A72,MATCH(TODAY(),B61:B72))

 

[jeff2813]

Hi there, thank you for your answer. The formula works properly but when i put in an extra payment the balance drops and the number of payments should drop as well but instead i receive a #Value! error.
I'm guessing it's because of the first cell A72 doesn't change. Is there a way to get the first cell to change to the new payment number? As example, if i put in an additional $2000 on March 20,2020 the due date and payment number changes to Sept 20 and payment 10. H13 should then read 8 payments but i receive the error instead. Hope this makes sense. Thanks very much for your help.

 

[AhoyNC]

Maybe:

=NPER($D$7/12,-$D$21,INDEX($H$60:$H$72,MATCH(TODAY(),$B$60:$B$74)))

 

[jeff2813]

Hi there, so everything works well. When i put in an extra 1000 additional dollars on or before today's date the number of months changes as well. As i use the amortization schedule for "what if scenarios" let's say that i put in $1000 extra in any of the other months the amortization will change but the number of payments remains the same. Is it possible to have the payments reduced as well? As an ex. if i put 1000 in cell d3. Feb 20, 2020 that hasn't taken place yet the schedule will change but the payments remain the same. It should read 9. Again thanks for your help. It's very appreciated.