WEBVTT
Kind: captions
Language: en
00:00:00.170 --> 00:00:05.000
At this point, your spreadsheet returns the
monthly payment amounts as a negative number
00:00:05.000 --> 00:00:06.500
with many decimal points.
00:00:06.500 --> 00:00:12.269
In this video, you will add an absolute value
formula to return only positive numbers and
00:00:12.269 --> 00:00:14.940
reformat the monthly payment cells as currency.
00:00:14.940 --> 00:00:19.820
In the last video, you changed some relative
cell references to absolute references.
00:00:19.820 --> 00:00:23.220
But something is still off about these calculations.
00:00:23.220 --> 00:00:28.760
With this function, the spreadsheet returns
a negative value for each of these calculations.
00:00:28.760 --> 00:00:34.100
This would work well in a budgeting spreadsheet where you wanted to subtract a payment amount each month.
00:00:34.100 --> 00:00:37.560
But in this case, you do not need a negative
number.
00:00:37.569 --> 00:00:41.960
To correct this, convert these values to an
absolute value, so that they always appear
00:00:41.960 --> 00:00:43.320
as positive numbers.
00:00:43.320 --> 00:00:45.839
Return again to the original formula cell.
00:00:45.839 --> 00:00:49.360
Place the cursor between the equals sign and
“PMT.”
00:00:49.360 --> 00:00:53.100
Type “ABS” and an open parentheses.
00:00:53.100 --> 00:00:54.940
The autofill menu appears.
00:00:54.949 --> 00:00:57.739
Make sure you are using the absolute value
formula.
00:00:57.740 --> 00:01:04.120
Then, close the parentheses at the very end of the formula so that they enclose the entire PMT function.
00:01:04.120 --> 00:01:05.320
Press Enter.
00:01:05.339 --> 00:01:07.670
The number appears as an absolute value.
00:01:07.670 --> 00:01:10.720
Drag the formula down to copy the revised
function.
00:01:11.940 --> 00:01:16.960
The monthly payment is now displayed as a positive number, but each has many decimal points.
00:01:16.960 --> 00:01:21.420
To reformat these cells to reflect currency,
highlight the cells in the monthly payment
00:01:21.431 --> 00:01:22.679
column of your table.
00:01:22.680 --> 00:01:25.580
Then, select “Format” and “Currency.”
00:01:28.980 --> 00:01:31.200
All of the numbers appear as dollars and cents.
00:01:31.200 --> 00:01:34.600
Format the “Loan Amount” column of your
table the same way.
00:01:38.380 --> 00:01:39.399
Good job!
00:01:39.399 --> 00:01:42.310
Compare some of the values in the spreadsheet
you created.
00:01:42.310 --> 00:01:48.320
A 3-year loan for one thousand dollars at a 4 percent APR would cost about 30 dollars a month.
00:01:48.320 --> 00:01:53.720
If you borrowed 5 thousand dollars, it would
cost you close to 150 dollars a month.
00:01:53.720 --> 00:01:59.039
And a loan of 10 thousand dollars would cost
you nearly 300 dollars a month.
00:01:59.039 --> 00:02:03.170
A spreadsheet like this can be used in conjunction
with a monthly budget like you created in
00:02:03.170 --> 00:02:04.180
the last activity.
00:02:04.180 --> 00:02:08.580
You could create a monthly budget to determine
what monthly payment you could afford.
00:02:08.590 --> 00:02:13.540
Then, you could create the loan amounts table
to compare total loan and interest costs to
00:02:13.540 --> 00:02:15.530
decide which loan is best for you.
00:02:15.530 --> 00:02:16.840
Now, it’s your turn
00:02:16.840 --> 00:02:22.840
Insert an absolute value formula within the PMT function to return all positive values.
00:02:22.840 --> 00:02:27.560
Change the cell format in the “Loan Amount”
and “Monthly Payments” columns to “Currency.”
00:02:27.560 --> 00:02:30.320
Compare the monthly payments for several loan
amounts.
00:02:30.320 --> 00:02:33.580
Consider which monthly payment you could reasonably
afford.
00:02:33.580 --> 00:02:39.040
Then, move on to the next video to calculate how much interest you would pay with each loan amount.