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:
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.
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 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Loan data | Months | ||||||||||||||
| 2 | Payoff Term | 12 | Monthly | 12 | ||||||||||||
| 3 | Date that 1st payment is due | 1/1/2015 | <--- Edit This | Semi-Monthly | 24 | |||||||||||
| 4 | Loan Term (Years) | 1.00 | <--- Edit This | Regular Principal | $29,949.54 | Bi-Weekly | 26 | |||||||||
| 5 | Annual Interest Rate | 3.625% | <--- Edit This | Early Principal | $0.00 | Weekly | 52 | |||||||||
| 6 | How Will Payments Be Made | Monthly | <--- Edit This | Total Principal | $29,949.54 | Aggressive Weekly | 52 | |||||||||
| 7 | Aggressive Bi-Weekly | 26 | ||||||||||||||
| 8 | Monthly Payment | $4,698.56 | Total Accrued Interest | $1,091.67 | Aggressive Semi-Monthly | 24 | ||||||||||
| 9 | Aggressive Monthly | 12 | ||||||||||||||
| 10 | Original Loan Amount | $55,291.00 | <--- Edit This | Payments Shaved | Chosen: | 12 | ||||||||||
| 11 | Interest that will accrue with | + | Total Interest Saved | $0.00 | 0.00 | |||||||||||
| 12 | minimum payments | $1,091.67 | ||||||||||||||
| 13 | Total $ paid with minimum payments | $56,382.67 | !!! Suggested amount to pay Extra each | |||||||||||||
| 14 | payment towards principal to pay off | |||||||||||||||
| 15 | Additional Principal | standard loan in roughly 1/2 the time !!! | ||||||||||||||
| 16 | you will pay every payment | $0.00 | <--- Edit This | <--- | $94.00 | |||||||||||
| 17 | Total | |||||||||||||||
| 18 | Unscheduled | Monthly | ||||||||||||||
| 19 | Payment # | Date | Scheduled Monthly Pmnt | Int. Accrued | Principal | Sched. Xtra Principal | Extra Princ. | Tot. Princ. Pd | Payment | Balance | ||||||
| 20 | $55,291.00 | |||||||||||||||
| 21 | 1 | 1/1/2015 | $4,698.56 | $167.02 | $4,531.53 | $4,531.53 | $4,698.56 | $50,759.47 | ||||||||
| 22 | 2 | 2/1/2015 | $4,698.56 | $153.34 | $4,172.70 | $4,172.70 | $4,698.56 | $46,214.25 | ||||||||
| 23 | 3 | 3/1/2015 | $4,698.56 | $139.61 | $3,810.54 | $3,810.54 | $4,698.56 | $41,655.30 | ||||||||
| 24 | 4 | 4/1/2015 | $4,698.56 | $125.83 | $3,445.01 | $3,445.01 | $4,698.56 | $37,082.58 | ||||||||
| 25 | 5 | 5/1/2015 | $4,698.56 | $112.02 | $3,076.10 | $3,076.10 | $4,698.56 | $32,496.04 | ||||||||
| 26 | 6 | 6/1/2015 | $4,698.56 | $98.17 | $2,703.78 | $2,703.78 | $4,698.56 | $27,895.65 | ||||||||
| 27 | 7 | 7/1/2015 | $4,698.56 | $84.27 | $2,328.02 | $2,328.02 | $4,698.56 | $23,281.37 | ||||||||
| 28 | 8 | 8/1/2015 | $4,698.56 | $70.33 | $1,948.81 | $1,948.81 | $4,698.56 | $18,653.14 | ||||||||
| 29 | 9 | 9/1/2015 | $4,698.56 | $56.35 | $1,566.11 | $1,566.11 | $4,698.56 | $14,010.93 | ||||||||
| 30 | 10 | 10/1/2015 | $4,698.56 | $42.32 | $1,179.91 | $1,179.91 | $4,698.56 | $9,354.70 | ||||||||
| 31 | 11 | 11/1/2015 | $4,698.56 | $28.26 | $790.17 | $790.17 | $4,698.56 | $4,684.40 | ||||||||
| 32 | 12 | 1 Year | 12/1/2015 | $4,698.56 | $14.15 | $396.88 | $396.88 | $4,698.56 | ||||||||
Amort Sched With Extra Payments | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2 | H2 | =MATCH(0,K20:K1580,0) -1 |
| E3:E6,E16,E10 | E3 | = "<--- Edit This" |
| H4 | H4 | =SUM(F21:F1580) |
| H5 | H5 | =SUM(G21:H1580) |
| H6 | H6 | = OriginalPrincipal + ExtraPaidPrincipal |
| A8 | A8 | = ScheduledPaymentsPerYear & " Payment" |
| D8 | D8 | =(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)))))) |
| H8 | H8 | =SUM(E21:E1580) |
| M10 | M10 | =INDEX(M2:M9,MATCH($D$6,L2:L9,0)) |
| H11 | H11 | = (OriginalLoanamount + MaximumInterestPossible) - (SUM(J:J)-SUM(J1:J20)) |
| J11 | J11 | = TermOfLoanInYears * SelectedPaymentsPerYear - ( ROUND(MAX(A21:A1580)/SelectedPaymentsPerYear,2) * SelectedPaymentsPerYear) |
| D12 | D12 | =-CUMIPMT(AnnualInterestRate/SelectedPaymentsPerYear,TermOfLoanInYears*SelectedPaymentsPerYear,OriginalLoanamount,1,SelectedPaymentsPerYear*TermOfLoanInYears,0) |
| D13 | D13 | = OriginalLoanamount + MaximumInterestPossible |
| G16 | G16 | = ROUND(E21 * 0.56,0) |
| J18 | J18 | = ScheduledPaymentsPerYear |
| D19 | D19 | = "Scheduled " & D6 & " Pmnt" |
| K20 | K20 | =OriginalLoanamount |
| A21:A32 | A21 | =IF($K20 < 0.01,"", $A20 + 1 ) |
| B21:B32 | B21 | =IF($A21="","",IF(MOD($A21,SelectedPaymentsPerYear)=0,$A21/SelectedPaymentsPerYear & " Year","")) |
| C21 | C21 | = DateOfPayment1 |
| D21:D32 | D21 | =IF(PeriodPayment<=$K20,PeriodPayment,$K20+$K20*AnnualInterestRate/SelectedPaymentsPerYear) |
| E21:E32 | E21 | =IF($A21="","",IPMT(AnnualInterestRate/SelectedPaymentsPerYear,1,TermOfLoanInYears*SelectedPaymentsPerYear,-$K20)) |
| F21:F32 | F21 | =IF($D21 > 0,PPMT(AnnualInterestRate/SelectedPaymentsPerYear,ROW(1:1),TermOfLoanInYears*SelectedPaymentsPerYear,-$K20),0) |
| G21:G32 | G21 | =IFERROR(IF($K20 + $E21 - $D21 = 0,0,IF($K20 + $E21 - $D21-AdditionalMonthlyPrincipal<0,$K20 + $E21 - $D21,AdditionalMonthlyPrincipal)),0) |
| C22:C32 | C22 | =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:I32 | I21 | = $F21 + $G21 + $H21 |
| J21:J32 | J21 | = $D21 + $G21 + $H21 |
| K21:K32 | K21 | =IF($K20 > 0.01, $K20 + $E21 - $D21 - $G21 - $H21, 0) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| AdditionalMonthlyPrincipal | ='Amort Sched With Extra Payments'!$D$16 | G21:G32 |
| AnnualInterestRate | ='Amort Sched With Extra Payments'!$D$5 | D12, D8, D21:F32 |
| ChosenCompounding | ='Amort Sched With Extra Payments'!$O$4 | F24 |
| CompoundPeriod | ='Amort Sched With Extra Payments'!$D$7 | F27 |
| DateOfPayment1 | ='Amort Sched With Extra Payments'!$D$3 | F23, C21 |
| ExtraPaidPrincipal | ='Amort Sched With Extra Payments'!$H$5 | F25, H6 |
| MaximumInterestPossible | ='Amort Sched With Extra Payments'!$D$12 | F32, H11, D13 |
| MinimumMonthlyPayment | ='Amort Sched With Extra Payments'!$D$9 | F29 |
| OriginalLoanamount | ='Amort Sched With Extra Payments'!$D$10 | F30, K20, H11, D8, D12:D13 |
| OriginalPrincipal | ='Amort Sched With Extra Payments'!$H$4 | F24, H6 |
| PeriodPayment | ='Amort Sched With Extra Payments'!$D$8 | F28, D21:D32 |
| ScheduledPaymentsPerYear | ='Amort Sched With Extra Payments'!$D$6 | F26, M10, J18, D19, D8, A8, C22:C32 |
| SelectedPaymentsPerYear | ='Amort Sched With Extra Payments'!$M$10 | J11, D12, D8, D21:F32, B21:B32 |
| TermOfLoanInYears | ='Amort Sched With Extra Payments'!$D$4 | J11, D12, D8, E21:F32 |
| TotalAmountOfPrincipalPaid | ='Amort Sched With Extra Payments'!$H$6 | F26 |
| TotalInterestAccrued | ='Amort Sched With Extra Payments'!$H$8 | F28 |
| TotalOfAllPayments | ='Amort Sched With Extra Payments'!$H$9 | F29 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| L24:M383,A21:K1580 | Expression | =ROUND(#REF!,5)=0 | text | YES |
| A21:K1580 | Expression | =$A21>($D$4*$D$6) | text | YES |
| A21:K1580 | Expression | =ROUND(#REF!,5)=0 | text | YES |
| L23:M382 | Expression | =$A21>($D$4*$D$6) | text | YES |
| L23:M382 | Expression | =ROUND(#REF!,5)=0 | text | YES |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D6 | List | =$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.
