8.4 Loans
In the last section, you learned about payout annuities.
In this section, you will learn about conventional loans (also called amortized loans or installment loans). Examples include auto loans and home mortgages. These techniques do not apply to payday loans, add-on loans, or other loan types where the interest is calculated up front.
One great thing about loans is that they use exactly the same formula as a payout annuity. To see why, imagine that you had $10,000 invested at a bank, and started taking out payments while earning interest as part of a payout annuity, and after 5 years your balance was zero. Flip that around, and imagine that you are acting as the bank, and a car lender is acting as you. The car lender invests $10,000 in you. Since you’re acting as the bank, you pay interest. The car lender takes payments until the balance is zero.
Loan Formula
- is the balance in the account at the beginning (starting amount, present value, principal or amount of loan.)
- is the loan payment (the amount you take out each year, each month, etc.)
- is the annual interest rate (in decimal form for this formula)
- is the number of compounding periods in one year
- is the length of the loan, in years
Like before, the compounding frequency is not always explicitly given, but is determined by how often you make payments.
When to use this
The loan formula assumes that you make loan payments on a regular schedule (every month, year, quarter, etc.) and are paying interest on the loan.
Compound interest: One deposit
Annuity: Many deposits.
Payout Annuity: Many withdrawals
Loans: Many payments
Example of a Loan
You can afford $200 per month as a car payment. If you can get an auto loan 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 this example,
the monthly loan payment
3% annual rate
since we’re doing monthly payments, we’ll compound monthly
since we’re making monthly payments for 5 years
We’re looking for P; the starting amount of the loan.
Putting this into the equation:
You can afford an $11,120 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,120 = $880 interest total.
Using Technology
Similar to the annuities in the last section, we can use TVM Solver on a calculator or Excel to solve these problems as well.
On a TI 83/84 Calculator
Once again, we go to APPS and 1: Finance and 1: TVM Solver. The differences now is that our PV is positive because we are given the money to buy something, PMT is now negative because this is money we are giving the bank each month (or k times a year). The FV will be 0 because our ending amount should be nothing; we should be paying off the loan. (Later in the section, this might change because we can look at loan balances at any time.) We put the cursor on which we want to solve and hit ALPHA-ENTER.
For the previous example:
| Enter the Information | After going to PV and ALPHA-ENTER |
Notice that our starting amount here is a little larger than the answer we got by hand. Again, this is due to intermediate round-off error.
On CASIO
Again, this is similar to our procedure in the last section. Go to Menu-TVM and F2: for Compound Interest as it will allow us to put a PMT there. We fill in everything as before except we end with 0, so our FV or future value is 0, PMT is negative. We use F3 to find PV or Present Value needed. (Note, if you do not see the bottom that shows what the function buttons indicate, try pressing EXE.)
For the previous example:
| Enter the Information: | After hitting F3 to find PV: |
In Excel or Google Sheets
In Excel or Google Sheets, there are several commands that figure compound interest for payout annuities:
- =PV(r/k, kt, -PMT) will provide the P or present value or loan amount, given the other variables
- =PMT(r/k, kt, P) will provide the payment amount required for a $P loan for t years compounded k times a year.
- =NPER(r/k, -PMT, P) will give the number of periods it will take to pay off the loan, can be divided by k to get years.
- =RATE(kt, -PMT, P) will give the rate of a loan with payments, PMT
For our previous example, we would type in =PV(0.03/12, 60, -200)
Example Determining Amount of Possible Withdrawal
You want to take out a $140,000 mortgage (home loan). The interest rate on the loan is 6%, and the loan is for 30 years. How much will your monthly payments be?
In this example,
We’re looking for .
or 6% annual rate
since we’re withdrawing monthly
years
the starting loan amount
In this case, we’re going to have to set up the equation, and solve for .
You will make payments of $839.37 per month for 30 years.
You’re paying a total of $302,173.20 to the loan company: $839.37 per month for 360 months. You are paying a total of $302,173.20 – $140,000 = $162,173.20 in interest over the life of the loan.
Alternatively, you could use a calculator or Excel. On Calculator, the TVM solver would be filled in as:
N = 360
I% = 6
PV = 140000
PMT = 0 for now – or it doesn’t matter as this is what we are solving for)
FV = 0
P/Y = 12
C/Y = 12
END
On a TI: Cursor back to PMT and hit ALPHA-ENTER and PMT will change to -839.37073. or in this case our payment would be $839.37 as found by hand as well.
On the CASIO: Hit F4 for PMT and it calculates the same as the TI.
On Excel: =PMT(.06/12, 360, 140000)
To see what is happening to the loan balance over time, we could build an amortization schedule, a spreadsheet that shows the details of the payoff. To create one for the previous example, each month we’d calculated the interest charge as 3%/12 = 0.25% interest on the remaining loan balance. Payment beyond the interest charge goes towards principal and reduces the remaining loan balance. The first several months are shown here:
| Month | Payment | Interest Charge | Portion to Principal | Remaining Loan Balance |
| 0 | — | — | — | $140,000.00 |
| 1 | $839.37 | $350.00 | $489.37 | 139,510.63 |
| 2 | 839.37 | 348.78 | 490.59 | 139,020.04 |
| 3 | 839.37 | 347.55 | 491.82 | 138,528.22 |
| 4 | 839.37 | 346.32 | 493.05 | 138,035.17 |
Notice that the interest charge decreases each month as the loan balance decreases. There are several online calculators for amortization and a procedure in excel as well or you can watch a video also.
Try it Now 1
Janine bought $3,000 of new furniture on credit. Because her credit score isn’t very good, the store is charging her a fairly high interest rate on the loan: 16%. If she agreed to pay off the furniture over 2 years, how much will she have to pay each month?
Example Finding the Number of Periods
Consider the $140,000 mortgage at 6% from the previous example. If the homeowner increased their payments to $1000 per month, how long will it take them to pay off the loan?
or 6% annual rate
since we’re withdrawing monthly
the starting loan amount
the given payment amount
We are solving for , the amount of time it will take to pay off.
The loan will be paid off in about 20 years.
By increasing the payment by about 20%, they were able to decrease the length of the loan by about 33%, and reduce interest paid by more than $60,000.
This could also be accomplished using a calculator or Excel.
On Calculator, the TVM solver would be filled in as:
N =0 for now – or it doesn’t matter as this is what we are solving for)
I% = 6
PV = 140000
PMT = -1000
FV = 0
P/Y = 12
C/Y = 12
END
On a TI: Cursor back to N and hit ALPHA-ENTER and N will change to 241.396 if you use 2nd MODE and then type in 241.396/12 = 20.12 years.
On the CASIO: Hit F1 for PMT and it calculates the same as the TI. Then go back to the MENU and RUN-MAT and do the division above.
On Excel: =NPER(.06/12, -1000, 140000) and in Excel, it is simple to go to another cell, hit = then click the cell with the NPER command and /12 and enter.
Try it Now 2
Julio is considering putting a $1,000 laptop purchase on his credit card, which has an interest rate of 12% compounded monthly. How long will it take him to pay off the purchase if he makes payments of $30 a month?
Remaining Loan Balance
With loans, it is often desirable to determine what the remaining loan balance will be after some number of years. For example, if you purchase a home and plan to sell it in five years, you might want to know how much of the loan balance you will have paid off and how much you have to pay from the sale.
To determine the remaining loan balance after some number of years, we first need to know the loan payments, if we don’t already know them. Remember that only a portion of your loan payments go towards the loan balance; a portion is going to go towards interest. For example, if your payments were $1,000 a month, after a year you will not have paid off $12,000 of the loan balance.
To determine the remaining loan balance, we can think “how much loan will these loan payments be able to pay off in the remaining time on the loan?”
Example Finding an Balance of a Loan at a Point in Time
If a mortgage at a 6% interest rate has payments of $1,000 a month, how much will the loan balance be 10 years from the end the loan?
To determine this, we are looking for the amount of the loan that can be paid off by $1,000 a month payments in 10 years. In other words, we’re looking for P (PV) when:
or 6% annual rate
since we’re withdrawing monthly
years since we will be making monthly payments for 10 more years
the given payment amount
The loan balance with 10 years remaining on the loan will be $90,073.45
This could also be accomplished using a calculator or Excel.
On Calculator, the TVM solver would be filled in as:
N =120
I% = 6
PV = this is what we are solving for so it does not matter
PMT = -1000
FV = 0
P/Y = 12
C/Y = 12
END
On a TI: Cursor back to PV and hit ALPHA-ENTER and PV will change to 90073.45333 or $90,073.45.
On the CASIO: Hit F3 for PV and it calculates the same as the TI.
On Excel: =PV(.06/12, 120,-1000)
Often times answering remaining balance questions requires two steps:
- Calculating the monthly payments on the loan
- Calculating the remaining loan balance based on the remaining time on the loan
Example Finding Remaining Balance given Original Loan Amount.
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?
First we will calculate their monthly payments.
We’re looking for PMT.
or 4% annual rate
since we’re withdrawing monthly
years
the starting loan amount
We set up the equation and solve for PMT.
Now that we know the monthly payments, we can determine the remaining balance. We want the remaining balance after 5 years, when 25 years will be remaining on the loan, so we calculate the loan balance that will be paid off with the monthly payments over those 25 years.
or 4% annual rate
since we’re withdrawing monthly
years
the monthly loan payment we calculated above
The loan balance after 5 years, with 25 years remaining on the loan, will be $162,758
Over that 5 years, the couple has paid off $180,000 – $162,758 = $17,242 of the loan balance. They have paid a total of $858.93 a month for 5 years (60 months), for a total of $51,535.80, so $51,535.80 – $17,242 = $34,292.80 of what they have paid so far has been interest.
These steps could also be accomplished using a calculator or Excel.
On Calculator, the TVM solver would be filled in as:
N =360
I% = 4
PV = 180000
PMT = 0
FV = 0
P/Y = 12
C/Y = 12
END
On a TI: Cursor back to PMT and hit ALPHA-ENTER and PMT will change to $859.35
For step 2: Change N to 300, cursor to PV and ALPHA-ENTER, we get $162,805.52
On the CASIO: Hit F4 for PMT and it calculates the same as the TI.
For step 2: Click F1 for Repeat, this takes you back to the TVM solver screen. Change n to 300 EXE and click F3 for PV
On Excel: =PMT(.04/12, 360,180000) in Cell A1 for example and then =PV(.04,12,300,A1)
Try it Now Answers
-
- unknown
or 16% annual rate
since we’re making monthly payments
years to repay
we’re starting with $3,000 loanSolving for PMT gives $146.89 as monthly payments.In total, she will pay $3,525.36 to the store, meaning she will pay $525.36 in interest over the two years. Using a calculator or Excel =PMT(0.16/12,24,3000) yields the same results.
- unknown
or 12% annual rate
since we’re making monthly payments
we’re starting with $1,000 loan. We are solving for t, the time to pay off the loanSolving for t gives 3.396 years of monthly payments. It will take about 3.4 years to pay off the purchase. In total, he will pay $1222.50 to the credit card company, meaning he will pay $222.50 in interest over the two years. Using a calculator or Excel =NPER(0.12/12,-30,1000) and dividing by 12 yields the same results.
Media Attributions
- 84example1
- 84example2
- 84example3
- 84example4