# Remaining balance on Amortization

#### jeff2813

##### New Member
Hi there, I have an amortization schedule that has a formula in cell H13. This formula was given to me by someone on this forum for which I'm very thankful. It gives me the remaining months left on the loan. In general it searches for today and finds the remaining months. If i make additional payments it will also reduce the number of payments if entered up to todays date. As an ex. if i put \$1000 additional in D61 the payments will drop to 19, and if another \$1000 in D62 the payments to 17. If i put in another \$1000 in D63 the payments remain the same because the date hasn't occurred. As i use this amortization table for "what if scenario's", i would like it to reflect todays date but the table should reduce payments beyond that date as the table shrinks when a payment is made. I hope this makes sense. Thank you.

Book1
ABCDEFGH
1Loan Amortization Schedule
2
3
4
5Loan
6Loan Amount 10,000.00 Rate (per period)0.417%
7Annual Interest Rate5.00%Total Payments 10,529.14
8Term of Loan in Years2Total Interest 529.14
9First Payment Date12/20/2019Est. Interest Savings -
10Payment FrequencyMonthly.
11Compound PeriodMonthly.
12Payment TypeEnd of PeriodBalance Today\$9,204.26
13RoundingOn22
20[42]
21Monthly Payment 438.71
22
57
58Amortization Schedule
6012/20/201910,000.00
61112/20/2019438.7141.67397.049,602.96
6221/20/2020438.7140.01398.709,204.26
6332/20/2020438.7138.35400.368,803.90
6443/20/2020438.7136.68402.038,401.87
6554/20/2020438.7135.01403.707,998.17
6665/20/2020438.7133.33405.387,592.79
6776/20/2020438.7131.64407.077,185.72
6887/20/2020438.7129.94408.776,776.95
6998/20/2020438.7128.24410.476,366.48
70109/20/2020438.7126.53412.185,954.30
711110/20/2020438.7124.81413.905,540.40
721211/20/2020438.7123.08415.635,124.77
731312/20/2020438.7121.35417.364,707.41
74141/20/2021438.7119.61419.104,288.31
75152/20/2021438.7117.87420.843,867.47
76163/20/2021438.7116.11422.603,444.87
77174/20/2021438.7114.35424.363,020.51
78185/20/2021438.7112.59426.122,594.39
79196/20/2021438.7110.81427.902,166.49
80207/20/2021438.719.03429.681,736.81
81218/20/2021438.717.24431.471,305.34
82229/20/2021438.715.44433.27872.07
832310/20/2021438.713.63435.08436.99
842411/20/2021438.811.82436.990.00
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
Loan
Cell Formulas
RangeFormula
D11D11=D10
H6H6=((1+D7/D15)^(D15/D14))-1
H7H7=SUM(F61:F840)+SUM(G61:G840)
H8H8=SUM(F60:F840)
H9H9=IF(AND(SUM(D61:D840)=0,roundOpt)," - ",(nper*(-PMT(rate,nper,loan_amount,,pmtType))-loan_amount)-H8)
H10H10=IF(AND(NOT(H840=""),H840>0.004),"ERROR: Limit is "&OFFSET(A841,-1,0,1,1)&" payments",".")
H11H11=IF(D15>D14,"Warning: negative amortization",".")
H12H12=VLOOKUP(TODAY(),B22:H149,7)
H13H13=NPER(\$D\$7/12,-\$D\$21,INDEX(\$H\$60:\$H\$100,MATCH(TODAY(),\$B\$60:\$B\$102)))
C21C21=D10&" Payment"
D21D21=IF(roundOpt,ROUND(-PMT(rate,nper,\$D\$6,,pmtType),2),-PMT(rate,nper,\$D\$6,,pmtType))
H60H60=\$D\$6
A61:A103A61=IF(H60="","",IF(roundOpt,IF(OR(A60>=nper,ROUND(H60,2)<=0),"",A60+1),IF(OR(A60>=nper,H60<=0),"",A60+1)))
B61:B103B61=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:C103C61=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:F103F61=IF(A61="","",IF(AND(A61=1,pmtType=1),0,IF(roundOpt,ROUND(rate*H60,2),rate*H60)))
G61:G103G61=IF(A61="","",C61-F61+D61)
H61:H103H61=IF(A61="","",H60-G61)
Named Ranges
NameRefers ToCells
Borrowell=Loan!\$B\$25:INDEX(Loan!\$J:\$J,MATCH(99^99,Loan!\$B:\$B,1))H7:H9, H12:H13, A61:C85, F85, F61:H84, H85
compound_period=Loan!\$D\$15H6, H11
fpdate=Loan!\$D\$9B61:B103
loan_amount=Loan!\$D\$6H9, D21, H60
months_per_period=Loan!\$D\$17B61:B103
nper=Loan!\$D\$18H9, D21, A61:A103, C61:C103
payment=Loan!\$D\$21H13, C61:C103
periods_per_year=Loan!\$D\$14H6, H11, B61:B103
pmtType=Loan!\$D\$16H9, D21, F61:F103
Print_Area=OFFSET(Loan!\$A\$1,0,0,ROW(Loan!\$A\$59)+1+Loan!\$D\$18,8)H6:H9, D11, H11:H13, C21:D21, A61:C103, H60:H85, F61:G84, F85:F103
Print_Titles=Loan!\$59:\$59H12
rate=Loan!\$H\$6H9, D21, C61:C103, F61:F103
roundOpt=Loan!\$D\$19H9, D21, A61:A103, C61:C103, F61:F103
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A61:H840Expression=MOD(\$A61,periods_per_year)=0textYES
D11Expression=compound_period>periods_per_yeartextYES
Cells with Data Validation
CellAllowCriteria
D10:D11List=\$L\$6:\$L\$13
D12ListEnd of Period, Beginning of Period
D13ListOn,Off

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Replies
1
Views
1K
Replies
4
Views
749
Replies
0
Views
756
Replies
7
Views
304
Replies
4
Views
485

1,191,627
Messages
5,987,762
Members
440,109
Latest member
mitra2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back