Pro workbook: Loan with extra payments, Compare, Formulas. Sample data, real formulas.
| Loan amortization | ||||||||||
| Type the loan in the yellow cells. The payment and the whole schedule follow. Extra payments shorten it. | ||||||||||
| Amount borrowed | 250,000.00 | Monthly payment | 1,595.51 | With extra payments | Interest each month = previous balance × rate ÷ 12, rounded to cents — the way a bank does it, | |||||
| Annual interest rate | 5.90% | Payments (scheduled) | 300 | Payments made | 235 | so the last payment absorbs the rounding instead of leaving 3 cents owing. | ||||
| Term (years) | 25 | Total interest (scheduled) | 228,651.85 | Total interest | 167,796.40 | Year-end rows are shaded. The schedule stops on its own when the balance reaches zero. | ||||
| First payment | 2026-10-01 | Total paid (scheduled) | 478,651.85 | Interest saved | 60,855.45 | Up to 480 payments (40 years). | ||||
| Extra every month | 150.00 | Months saved | 65 | |||||||
| Paid off | Apr 2046 | |||||||||
| # | Date | Payment | Extra (one-off) | Principal | Interest | Total paid | Interest to date | Balance | ||
| 1 | Oct 2026 | 1,745.51 | 516.34 | 1,229.17 | 1,745.51 | 1,229.17 | 249,483.66 | |||
| 2 | Nov 2026 | 1,745.51 | 518.88 | 1,226.63 | 3,491.01 | 2,455.80 | 248,964.78 | |||
| 3 | Dec 2026 | 1,745.51 | 521.43 | 1,224.08 | 5,236.52 | 3,679.88 | 248,443.35 | |||
| 4 | Jan 2027 | 1,745.51 | 524.00 | 1,221.51 | 6,982.02 | 4,901.39 | 247,919.35 | |||
| 5 | Feb 2027 | 1,745.51 | 526.57 | 1,218.94 | 8,727.53 | 6,120.33 | 247,392.78 | |||
| 6 | Mar 2027 | 1,745.51 | 529.16 | 1,216.35 | 10,473.04 | 7,336.68 | 246,863.62 | |||
| 7 | Apr 2027 | 1,745.51 | 531.76 | 1,213.75 | 12,218.54 | 8,550.43 | 246,331.86 | |||
| 8 | May 2027 | 1,745.51 | 534.38 | 1,211.13 | 13,964.05 | 9,761.56 | 245,797.48 | |||
| 9 | Jun 2027 | 1,745.51 | 537.01 | 1,208.50 | 15,709.56 | 10,970.06 | 245,260.47 | |||
| 10 | Jul 2027 | 1,745.51 | 539.65 | 1,205.86 | 17,455.06 | 12,175.92 | 244,720.82 | |||
| 11 | Aug 2027 | 1,745.51 | 542.30 | 1,203.21 | 19,200.57 | 13,379.13 | 244,178.52 | |||
| 12 | Sep 2027 | 6,745.51 | 5,000.00 | 5,544.97 | 1,200.54 | 25,946.07 | 14,579.67 | 238,633.55 | ||
| 13 | Oct 2027 | 1,745.51 | 572.23 | 1,173.28 | 27,691.58 | 15,752.95 | 238,061.32 | |||
| 14 | Nov 2027 | 1,745.51 | 575.04 | 1,170.47 | 29,437.09 | 16,923.42 | 237,486.28 | |||
| 15 | Dec 2027 | 1,745.51 | 577.87 | 1,167.64 | 31,182.59 | 18,091.06 | 236,908.41 | |||
| 16 | Jan 2028 | 1,745.51 | 580.71 | 1,164.80 | 32,928.10 | 19,255.86 | 236,327.70 | |||
| 17 | Feb 2028 | 1,745.51 | 583.57 | 1,161.94 | 34,673.60 | 20,417.80 | 235,744.13 | |||
| 18 | Mar 2028 | 1,745.51 | 586.43 | 1,159.08 | 36,419.11 | 21,576.88 | 235,157.70 | |||
| 19 | Apr 2028 | 1,745.51 | 589.32 | 1,156.19 | 38,164.62 | 22,733.07 | 234,568.38 | |||
| 20 | May 2028 | 1,745.51 | 592.22 | 1,153.29 | 39,910.12 | 23,886.36 | 233,976.16 | |||
| 21 | Jun 2028 | 1,745.51 | 595.13 | 1,150.38 | 41,655.63 | 25,036.74 | 233,381.03 | |||
| 22 | Jul 2028 | 1,745.51 | 598.05 | 1,147.46 | 43,401.14 | 26,184.20 | 232,782.98 | |||
| 23 | Aug 2028 | 1,745.51 | 600.99 | 1,144.52 | 45,146.64 | 27,328.72 | 232,181.99 | |||
| 24 | Sep 2028 | 1,745.51 | 603.95 | 1,141.56 | 46,892.15 | 28,470.28 | 231,578.04 | |||
| 25 | Oct 2028 | 1,745.51 | 606.92 | 1,138.59 | 48,637.65 | 29,608.87 | 230,971.12 | |||
| 26 | Nov 2028 | 1,745.51 | 609.90 | 1,135.61 | 50,383.16 | 30,744.48 | 230,361.22 | |||
| 27 | Dec 2028 | 1,745.51 | 612.90 | 1,132.61 | 52,128.67 | 31,877.09 | 229,748.32 | |||
| 28 | Jan 2029 | 1,745.51 | 615.91 | 1,129.60 | 53,874.17 | 33,006.69 | 229,132.41 | |||
| 29 | Feb 2029 | 1,745.51 | 618.94 | 1,126.57 | 55,619.68 | 34,133.26 | 228,513.47 | |||
| 30 | Mar 2029 | 1,745.51 | 621.99 | 1,123.52 | 57,365.19 | 35,256.78 | 227,891.48 | |||
| 31 | Apr 2029 | 1,745.51 | 625.04 | 1,120.47 | 59,110.69 | 36,377.25 | 227,266.44 | |||
| 32 | May 2029 | 1,745.51 | 628.12 | 1,117.39 | 60,856.20 | 37,494.64 | 226,638.32 | |||
| 33 | Jun 2029 | 1,745.51 | 631.20 | 1,114.31 | 62,601.70 | 38,608.95 | 226,007.12 | |||
| 34 | Jul 2029 | 1,745.51 | 634.31 | 1,111.20 | 64,347.21 | 39,720.15 | 225,372.81 |
| Compare rates and terms | |||||
| Monthly payment and total interest for the loan amount on the Loan sheet, across rates and terms. No extra payments here. | |||||
| Monthly payment | 10 yrs | 15 yrs | 20 yrs | 25 yrs | 30 yrs |
| 3.90% | 2,519.26 | 1,836.72 | 1,501.81 | 1,305.83 | 1,179.17 |
| 4.90% | 2,639.43 | 1,963.99 | 1,636.11 | 1,446.95 | 1,326.82 |
| 5.90% | 2,762.97 | 2,096.16 | 1,776.68 | 1,595.51 | 1,482.84 |
| 6.90% | 2,889.84 | 2,233.12 | 1,923.27 | 1,751.03 | 1,646.50 |
| 7.90% | 3,020.00 | 2,374.72 | 2,075.57 | 1,913.01 | 1,817.01 |
| Total interest | 10 | 15 | 20 | 25 | 30 |
| 3.90% | 52,311.68 | 80,609.01 | 110,434.40 | 141,748.23 | 174,501.38 |
| 4.90% | 66,732.19 | 103,517.40 | 142,666.43 | 184,083.96 | 227,654.05 |
| 5.90% | 81,556.97 | 127,308.70 | 176,404.39 | 228,651.85 | 283,822.86 |
| 6.90% | 96,781.24 | 151,961.06 | 211,584.68 | 275,309.56 | 342,740.12 |
| 7.90% | 112,399.52 | 177,449.63 | 248,136.36 | 323,902.60 | 404,124.86 |
| Yellow rates in the first column are editable; the second table reads them. The row matching your rate is highlighted. | |||||
| Term headers in row 11 are numbers (years) so the formulas can read them. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Interest is computed month by month, not with IPMT, so you can see it. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Loan · Monthly payment | -PMT(B4/12, B5*12, B3) | Excel's PMT with the rate per month and the number of months. PMT returns a negative (money going out); the minus sign flips it. |
| Loan · Interest (row) | ROUND(I11*$B$4/12, 2) | Last month's balance × annual rate ÷ 12, rounded to cents like a lender does. Rounding here means the final payment absorbs the odd cents. |
| Loan · Payment (row) | MIN($E$3+$B$7+N(D12), I11+F12) | Scheduled payment plus the monthly extra plus any one-off in column D — but never more than what's owed (balance + this month's interest), so the last payment is exact. N() turns an empty extra into 0. |
| Loan · Principal (row) | C12-F12 | Whatever isn't interest reduces the balance. |
| Loan · Balance (row) | ROUND(I11-E12, 2) | Previous balance minus principal paid. Hits exactly 0.00 on the last row. |
| Loan · # (row) | IF(I11<=0, "", 12) | The row number appears only while there's a balance left, which is how the schedule stops itself. The money columns return 0 on those rows, hidden by the number format, so SUMs never meet a blank text. |
| Loan · Date (row) | IF(A12="", "", EDATE($B$6, 11)) | First payment date plus n months. |
| Loan · Payments made | COUNTIF(I12:I491, ">0") + IF(COUNTIF(I12:I491, 0)>0, 1, 0) | Rows with a balance still owing, plus the one row where it reaches zero. |
| Loan · Interest saved | E5-H5 | Scheduled total interest minus the interest actually paid with extras. |
| Loan · Paid off | INDEX(B12:B491, H4) | The date on the last payment row. |
| Compare · payment grid | -PMT($A5/12, B$11*12, Loan!$B$3) | Same PMT with the rate from the row and the term (years, in row 11) from the column. |
| Compare · interest grid | B5*B$11*12 - Loan!$B$3 | Payment × number of payments minus the amount borrowed. |