What Is An Amortization Schedule?

Taking out large loans, like mortgages for homes, can be a complex process. But at heart, the concept is simple. The buyer borrows money to pay off the purchase price, then repays the lender a portion of the balance every month, plus interest charges, until the loan is paid off.

Paying off large home loans over multiple decades might be a little daunting to think about. But there’s a tool lenders and borrowers use to map out long-term loan repayments to bring clarity to their finances: an amortization schedule.

Negative amortization relates to the monthly interest rate the bank charges for administering a mortgage. If a borrower’s monthly payment is exceedingly low, it may not cover all the interest charged on the loan. In that case, the borrower will not make progress in paying the loan off.

In addition, if the payment includes only a portion of the interest due, the borrower will end up paying interest on the money borrowed but will also pay interest on the interest not paid when due.

Amortization Schedule Calculator

Before going into detail on how a loan amortization schedule breaks down, you may be interested in checking out an amortization schedule calculator.

This tool produces a monthly payment schedule based on the purchase price, interest rate, and down payment on your home. It also factors in other home-related fees — property taxes, HOA fees, and insurance — to give you a full picture of how much you’ll pay every month for all home debt expenses.

Amortization Schedule Example

Your monthly amortization table serves as a countdown. It lists every scheduled payment off your loan, showing the allocation of the repayments every month and the declining balance on your debt until it reaches zero.

For example, if you borrow money and agree to repay the principal and interest on a 30-year mortgage, the amortization chart lists each monthly payment and how much of the payment is allocated toward interest charges and paying down the debt principal. It also shows how much the borrower has left to pay on their total balance.

Here’s an example of how the top of an amortization schedule might look for a loan of $50,000 being paid off $500 at a time.

INSTALLMENT	PAYMENT	INTEREST	PRINCIPAL	BALANCE
1	$500	$400	$100	$49,900
2	$500	$375	$125	$48,875
3	$500	$350	$150	$48,725

 

Each monthly payment is $500. Out of the first payment, $400 goes toward paying interest, and the remainder pays down the principal of the debt.

In the second month, $500 is still due. However, since the interest rate is computed on the balance owed — which is decreasing with every payment — less of that payment is allocated toward interest fees, and more is going toward paying off the principal.

The above example is a simplified version of an amortization schedule. With complex transactions like fixed-rate mortgages or car loans, the amortization schedule would be much longer and more exact.

How to Make an Amortization Schedule

For those who want to build out their own schedules, the process is fairly simple.

Create an Amortization Schedule in Excel

Here are some tips for creating an amortization schedule using Excel, Google Sheets, or your favorite spreadsheet software.

Enter Loan Details

Reserve the first four rows of a new spreadsheet for information about the loan, putting these labels in spaces A1 to A4:

	A	B
1	Loan Amount
2	Interest Rate
3	Total Term Months (15 Years)
4	Payments

 

Enter the numeric information in spaces B1 to B3, as in this example:

	A	B
1	Loan Amount	$270,000
2	Interest Rate	6.5%
3	Total Term Months (15 Years)	180
4	Payments

 

In cell B4, enter the formula for calculating the monthly payment, which is:

=ROUND(PMT($B$2/12,$B$3,-$B$1,0), 2)

After Excel completes the calculation, the resultant table will read:

	A	B
1	Loan Amount	$270,000
2	Interest Rate	6.5%
3	Total Term Months (15 Years)	180
4	Payments	$2,351.99

 

Map Out the Payment Schedule

On the same spreadsheet, skip down to row 6 or 7. Enter these headers across the cells in that row:

	A	B	C	D	E	F	G
1	Loan Amount	$270,000
2	Interest Rate	6.5%
3	Total Term Months (15 Years)	180
4	Payments	$2,351.99
5
6
7	Period	Beginning Balance	Payment	Principal	Interest	Cumulative Interest	Ending Balance
8

 

In the “Period” column (starting with cell A8), fill out the remaining months of the loan term.

Then, jump to row 8 to start entering the data and formula for creating the amortization schedule:

            B8: =$B$1

            C8: =$B$4

            E8: =ROUND($B8*($B$2/12), 2)  

In D8, enter the formula for the difference of the amount of loan interest (E8) from the payment total (C8): =$C8-$E8.

In H8, enter the formula for the difference of the principal portion (D8) from the beginning balance (B8): =$B8-$D8.

Next, it’s time to set up the rest of the schedule by skipping to cell B9 and entering the formula =$H8.Copy the contents of C8, D8, and E8 and paste them into their corresponding spots in row 9.

In cell F9, enter the formula =$D9+$F8. In cell G9, enter =$E9+$G8. Finally, copy all the cells between B9 and G9 and paste them to fill out your remaining spaces.

If you’ve done it correctly, the first three rows of your amortization schedule will look like this:

 

How to Calculate Monthly Mortgage Payment

Say you have purchased a $350,000 house and made a 20% down payment ($70,000). You have taken out a loan for the remaining $280,000 due at a fixed interest rate of 6.5% on a 15-year term.

Using a calculator, you’ll see that your interest and principal repayments and interest will total $2,439.10 every month. That’s strictly for the loan repayment; it doesn’t consider taxes, HOA fees, or insurance.

If all goes according to plan, you’ll pay off the amount you borrowed in 180 monthly payments (12 months times 15 years). Your first payment would reflect $1,516.67 in interest charges and $922.43 in paying down the debt principal. The resulting balance would be $279,077.57.

Every subsequent month, your interest figures go down. The interest is applied to your remaining balance, so you'll owe less in interest when you keep paying that off. Your monthly payments of $2,439.10 will progressively pay off more of the principal and less of the interest.

For example, in your second payment, you’ll pay $1,511.67 toward your interest debt ($5.00 less than your first payment) and $927.43 toward your principal payment ($5.00 more than your first payment).

Paying Off Your Mortgage Early

Some homeowners come into windfalls that allow them to pay their mortgage loans off before the end of the amortization schedule. This helps them save on interest charges, remove significant debt from their balance sheets, and build credit.

However, some unfavorable tax implications may come from early mortgage repayment. Check with your financial advisor to learn what they are.

Stay on Top of Your Finances With an Amortization Schedule

When you take out a loan for a house or car, an amortization schedule can help you get clarity on your finances during repayment. Follow these guidelines to build your own and gain a helpful perspective on your money.

---

Related Resources