Amortization Calculator

Here we are going to build out an amortization schedule for a loan, and it’s going to be one of those exercises like in high school where your teacher made you do it by hand, yet the entire time you were probably thinking, “this would be much easier with a calculator.” The good thing is that, in real life, we can use Excel, an online calculator, or some type of online spreadsheet to make our lives much easier. That being said, I’m going to show how to do it by hand because, in order to build out a schedule, we must first understand how to calculate all the parts.

Payments Formula

The total payment each period is calculated through the ordinary annuity formula.

Where:

• PMT = total payment each period
• PV = present value of loan (loan amount)
• i = period interest rate expressed as a decimal
• n = number of loan payments

The present value of an annuity formula equates how much a stream of equal payments made at regular intervals is worth at current time. By rearranging the formula, we can calculate how much each payment must be worth in order to equal a present value, where the present value is the value of the loan. The payment calculated will be the total payment each month for the duration of the loan. Loan payments consist of two parts: payments toward principal, and payments toward interest.

Calculating Payment towards Interest

As part of the total loan payment each period, the borrower must make a payment towards interest. The lender charges interest as the cost to the borrower of, well, borrowing the money. This is a result of the time value of money principle, since money today is worth more than money tomorrow. Interest is easy to calculate. You simply take the interest rate per period and multiply it by the value of the loan outstanding. The formula is shown below:

Where:

• P = principal remaining
• i = period interest rate expressed as a decimal

Calculating Payment towards Principal

There isn’t a good direct way to calculate the payment towards principal each month, but we can back into the value by subtracting the amount of interest paid in a period from the total payment each period. Since interest and principal are the only two parts of the payment per period, the sum of the interest per period and principal per period must equal the payment per period.

Amortization Schedule Example

Let’s take a look at an example. Suppose you take out a 3-year, $100,000 loan at 6.0% annually, with monthly payments. When building out a table, I think the most important part is the setup. Once a good table is set up, filling in the values is relatively easy. Below is an example of a table that could be used for the schedule:

Here, we can see how much we pay towards principal and interest each period, the total payment each period, and the remaining balance. You could add other columns, like cumulative principal payments made, and cumulative interest paid, but this is up to you.

Alright, now we have to actually fill in the table. We can start with each month’s “Payment” calculation. We will use the formula above, where the present value of the loan is $100,000, the interest rate per period is 0.06/12 since we are working with monthly payments, and our number of payments is 36, which is twelve payments per year for three years. The calculation is shown below:

So, each month, your total payment will be $3,042.19. Now, we need to calculate how much of that is paid towards interest each month. We will use our formula above, and the work is shown below for the first month:

The portion of the payment paid towards interest is $500 in the first period. The portion paid towards interest will change each period, since the balance of the loan will change each period, but I will dig into that in just a bit.

Next, we need to calculate the portion paid towards the principal, which is just the total payment less interest. The calculation is shown below:

We are almost done with our first period’s calculations. The last part, which I haven’t discussed yet, is how the balance changes. The balance of the loan after a period’s payment is the previous balance of the loan less the portion of the payment made towards principal. What you pay towards interest does not affect the balance of the loan. For our first period, the previous balance of the loan is the total balance. The calculation is shown below:

Now that we have all our values for the first line, we can begin to fill in our table.

Ok, so how do we fill in the rest? One thing is easy to fill in, which is the “Payment” column, since the payment will not change. In every row, our payment will be $3,042.19.

As with our calculations, the next thing we need to work on is interest. As I mentioned before, the interest each period will change as the balance of the loan changes. In the second period, since you only have $97,457.81 remaining to pay off, the interest portion of the second month’s payment will be $97,457.81 (the previous balance of the loan) times the interest rate for the period. The work is calculated exactly the same as the first month’s interest, yet the principal remaining is the previous balance of the loan. The second month’s interest is calculated as follows:

And our principal for the second period will be calculated the exact same way as before, where we simply subtract that period’s interest from the payment.

Our balance is also calculated the same way as before, where we subtract that period’s payment towards principal.

The rest of the table can be filled out using the iterative process described above. Here, I have condensed the table so that you only see the first three months and the last three months.

At the end of the three years, you will have paid off the entirety of the loan.