You can afford $200 per month as a car payment.  If you can get an auto loan through a local credit union at 3% interest for 60 months (5 years), how expensive of a car can you afford?  In other words, what amount loan can you pay off with $200 per month? In addition how much interest is paid over the life of the loan (what is the cost of the loan)?

Solution

In this example we are given the following:

$$\begin{array}{lll}\mathrm{pymt}\hfill & =\$200\hfill & \text{the monthly loan payment}\hfill \\ r\hfill & =0.03\hfill & \text{3\% annual rate}\hfill \\ n\hfill & =12\hfill & \text{since we’re doing monthly payments}\hfill \\ t\hfill & =5\hfill & \text{since we’re making monthly payments for 5 years}\hfill \end{array}$$

We’re looking for $P$, the starting principal amount of the loan.

$$\begin{array}{l}P=\frac{\text{200}·\left(1–{\left(1+\frac{0.03}{12}\right)}^{\left(–12·5\right)}\right)}{\left(\frac{0.03}{12}\right)}\\ P˜\$11,130.47\end{array}$$

You can afford a $11,130 loan.

You will pay a total of $12,000 ($200 per month for 60 months) to the loan company.  The difference between the amount you pay and the amount of the loan is the interest paid.  In this case, you’re paying $12,000-$11,130 = $870 interest total.

You want to take out a $140,000 mortgage (amortized loan).  The interest rate on the loan is 6%, and the loan is for 30 years.  How much will your monthly payments be?

Solution

In this example we’re looking for the amount of the payment, $\mathrm{pymt}$.

$$\begin{array}{lll}P\hfill & =\$140,000\hfill & \text{the starting loan amount}\hfill \\ r\hfill & =0.06\hfill & \text{6\% annual rate}\hfill \\ n\hfill & =12\hfill & \text{since we’re doing monthly payments}\hfill \\ t\hfill & =30\hfill & \text{since we’re making monthly payments for 30 years}\hfill \end{array}$$

Use the formula where pymt was already solved for

$$\begin{array}{l}\text{pymt}=\frac{140,000·\left(\frac{0.06}{12}\right)}{\left(1–{\left(1+\frac{0.06}{12}\right)}^{\left(–12·30\right)}\right)}\\ \text{pymt}˜\$839.37\end{array}$$

You will make payments of $839.37 per month for 30 years.

If you add all the payments up for 30 years you are paying a total of $302,173.20 to the loan company. This means the cost of the loan (interest) is:

$$\$302,173.20 –\$140,000 =\$162,173.20$$

This is not unusual to see the interest being paid over the life of a 30 year loan to be about the same as the amount borrowed for interest rates around 6%.

Going back to the second example. You take out a loan with an initial principal of $140,000. The interest rate on the loan is 6% with a term of 30 years. The monthly payments were found to be $839.37. Determine the balance of the loan at 5, 10, 15, 20, and 25 years into the loan.

Was half of the balance paid at the half way mark of 15 years?

Solution

In the table below the first column time in years represents the amount of time after the loan has begun. When looking in the formula in the second column we see the time remaining is used to find the principal amount on a loan with the given time remaining. This will represent the balance of the loan. For instance 5 years after the loan was started there was 25 years remaining (left for the loan). The balance of the loan after 5 years was found to be $130,275.99.

Looking at the table we see that after half the time for the term of the loan has passed (15 years) there is still more than half of the balance remaining. We don’t actually see the balance dip below half ($70,000) until after 20 years has passed.

If we plot the data for the balance of the loan after a given number of years we get the graph as shown below. Notice how slowly the balance is reduced at the beginning of the loan term, but quickly accelerates to zero as you get closer to the end of the term.

A couple purchases a home with a $180,000 mortgage at 4% for 30 years with monthly payments.  What will the remaining balance on their mortgage be after 5 years?

Solution

First we will calculate their monthly payments as that will be needed to find the remaining balance. We will start with listing out what is known:

$$\begin{array}{lll}P\hfill & =\$180,000\hfill & \text{the starting loan amount}\hfill \\ r\hfill & =0.04\hfill & \text{4\% annual rate}\hfill \\ n\hfill & =12\hfill & \text{since they’re paying monthly}\hfill \\ t\hfill & =30\hfill & \text{since they’re making monthly payments for 30 more years}\hfill \end{array}$$

Now solve for $\mathrm{pymt}$:

$$\begin{array}{l}\text{pymt}=\frac{P·\left(\frac{r}{n}\right)}{\left(1–{\left(1+\frac{r}{n}\right)}^{\left(–n·t\right)}\right)}\\ \text{pymt}=\frac{180000·\left(\frac{0.04}{12}\right)}{\left(1–{\left(1+\frac{0.04}{12}\right)}^{\left(–12·30\right)}\right)}\\ \text{pymt}=\$859.35\end{array}$$

Now that we know the monthly payments, we can determine the remaining balance after 5 years (or 25 years left on the loan). Calculate a loan balance that will be paid off with the monthly payments of $859.35 over 25 years:

$$\begin{array}{lll}\mathrm{pymt}\hfill & =\$859.35\hfill & \text{the monthly loan payment we calculated above}\hfill \\ r\hfill & =0.04\hfill & \text{4\% annual rate}\hfill \\ n\hfill & =12\hfill & \text{since they’re paying monthly}\hfill \\ t\hfill & =25\hfill & \text{since they’d be making monthly payments for 25 more years}\hfill \end{array}$$

Now solve for $P$ in the loan formula:

$$\begin{array}{l}P=\frac{\text{pymt}·\left(1–{\left(1+\frac{r}{n}\right)}^{\left(–n·t\right)}\right)}{\left(\frac{r}{n}\right)}\\ P=\frac{\text{859}\text{.35}·\left(1–{\left(1+\frac{0.04}{12}\right)}^{\left(–12·25\right)}\right)}{\left(\frac{0.04}{12}\right)}\\ P˜\$162,805.99\end{array}$$

The loan balance after 5 years, with 25 years remaining on the loan, will be $162,805.99

Over that 5 years, the couple has paid off $\$180,000–\$162,805.99=\$17,194.01$ of the loan balance. 

If we were interested in finding out how much interest was paid over those five yeras we first find the total that was paid in payments over the five years and then subtract off the amount paid to the loan over the five years we just saw from above.

They have paid a total of $859.35 a month for 5 years (60 months), for a total of $51,561.00, so $\$51561.00–\$17,194.01=\$34,366.99$ of what they have paid so far has been interest.

An amount of $500 is borrowed with an amortized loan for 6 months at a rate of 12%. This loan would have a payment of $86.27. Make an amortization schedule showing the monthly payment, the monthly interest on the outstanding balance, the portion of the payment contributing toward reducing the debt, and the outstanding balance.

Solution

With the first payment we use the entire principal to determine the amount of interest paid from the payment:

$$\left(\text{principal}\right)\left(\text{monthly interest rate}\right)=\left(500\right)\left(\frac{0.12}{12}\right)=\$5$$

This means, the first month, out of the $86.27 payment, $5 goes toward the interest and the remaining $81.27 toward the principal (or balance of the loan) leaving a new balance of:

$$\$500–\$81.27=\$418.73$$

Let us fill that out in the first row of the table:

Now in the second month we see the outstanding balance is $418.73. Calculate the monthly interest on the outstanding balance:

$$\left(418.73\right)\left(\frac{0.12}{12}\right)=\$4.19$$

Out of the $86.27 payment, $4.19 goes toward the interest and the remaining $82.08 toward the balance leaving a new balance of:

$$\$418.73–\$82.08=\$336.65$$

The process continues in the table below for the other months as well:

Note that the last balance of 3 cents is due to error in rounding off. This round off value is typically added to the last payment for the loan, so instead of paying $86.27 the borrow would have paid $86.30.

Abigail purchases a home for $160,000 and the loan is amortized over 30 years at an interest rate of 4.4%. Complete an amortized loan schedule in a spread sheet and answer the following questions.

1. Find the balance after the 100th payment.
2. Find the balance owed after 20 years
3. Determine the cost of the loan (interest).
4. If an extra $200 a month is paid towards the balance find the new number of payments and the total cost of the loan.
5. If an extra $400 a month is paid towards the balance find the new number of payments and the total cost of the loan.

Solution

To complete the amortization schedule we need to start by finding the monthly payments

$$\begin{array}{l}\text{pymt}=\frac{160,000·\left(\frac{0.044}{12}\right)}{\left(1–{\left(1+\frac{0.044}{12}\right)}^{\left(–12·30\right)}\right)}\\ \text{pymt}˜\$801.22\end{array}$$

Below represents the first three payments in the amortization schedule. View the full schedule in Google Sheets.

1. From the schedule we see the balance after the 100th payment is $134,139.52.
   

   Payment #	Payment	Interest	Principal Paid	Extra Payment	Balance After Payment
   100	$801.22	$492.98	$308.24		$134,139.52

2. After 20 years is the same as after the 20*12=240th payment. This balance is $77,668.70
   

   Payment #	Payment	Interest	Principal Paid	Extra Payment	Balance After Payment
   240	$801.22	$286.67	$514.55		$77,668.70

3. The cost of the loan can be found by adding up all the interest payments in the schedule. This total was found to be $128,437.30 when adding the interest payments found in the google sheets document. It will be slightly different due to rounding from taking the sum of all payments and subtracting the principal of the loan (that value would be $128.439.20).
4. In the second tab of the Google Sheets file we can see the extra $200 per month added to each payment that goes towards reducing the balance in addition to the principal portion of a payment. The loan would now finish at the 241 payment as that is where the balance is now a negative value. This is 20 years plus 1 month. The cost of the loan is found by adding all the interest payments and we find the cost is $81,218.

   By adding an extra $200 per month for each payment the borrower pays approximately $47,000 less in interest over the life of the loan.

5. In the third tab of the document we see what happens when an extra $400 is applied each month. There is a total of 184 payments yielding a cost for the loan of $59,963.