How to build an amortization schedule for a promissory note in a spreadsheet

You can build an amortization schedule in any spreadsheet with one payment formula and a short row of column formulas. Interest equals the starting balance times the monthly rate, principal equals the payment minus that interest, and the ending balance equals the starting balance minus principal. Copy that row down once per payment and the schedule fills itself in.

Free download: get the ready-made schedule as an Excel file (.xlsx) or a CSV for Google Sheets. Type in the loan amount, interest rate, number of payments, and first due date, and all of the rows below update, with up to 360 payments. It also has a column for extra principal payments and an optional balloon date, and it uses the same formulas described in this guide.

The schedule is the payment-by-payment record of how each check splits between interest and principal. The Consumer Financial Protection Bureau (CFPB) describes amortization as paying off a loan "with regular payments, so that the amount you owe goes down with each payment." A schedule shows you exactly how fast that happens on your own note. If you are still choosing a structure, our comparison of amortized, interest-only, and balloon repayment explains where a schedule fits.

Put the loan terms in their own cells at the top so every formula can point to them. Use B1 for the principal, B2 for the annual interest rate, and B3 for the number of monthly payments. For the example in this guide, B1 holds 8000, B2 holds 5%, and B3 holds 12.

In B4, enter =ROUND(PMT(B2/12,B3,-B1),2). PMT returns the level payment for a fixed-rate loan. The minus sign in front of B1 keeps the result positive, and ROUND trims it to whole cents, since nobody writes a check for a fraction of a penny. For $8,000 at 5 percent over 12 months, B4 shows $684.86.

The column formulas, row by row

Put headers in row 6 and start the first payment in row 7. These are the formulas for row 7, with the result each one produces for the example loan:

In row 8, change only two cells. The payment number becomes =A7+1, and the starting balance becomes =F7, so each month begins where the last one ended. Then select row 8 and fill down through row 18 for all 12 payments. The dollar signs lock the input cells so they stay put as you copy. If you want due dates, add a column with =EDATE(first due date, A7-1), which moves forward one month per row.

Here are the first four rows of the finished schedule:

The interest shrinks every month while the principal grows. Across all 12 payments, the borrower pays $218.32 in interest, for $8,218.32 in total. If the interest column ever comes out larger than the payment, something is wrong. The CFPB calls that situation negative amortization and explains the result plainly: "The unpaid interest gets added to the amount you borrowed."

Two quick checks catch most mistakes. Below the table, enter =SUM(E7:E18) in the principal column. It should equal your original principal of $8,000.00 exactly. Then enter =SUM(D7:D18) in the interest column, which should show $218.32 for this loan. If the principal total is off by a few cents, the final-payment fix below solves it. If it is off by more, look for a row where the starting balance does not point to the ending balance above it.

This setup charges one-twelfth of the annual rate each month, whatever the number of days in that month. Many private notes work that way, and it keeps the payments level. If your note instead says interest accrues daily, add a column for the days since the last payment and change the interest formula to =ROUND(B7*$B$2*H7/365,2), where H7 holds the day count. Your note and your spreadsheet should use the same method, so read the interest clause before you build anything.

Fixing the final payment and adding extra payments

Because the payment is rounded to the cent, a schedule can end a few cents above or below zero. In the 12-month example the rounding happens to cancel out: the last row starts at $682.02, adds $2.84 of interest, and the regular $684.86 clears it exactly. Spread the same $8,000 over 24 months and it does not. The regular payment is $350.97, but the last one has to be $351.00. Over 36 months, the payment is $239.77 and the last one drops to $239.65.

The fix is one formula. In the payment cell of the final row only, replace =$B$4 with the starting balance plus that row's interest, which is =B18+D18 in a 12-payment schedule. The ending balance then lands on exactly zero, and your note can state the final payment as its own figure.

For extra payments, add column G for extra principal and change the ending balance formula to =B7-E7-G7. Type any extra amount in the row where it happens. If the borrower in the example adds $500 with payment 3, total interest falls from $218.32 to $199.25, and payment 12 shrinks to $165.79 with the final-row formula doing the work.

The same sheet handles a balloon note. Set B3 to the longer amortization period, such as 36 months, but stop the table at the due date. For $8,000 at 5 percent amortized over 36 months and due after 12, the regular payment is $239.77. The ending balance after payment 12 is $5,465.19, so the final payment that month is $5,704.96, the starting balance of $5,681.29 plus $23.67 of interest. That is the same final-row formula, applied a little early.

Once the numbers are right, attach the schedule to your installment note as an exhibit. Fill in the actual due dates, have both of you initial each page, and add a sentence to the note such as "Borrower will make payments in the amounts and on the dates shown in Exhibit A, the Amortization Schedule, which is part of this Note. If Exhibit A and this Note conflict, this Note controls." Keep the spreadsheet file as well, so you can rebuild the schedule if a payment arrives late or early.

Sources

• Consumer Financial Protection Bureau, What is negative amortization?