Calculating Interest and Excel Functions:

Use Excel to Find the Payment and Total Interest on a Loan

This lesson combines what you learned about calculating the payment on a loan using the PMT function with calculating the total amount of the loan in order to calculate the interest on a loan. As you watch this video, follow along in Excel. You can use Excel for your practice problems and the quizzes, so it will be helpful for you to practice.

Video Source (07:26 mins) | Transcript

Total interest = Total amount of loan − Original principal amount

Practice Problems

Use Excel to calculate the total interest on a $45,000 loan for a small business making monthly payments with an interest rate of 7.5% over 10 years.

Use Excel to calculate the total interest on a $45,000 loan for a small business making monthly payments with an interest rate of 5.25% over 15 years.

Use Excel to calculate the total interest on a car loan paid monthly given the following values:
- Amount of loan = 13,500
- Annual interest rate = 5.75%
- Length of the loan = 6 years

Use Excel to calculate the total interest on a car loan paid monthly given the following values:
- Amount of loan = 13,500
- Annual interest rate = 4.25%
- Length of the loan = 6 years

Use Excel to calculate the total interest on a mortgage paid monthly given the following values:
- Amount of loan = 245,500
- Annual interest rate = 3.875%
- Length of the loan = 30 years

Use Excel to calculate the total interest on a cell phone for $800 at 5.25% interest over two years making monthly payments.

Solutions

$19,098.96
$20,114.09 (Written Solution)

To find out the total interest, we need to start by finding out the total amount that we have paid. We know the following:

Amount of loan = pv = $45,000

Annual Interest rate = r = 5.25%

Length of the loan = 15 years

Payments per year = n = 12
Using this information we can find the following:
$\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0525}{12}\text{ or }\frac{5.25\%}{12}$

Total number of payments = nper = years × n = 15 × 12 = 180

We put the above information into Excel so that it can do the calculations for us:

We will have Excel do the calculations for us. Any time we begin with an ‘=’ it tells Excel that we want it to make a calculation.

To find the periodic rate, we click on the cell where we want the calculation to be performed, then enter:

=B3/B6

(B3 contains the annual interest rate and B6 contains the number of payments per year.)

Then hit enter.

Next, we will calculate the total number of payments (nper). The total number of payments is found by multiplying the length of the loan by the number of payments per year. We will use Excel to calculate the total number of payments by clicking on the corresponding cell and typing in the following:

=B5*B6

(B5 contains the length of the loan in years and B6 contains the payments per year.)

Then hit enter.

To calculate the monthly payment, we will type the following into the corresponding cell:

=pmt(B4,B7,B2)

(B4 contains the periodic rate or RATE, B7 contains the total number of payments or NPER, B2 contains the amount of loan or PV.)

Then hit enter.

To calculate the total loan amount with interest we multiply the monthly payment by the total number of payments (nper). We will have Excel calculate that for us by typing in the following:

=B8*B7

(B8 contains the monthly payment. B7 contains the total number of payments.)

Then hit enter.

To calculate the interest paid we add the total amount with interest to the amount of the loan. We will have Excel make the calculation by typing in:

=B9+B2

(B9 contains the total amount with interest. B2 contains the amount of the loan.)

Then hit enter.

The value that appears in B10 is ($20114.09).

So the total interest paid is $20,114.09
$2,494.39
$1,818.09 (Written Solution)

To find out the total interest, we need to start by finding out the total amount that we have paid. We know the following:

Amount of loan = pv = $13,500

Annual Interest rate = r = 4.25%

Length of the loan = 6 years

Payments per year = n = 12

Using this information we can find the following:

$\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0425}{12}\text{ or }\frac{4.25\%}{12}$

Total number of payments = nper = years × n = 6 × 12 = 72

We put the above information into Excel so that it can do the calculations for us:

We will have Excel do the calculations for us. Any time we begin with an ‘=’ it tells Excel that we want it to make a calculation.

To find the periodic rate, we click on the cell where we want the calculation to be performed, then enter:

=B3/B6

(B3 contains the annual interest rate and B6 contains the number of payments per year.)

Then hit enter.

Next, we will calculate the total number of payments (nper). The total number of payments is found by multiplying the length of the loan by the number of payments per year. We will use Excel to calculate the total number of payments by clicking on the corresponding cell and typing in the following:

=B5*B6

(B5 contains the length of the loan in years and B6 contains the payments per year.)

Then hit enter.

To calculate the monthly payment, we will type the following into the correct cell:

=pmt(B4,B7,B2)

(B4 contains the periodic rate or RATE, B7 contains the total number of payments or NPER, B2 contains the amount of loan or PV.)

Then hit enter.

To calculate the total loan amount with interest we multiply the monthly payment by the total number of payments (nper). We will have Excel calculate that for us by typing in the following:

=B8*B7

(B8 contains the monthly payment. B7 contains the total number of payments.)

Then hit enter.

To calculate the interest paid we add the total amount with interest to the amount of the loan. We will have Excel make the calculation by typing in:

=B9+B2

(B9 contains the total amount with interest. B2 contains the amount of the loan.)

Then hit enter.

The amount that appears in cell B10 is ($1818.09). This is the total amount of interest paid on the loan.
$170,095.53
$44.48