Skip to content

5. Calculate Totals for Interest and Amount Paid

Playback Speed:
Transcript

In this video, you will calculate the total amount you will pay for loans of different amounts.

Then, you will calculate how much of that total amount is interest.

This will tell you how much it will cost to borrow the money over the course of the loan term.

When you take out a loan for a car, a home, or some other purchase, you pay back interest in addition to the principal, or original loan amount.

Add a column in your loan amount chart for Total Payments.

To calculate the total amount you will pay for the loan, multiply the monthly payment by the number of months.

In the first cell under Total Payment, type equals and select the monthly payment cell beside it.

Type a multiplication symbol, and click the number of payments cell.

As you have learned, cells that remain fixed need an absolute cell reference.

Make the number of payments cell absolute in this formula by typing dollar signs before the column letter and the row number.

Then, press Enter.

Good job!

Now this cell calculates the total amount you will spend on this loan.

Finally, calculate the amount you will pay in interest--that is, the money you will pay to the lender for using their money.

Create a column for Total Interest.

Then, subtract the initial loan amount from the total payments.

Type equals and select the total payments cell.

Type a minus sign, then select the initial loan amount cell.

These cell references remain relative, because they both change relative to the cell the formula is in.

Press Enter and check the math.

Great!

Highlight the total payments cell and the total interest cell.

Then, drag the cell handle down to copy the formulas to the rest of your sheet.

Terrific!

Your spreadsheet automatically calculates these values for all of the loan amounts in your sheet.

Take a look at these calculations.

Perhaps you could get a loan for $15,000, but the 400 dollar monthly payment does not fit your budget.

But maybe you could pay 300 dollars a month.

You could borrow 10 thousand dollars instead.

But look, also, at what you will pay in interest - almost 630 dollars!

You might be better off saving more towards a down payment before you finance the car.

If you could save 2 thousand dollars more, for example, you would only have to finance 8 thousand dollars.

You would save yourself over 60 dollars a month in payments.

If you have time, experiment with changing the APR and loan terms.

Type in a different APR to see how the monthly payments and total interest increase.

When you change the APR on your spreadsheet, the other calculations automatically update.

You might also be able to bring your monthly payments down by extending the loan term to five or six years.

Check this out: If you extend the loan to five years instead of three, the monthly payment decreases substantially.

That’s great, but you’ll pay over a thousand dollars in interest in the long run.

That’s over 400 dollars more than you would have paid if you financed the car for 3 years, and over ten percent of the principal loan amount!!

As with cell phones and housing options, decisions about car loans are not the same for everyone.

For you, it might be worth paying a few hundred dollars more over five years in order to keep your monthly payments lower.

But your neighbor may be more concerned about the total amount that they will pay.

Neither decision is right or wrong.

It’s about what works best for you and your situation.

But it always helps to be informed about all the options, and that’s where spreadsheets can help you prepare--and even save you money!

Now, it’s your turn: Calculate the total payment by multiplying the monthly payment by the number of payments.

Use an absolute cell reference for the number of payments.

Calculate the amount you will pay in interest by subtracting the loan amount from the total payments.

Copy these formulas to the rest of your loan amount table.

Experiment with changing the APR and loan terms to choose the best loan.

Then, move on to the next video where you will research several cars within the price range you set.

Next
Instructions
  1. Calculate the total payment (monthly payment * monthly loan term).
  2. Use an absolute cell reference ($) for the number of payments cell.
  3. Calculate the amount you will pay in interest (total payments - loan amount).
  4. Copy these formulas to the rest of your loan amount table.
  5. Experiment changing the APR and loan terms.
Attributions
  • "Money" by Ervins Strauhmanis (https://www.flickr.com/photos/ervins_strauhmanis/9547663730) -- Licensed by CC BY 2.0 (https://creativecommons.org/licenses/by/2.0/) -- Image scaled up, cropping edges
  • "BMW for sale" by JOHN LLOYD (https://www.flickr.com/photos/hugo90/4566994618) -- Licensed by CC BY 2.0 (https://creativecommons.org/licenses/by/2.0/) -- Image scaled up, cropping edges