Remaining balance on Amortization

jeff2813

New Member
Joined
Jul 17, 2019
Messages
30
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
59No.Due DatePayment DueAdditional PaymentInterestPrincipalBalance
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

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,144,246
Messages
5,723,215
Members
422,485
Latest member
Tameima

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top