Back
Use Excel to Find the Payment and Total Interest on a Loan
> ... Math > Calculating Interest, 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.

Use Excel to Calculate the Total Interest Charged on a Loan

Total interest = Total amount of loan − Original principal amount.

### Practice Problems

1. 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. (
Solution
Solution:
$$19,098.96$$
)
2. 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. (
Solution
Solution:
$$20,114.09$$
Details:
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:

$$\displaystyle\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 \times n = 15 \times 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 select the cell where we want the calculation to be performed, then type:

=B3/B6

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

Then press 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 selecting the corresponding cell and typing the following:

=B5*B6

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

Then press 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 press 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 press 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 the following:

=B9+B2

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

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

So the total interest paid is $$20,114.09$$
)
3. Use Excel to calculate the total interest on a car loan paid monthly given the following values: (
Solution
Solution:
$$2,494.39$$
)
• Amount of loan = $$13,500$$
• Annual interest rate = $$5.75\%$$
• Length of the loan = 6 years
4. Use Excel to calculate the total interest on a car loan paid monthly given the following values: (
Solution
Solution:
$$1,818.09$$
Details:
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:

$$\displaystyle\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 \times n = 6 \times 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 select the cell where we want the calculation to be performed, then type:

=B3/B6

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

Then press 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 selecting the corresponding cell and typing the following:

=B5*B6

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

Then press 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 press 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 the following:

=B8*B7

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

Then press 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 the following:

=B9+B2

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

Then press enter. The amount that appears in cell B10 is ($$1818.09$$). This is the total amount of interest paid on the loan.
)
• Amount of loan = $$13,500$$
• Annual interest rate = $$4.25\%$$
• Length of the loan = 6 years
5. Use Excel to calculate the total interest on a mortgage paid monthly given the following values: (
Solution
Solution:
$$170,095.53$$
)
• Amount of loan = $$245,500$$
• Annual interest rate = $$3.875\%$$
• Length of the loan = 30 years
6. Use Excel to calculate the total interest on a cell phone for $$800$$ at $$5.25\%$$ interest over two years making monthly payments. (
Solution
Solution:
$$44.48$$
)

## Need More Help?

1. Study other Math Lessons in the Resource Center.
2. Visit the Online Tutoring Resources in the Resource Center.