APRIL 2020 PCWorld 135the function followed by the interest rate
divided by 12 (for 12 months), the Term (or
number of monthly payments), followed
by the Loan Amount.
Enter this formula in cell F2:
=PMT(D2/12,E2,C2,0,0), then copy
from F2 down to F3 through F6.
To calculate a down payment, enter
the down payment percentage in
column F; enter the formula in column G;
enter the adjusted loan amount formula
in column H; and the new monthly
payment formula in column I. Also,
change the Term (in months) to Term (in
years) in column E—for example,
instead of entering 360 months for a
House Loan, enter 30 years.
Enter the following formula in cell
G10: =SUM(C10*F10). Enter this
formula in cell H10: =SUM(C10-G10).
And enter this formula in cell I10:
=PMT(D10/12, E10*12,H10,0,0).
Note that the Interest Rate is still divided
by 12, and that the Term
(in years) is multiplied
by 12.
Handy tip: Have
you ever wondered why
some currency formats
center the dollar amount
and the dollar sign
($1500.00), while others
push the numbers
aligned to the right and
push the dollar sign aligned to the left ($
1500.00)? To center the number and the
dollar sign, highlight the cell, and choose
Home Tab > Number Group > Number
Format > Currency. To align the dollar sign
left and the numbers right, highlight the
cell, then click the $ symbol in the
Number group.
We hope that knowing how to use these
formulas will help you better use Excel.Use the PMT function to calculate your auto payments for that new car.