Best Excel Loan spreadsheet ever?

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
4,546
Office Version
  1. 2007
Platform
  1. Windows
I have recently seen a few threads asking about calculating loans so I decided to dust off a program that I started playing with back in about 2015, when I had started learning a few things about excel.

I originally got the program from TvmCalcs. The original script can be downloaded from here if you want to see how it originally looked & worked

After I recently, dusted it off, I decided to correct a few changes I had made way back when, I also decided to 'Supe' it up a bit.

The following is what I have ended up with thus far so you can see a portion of what it does:

Amortization Schedule With Extra Payments V1.0145.xlsx
ABCDEFGHIJKLMN
1Loan dataMonths
2Payoff Term12Monthly12
3Date that 1st payment is due1/1/2015<--- Edit ThisSemi-Monthly24
4Loan Term (Years)1.00<--- Edit ThisRegular Principal$29,949.54Bi-Weekly26
5Annual Interest Rate3.625%<--- Edit ThisEarly Principal$0.00Weekly52
6How Will Payments Be Made Monthly <--- Edit ThisTotal Principal$29,949.54Aggressive Weekly52
7Aggressive Bi-Weekly26
8Monthly Payment$4,698.56 Total Accrued Interest $1,091.67Aggressive Semi-Monthly24
9Aggressive Monthly12
10Original Loan Amount$55,291.00<--- Edit ThisPayments ShavedChosen:12
11Interest that will accrue with+Total Interest Saved$0.000.00
12 minimum payments$1,091.67
13Total $ paid with minimum payments$56,382.67!!! Suggested amount to pay Extra each
14payment towards principal to pay off
15Additional Principalstandard loan in roughly 1/2 the time !!!
16 you will pay every payment$0.00<--- Edit This<---$94.00
17Total
18Unscheduled Monthly
19Payment #Date Scheduled Monthly Pmnt Int. AccruedPrincipalSched. Xtra PrincipalExtra Princ.Tot. Princ. PdPaymentBalance
20$55,291.00
211 1/1/2015$4,698.56$167.02$4,531.53 $4,531.53$4,698.56$50,759.47
222 2/1/2015$4,698.56$153.34$4,172.70 $4,172.70$4,698.56$46,214.25
233 3/1/2015$4,698.56$139.61$3,810.54 $3,810.54$4,698.56$41,655.30
244 4/1/2015$4,698.56$125.83$3,445.01 $3,445.01$4,698.56$37,082.58
255 5/1/2015$4,698.56$112.02$3,076.10 $3,076.10$4,698.56$32,496.04
266 6/1/2015$4,698.56$98.17$2,703.78 $2,703.78$4,698.56$27,895.65
277 7/1/2015$4,698.56$84.27$2,328.02 $2,328.02$4,698.56$23,281.37
288 8/1/2015$4,698.56$70.33$1,948.81 $1,948.81$4,698.56$18,653.14
299 9/1/2015$4,698.56$56.35$1,566.11 $1,566.11$4,698.56$14,010.93
3010 10/1/2015$4,698.56$42.32$1,179.91 $1,179.91$4,698.56$9,354.70
3111 11/1/2015$4,698.56$28.26$790.17 $790.17$4,698.56$4,684.40
32121 Year12/1/2015$4,698.56$14.15$396.88 $396.88$4,698.56 
Amort Sched With Extra Payments
Cell Formulas
RangeFormula
H2H2=MATCH(0,K20:K1580,0) -1
E3:E6,E16,E10E3= "<--- Edit This"
H4H4=SUM(F21:F1580)
H5H5=SUM(G21:H1580)
H6H6= OriginalPrincipal + ExtraPaidPrincipal
A8A8= ScheduledPaymentsPerYear & " Payment"
D8D8=(IF(ScheduledPaymentsPerYear="Aggressive Weekly",ROUND(PMT(AnnualInterestRate/12,TermOfLoanInYears*12,-OriginalLoanamount)/4,2), IF(ScheduledPaymentsPerYear="Aggressive Bi-Weekly",ROUND(PMT(AnnualInterestRate/12,TermOfLoanInYears*12,-OriginalLoanamount)/2,2), IF(ScheduledPaymentsPerYear="Aggressive Semi-Monthly",ROUND(PMT(AnnualInterestRate/12,TermOfLoanInYears*12,-OriginalLoanamount)*26/24/2,2), IF(ScheduledPaymentsPerYear="Aggressive Monthly",ROUND(PMT(AnnualInterestRate/12,TermOfLoanInYears*12,-OriginalLoanamount)*13/12,2), PMT(AnnualInterestRate/SelectedPaymentsPerYear,TermOfLoanInYears*SelectedPaymentsPerYear,-OriginalLoanamount))))))
H8H8=SUM(E21:E1580)
M10M10=INDEX(M2:M9,MATCH($D$6,L2:L9,0))
H11H11= (OriginalLoanamount + MaximumInterestPossible) - (SUM(J:J)-SUM(J1:J20))
J11J11= TermOfLoanInYears * SelectedPaymentsPerYear - ( ROUND(MAX(A21:A1580)/SelectedPaymentsPerYear,2) * SelectedPaymentsPerYear)
D12D12=-CUMIPMT(AnnualInterestRate/SelectedPaymentsPerYear,TermOfLoanInYears*SelectedPaymentsPerYear,OriginalLoanamount,1,SelectedPaymentsPerYear*TermOfLoanInYears,0)
D13D13= OriginalLoanamount + MaximumInterestPossible
G16G16= ROUND(E21 * 0.56,0)
J18J18= ScheduledPaymentsPerYear
D19D19= "Scheduled " & D6 & " Pmnt"
K20K20=OriginalLoanamount
A21:A32A21=IF($K20 < 0.01,"", $A20 + 1 )
B21:B32B21=IF($A21="","",IF(MOD($A21,SelectedPaymentsPerYear)=0,$A21/SelectedPaymentsPerYear & " Year",""))
C21C21= DateOfPayment1
D21:D32D21=IF(PeriodPayment<=$K20,PeriodPayment,$K20+$K20*AnnualInterestRate/SelectedPaymentsPerYear)
E21:E32E21=IF($A21="","",IPMT(AnnualInterestRate/SelectedPaymentsPerYear,1,TermOfLoanInYears*SelectedPaymentsPerYear,-$K20))
F21:F32F21=IF($D21 > 0,PPMT(AnnualInterestRate/SelectedPaymentsPerYear,ROW(1:1),TermOfLoanInYears*SelectedPaymentsPerYear,-$K20),0)
G21:G32G21=IFERROR(IF($K20 + $E21 - $D21 = 0,0,IF($K20 + $E21 - $D21-AdditionalMonthlyPrincipal<0,$K20 + $E21 - $D21,AdditionalMonthlyPrincipal)),0)
C22:C32C22=IF($K21<0.01,"", IF(OR(ScheduledPaymentsPerYear="Weekly",ScheduledPaymentsPerYear="Aggressive Weekly"),$C21+7, IF(OR(ScheduledPaymentsPerYear="Bi-Weekly",ScheduledPaymentsPerYear="Aggressive Bi-Weekly"),$C21+14, IF(ScheduledPaymentsPerYear="Semi-Monthly",IF(DAY($C21)=1,DATE(YEAR($C21),MONTH($C21),15),DATE(YEAR($C21),MONTH($C21)+1,IF(DAY($C21)>=28,15,0))), IF(OR(ScheduledPaymentsPerYear="Monthly",ScheduledPaymentsPerYear="Aggressive Monthly"),DATE(YEAR($C21),MONTH($C21)+1,DAY($C21)),0)))))
I21:I32I21= $F21 + $G21 + $H21
J21:J32J21= $D21 + $G21 + $H21
K21:K32K21=IF($K20 > 0.01, $K20 + $E21 - $D21 - $G21 - $H21, 0)
Named Ranges
NameRefers ToCells
AdditionalMonthlyPrincipal='Amort Sched With Extra Payments'!$D$16G21:G32
AnnualInterestRate='Amort Sched With Extra Payments'!$D$5D12, D8, D21:F32
ChosenCompounding='Amort Sched With Extra Payments'!$O$4F24
CompoundPeriod='Amort Sched With Extra Payments'!$D$7F27
DateOfPayment1='Amort Sched With Extra Payments'!$D$3F23, C21
ExtraPaidPrincipal='Amort Sched With Extra Payments'!$H$5F25, H6
MaximumInterestPossible='Amort Sched With Extra Payments'!$D$12F32, H11, D13
MinimumMonthlyPayment='Amort Sched With Extra Payments'!$D$9F29
OriginalLoanamount='Amort Sched With Extra Payments'!$D$10F30, K20, H11, D8, D12:D13
OriginalPrincipal='Amort Sched With Extra Payments'!$H$4F24, H6
PeriodPayment='Amort Sched With Extra Payments'!$D$8F28, D21:D32
ScheduledPaymentsPerYear='Amort Sched With Extra Payments'!$D$6F26, M10, J18, D19, D8, A8, C22:C32
SelectedPaymentsPerYear='Amort Sched With Extra Payments'!$M$10J11, D12, D8, D21:F32, B21:B32
TermOfLoanInYears='Amort Sched With Extra Payments'!$D$4J11, D12, D8, E21:F32
TotalAmountOfPrincipalPaid='Amort Sched With Extra Payments'!$H$6F26
TotalInterestAccrued='Amort Sched With Extra Payments'!$H$8F28
TotalOfAllPayments='Amort Sched With Extra Payments'!$H$9F29
Cells with Conditional Formatting
CellConditionCell FormatStop If True
L24:M383,A21:K1580Expression=ROUND(#REF!,5)=0textYES
A21:K1580Expression=$A21>($D$4*$D$6)textYES
A21:K1580Expression=ROUND(#REF!,5)=0textYES
L23:M382Expression=$A21>($D$4*$D$6)textYES
L23:M382Expression=ROUND(#REF!,5)=0textYES
Cells with Data Validation
CellAllowCriteria
D6List=$L$2:$L$9


A dropbox link to the file to get the full script is here.

The link has a dropdown box for different types of payment schedules that can be used.

The green boxes show what changes as you make changes to the program.
The 'Agressive' options in the dropdown box show how just adding a few dollars to each payment can affect the term of the loan. It basically equates to averaging one extra monthly payment per yer over each payment made.

You also have an option to set an extra dollar amount added to each scheduled payment to see how that affects the loan.
You also have the option of adding additional random extra payments to see how that would affect the loan.

Please check out the program, & let me know what you liked, didn't like, suggestion for changes ... be it for additions to the program, subtractions from the program, any formula corrections/changes, etc. Whatever.

FYI if the formulas are extended down to Row 1580, that should handle a 30 yr loan with weekly payments.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,223,888
Messages
6,175,205
Members
452,618
Latest member
Tam84

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
Back
Top