How To Make An Amortization Schedule

Basics Mar 1, 2022

Are you trying to evaluate loans or mortgages? Amortization schedules make finding the break-even point of discount points easy, and more. They are a handy tool for seeing exact breakdowns of debt payments.

With spreadsheets, it has become almost trivial to make an amortization schedule.

What Is An Amortization Schedule?

Amortization schedules (or amortization tables) are breakdowns of principal and interest payments on instalment loans, usually mortgages, car loans, or student loans.

From an amortization table you can easily determine how much total interest will be paid over the lifetime of the loan. Simply sum up the interest paid column for each period.

An example amortization schedule for a 3% APR loan of $100k with payments of $1000

Amortization tables are useful for developing an intuition on debts. For instance, here's some lessons learned from playing with amortization tables:

Extra principal payments early on will compound and drastically reduce the total interest paid over the lifetime of the loan.
Loan lifetime interest usually is a significant portion of the total cost of the loan: e.g. on a 30-year mortgage of $100k at 3% APR, there's $51k in interest, meaning a total cost of $151k paid and a third of that is interest.
Slight changes in interest rates have large swings in total interest paid.
Early on most of your payment goes towards interest.
Later on most of your payment is principal, and there's very little interest.

How To Use An Amortization Table

The main use case for an amortization table is when you purchase discount points, and you want to know the break-even point.

That number is easy to find when you use discount points to reduce the periodic payment. It's just the cost to reduce APR divided by the difference of payments, which gives you the approximate number of periods before it breaks even.

For example, if you have $1000 in discount points, and it reduces your monthly payments by $25, then you have 40 months before it breaks even.

The break even point is useful to know if you are e.g. mortgaging a house but planning to sell it before it's paid off.

However, there's another break-even point that is slightly harder to calculate: discount points with the same payment amount.

Rather than using discount points to reduce your periodic payment, you keep the payment amount the same. For example, paying the highest amount you can fit into your budget safely.

Two amortization tables used to find the break-even point, such as when considering discount points with a fixed payment amount

In the above image, there's two amortization tables side-by-side to easily compare their payoff rates. Notice that the break-even point is 24 periods in this example, where $1k is used to reduce the APR for a fixed payment loan.

You can also get creative with using amortization tables.

Want to see how a lump-sum deposit into the loan affects things? Plug it in and see!

Want to compare renting vs. buying? Build an amortization table to account for equity in the house.

I did precisely that during my first house hunt. I compared the expenses of renting with the expenses of a house, and found that renting was the better option by a few hundred dollars. In theory, at least.

In practise, there's two factors I didn't account for in my amortization table and rent comparison: one is that my dearheart disposed of excess income reliably when renting, and did not actually save the excess. The second is that owning a home has afforded us to get a lodger who brings in income.

Between the forced savings of principal payments on our mortgage and the extra household income, owning a house worked out in our favour.

Using Amortization Tables is Mental Exercise

I'm a firm believer in keeping my mind active.

Amortization tables are now like simple toys for me to play with, but this was not always the case. Everyone starts somewhere, and I found amortization schedules to be a good way to get comfortable with the minutiae of loans.

Spending a couple hours sitting with a spreadsheet running numbers through an amortization table is like lifting weights for the mind. If you're thinking critically, you can develop an intuition and discover some lessons for yourself.

For instance, some questions to ask yourself might be "when is it better to dump a lump sum into a loan rather than investing it?" as well as "how does inflation affect payoff strategies?"

Ultimately though, the goal is to keep challenging my mind with new concepts and detailed procedures. I wish to grow and be pushed to greatness, not stagnate and become complacent.

How To Make An Amortization Table

To build an amortization table, you need to know the initial amount borrowed (principal), the APR/periodic interest, and the payment amount per period.

The payment amount is normally generated via equated monthly instalments. There's a formula that I won't go into, because you'll either be told a payment amount when getting a loan, or you can look up a calculator that calculates it for you.

Converting from APR to periodic interest is as simple as dividing APR by the number of periods in a year, like APR / 12 for monthly payments.

An example amortization schedule from earlier

Interest paid in a period is Period Interest Rate * Balance. Principal paid is Payment - Interest Paid. Then the ending balance is Starting Balance - Principal Paid.

Simple, see? You then carry the ending balance from the past period to the starting balance of the next period.

You can play around with such an amortization table, seeing what happens when you plug in different payments for specific months, or tweak interest rates, and so on.

If you do so, you'll hopefully find yourself more comfortable with evaluating loans.