WEBVTT
Kind: captions
Language: en
00:00:00.140 --> 00:00:04.860
In this video, you will calculate the total amount you will pay for loans of different amounts.
00:00:04.860 --> 00:00:08.400
Then, you will calculate how much of that
total amount is interest.
00:00:08.400 --> 00:00:13.120
This will tell you how much it will cost to borrow the money over the course of the loan term.
00:00:13.130 --> 00:00:18.369
When you take out a loan for a car, a home,
or some other purchase, you pay back interest
00:00:18.369 --> 00:00:22.009
in addition to the principal, or original
loan amount.
00:00:22.009 --> 00:00:25.300
Add a column in your loan amount chart for
“Total Payments.”
00:00:25.300 --> 00:00:29.400
To calculate the total amount you will pay
for the loan, multiply the monthly payment
00:00:29.400 --> 00:00:30.640
by the number of months.
00:00:30.640 --> 00:00:35.680
In the first “Total Payment” cell, type
“equals” and select the monthly payment cell beside it.
00:00:36.860 --> 00:00:40.480
Type a multiplication symbol, and click the
“number of payments” cell.
00:00:41.420 --> 00:00:45.920
As you have learned, cells that remain fixed
need an absolute cell reference.
00:00:45.929 --> 00:00:50.190
Make the “number of payments” cell absolute
in this formula by typing dollar signs before
00:00:50.190 --> 00:00:52.400
the column letter and the row number.
00:00:52.400 --> 00:00:53.740
Then, press Enter.
00:00:53.740 --> 00:00:54.940
Good job!
00:00:54.940 --> 00:00:58.420
Now this cell calculates the total amount
you will spend on this loan.
00:00:58.420 --> 00:01:02.600
Finally, calculate the amount you will pay
in interest--that is, the money you will
00:01:02.609 --> 00:01:08.140
pay to the lender that is over and above the
principal, or original loan amount.
00:01:08.140 --> 00:01:10.240
Create a column for “Total Interest.”
00:01:10.240 --> 00:01:13.760
Then, subtract the initial loan amount from
the total payments.
00:01:13.760 --> 00:01:17.220
Type “equals” and select the total payments
cell.
00:01:17.220 --> 00:01:20.720
Type a minus sign, then select the initial
loan amount cell.
00:01:21.300 --> 00:01:26.940
These cell references remain relative, because they both change relative to the cell the formula is in.
00:01:26.940 --> 00:01:28.680
Press Enter and check the math.
00:01:28.680 --> 00:01:29.420
Great!
00:01:29.420 --> 00:01:32.560
Highlight the total payments cell and the
total interest cell.
00:01:32.720 --> 00:01:36.700
Then, drag the cell handle down to copy the
formulas to the rest of your sheet.
00:01:40.780 --> 00:01:41.340
Terrific!
00:01:41.340 --> 00:01:46.300
Your spreadsheet automatically calculates these values for all of the loan amounts in your sheet.
00:01:46.300 --> 00:01:48.200
Take a look at these calculations.
00:01:48.200 --> 00:01:54.740
Perhaps you could get a loan for $15,000,
but the 400 dollar monthly payment does not fit your budget.
00:01:54.740 --> 00:01:57.920
But maybe you COULD pay 300 dollars a month.
00:01:59.060 --> 00:02:01.340
You could borrow 10 thousand dollars instead.
00:02:01.340 --> 00:02:06.000
But look also at what you will pay in interest:
almost 630 dollars!
00:02:06.000 --> 00:02:09.980
You might be better off saving more towards
a down payment before you finance the car.
00:02:09.989 --> 00:02:14.260
If you could save 2 thousand dollars more,
for example, you would only have to finance
00:02:14.260 --> 00:02:15.760
8 thousand dollars.
00:02:15.760 --> 00:02:19.380
You would save yourself over 60 dollars a
month in payments
00:02:19.380 --> 00:02:23.940
If you have time, experiment with changing
the APR and loan terms.
00:02:23.940 --> 00:02:28.980
Type in a different APR to see how the monthly
payments and total interest increase.
00:02:28.980 --> 00:02:33.480
When you change the APR on your spreadsheet,
the other calculations automatically update.
00:02:33.480 --> 00:02:37.560
You might also be able to bring your monthly
payments down by extending the loan term
00:02:37.569 --> 00:02:39.500
to five or six years.
00:02:39.500 --> 00:02:42.620
A smaller monthly payment is always the best,
right?
00:02:42.620 --> 00:02:43.960
Not necessarily!
00:02:43.960 --> 00:02:49.880
Check this out: If you extend the loan to five years instead of three, the monthly payment decreases substantially.
00:02:49.880 --> 00:02:50.880
That’s great!
00:02:50.890 --> 00:02:54.709
BUT you’ll pay over a thousand dollars
in interest in the long run.
00:02:54.709 --> 00:02:59.190
That’s more than 400 dollars more than you
would have paid if you financed the car for
00:02:59.190 --> 00:03:02.650
3 years, and over ten percent of the principal
loan amount!
00:03:02.650 --> 00:03:07.870
As with cell phones and housing options, decisions
about car loans are not the same for everyone.
00:03:07.870 --> 00:03:12.209
For you, it might be worth paying a few hundred
dollars more over five years in order to keep
00:03:12.209 --> 00:03:13.560
your monthly payments lower.
00:03:13.560 --> 00:03:17.680
But your neighbor may be more concerned about
the total amount that they will pay.
00:03:17.680 --> 00:03:20.040
Neither decision is right or wrong.
00:03:20.040 --> 00:03:23.600
It’s about what works best for you and your
situation.
00:03:23.600 --> 00:03:27.200
But it always helps to be informed about all
the options.
00:03:27.209 --> 00:03:30.909
And that’s where spreadsheets can help you
prepare--and even save you money!
00:03:30.909 --> 00:03:31.880
Now, it’s your turn
00:03:31.880 --> 00:03:36.720
Calculate the total payment by multiplying the monthly payment by the monthly loan term.
00:03:36.730 --> 00:03:39.830
Use an absolute cell reference for the number
of payments cell.
00:03:39.830 --> 00:03:45.200
Calculate the amount you will pay in interest by subtracting the loan amount from the total payments.
00:03:45.200 --> 00:03:48.460
Copy these formulas to the rest of your loan
amount table.
00:03:48.460 --> 00:03:53.340
Experiment with changing the APR and loan
terms to work out the best plan for your buyer.
00:03:53.340 --> 00:03:58.480
Then, move on to the next video where you will research several cars within the price range you set.