210 – payment function buying a car – the abc’s so you want to buy a car! we must first look...
TRANSCRIPT
Buying a Car – The ABC’s
So you want to buy a car!
We must first look at all the variables!
Car Price,Down Payment,
Interest Rate, Principal balance
Loan Term
I know what the car price is, but what is a down
payment?
In advertisements I hear them say
things like “Great Deals with 0%
Down”
Buying a Car – The ABC’sA down payment is the amount you are
willing to pay up front! You must
give the dealership $ before you drive
home
The rest of the car price you will ask
the bank for a loan!
In today’s economy you won’t find too many ads for 0% down payment.
For our example today we are going to ask the
car buyer for 10% of the Car Price for the
down Payment
I sometimes hear on ads the word
APR and then some numbers with a percent.
Ok, now what is the Interest Rate
Buying a Car – The ABC’sInterest is the
amount you are going to pay the
bank for your loan.
You borrow money and the
bank wants it back plus interest.
There are all kinds of loans, but we are
going to look at a simple fixed rate
loan.
APR stands for Annual Percentage Rate. The interest
rate We need to remember that
APR is the annual rate
Buying a Car – The ABC’sThis is important to remember because
we will need to divide by 12 to get to a monthly rate
Is that because we pay for a car on a
monthly basis?
Exactly!!
Buying a Car – The ABC’sThat is a banking
term that represents the
amount of money borrowed from the
bankSo what is the
Principal Balance?
So you take the car price and subtract
the down payment?
Exactly!
Buying a Car – The ABC’sNow let’s move on to loan term – how long are we going
to pay for this loan!I have heard that cars are so
expensive that people borrow
money for 4, 5, or 6 years!
Do I pay for my car once a year or
monthly!
Monthly is typical!
Buying a Car – The ABC’sSo you will take the
number of years and multiply by 12
to get the loan term!You told us that
we would use a new Excel
Function called PMT – Payment
Function
Why?
Yes and it is a bit
different than sum, max, min, average
Buying a Car – The ABC’sIt has many
variables (arguments)! All the ones we have
been talking about!
What answer will it give us?
So let’s buy a car!
It will calculate the monthly
payment so that at the end you will have paid
back all the money
borrowed plus interest
Buying a Car – The ABC’sWe will set up an Excel Spreadsheet that looks like the
following one! How much do you
want to spend?I want to buy a car
for $16,000 and pay it off in 5 years
Ok, we will give you an APR of 7.5%
One more thing about the PMT
function – it returns a negative number!
210 - Payment Function - Buying a Car
Scenarios
5 Years
Car Price $16,000.00
Down Payment 10% of Car price $1,600.00 =B6*10%
Principal or Borrowed Amount $14,400.00 =B6-B7
Interest Rate (APR) 7.50%
Loan Term in months 60
Monthly Payment ($288.55)=PMT(B9/12,B10,B8,0)
Total Payment ($17,312.79) =B12*B10
Total Interest ($2,912.79)
Buying a Car – The ABC’sThe spreadsheet
shows that the PMT calculation is a
monthly payment of -$288.55So if I pay that
amount monthly for 5 years, I end
up giving the bank $17,312.79
Then I paid $2,912.79 in
interest to the bank
Yes, so the cost of the loan is the
difference between what you borrowed and what you
paid them with your monthly
payment
Right Again
Payment Function ReviewLet’s review the PMT function
arguments
You start with =pmt(
=pmt(rate/12, nper
What is nper?
Rate is the first argument and it
is the Interest rate / 12
That makes the APR rate a
monthly figure.
Nper is number of periods to pay – how
many months
Payment Function ReviewThe next argument is PV which stands for Present Value.
We used principal or borrowed amount for
Present value
=pmt(rate/12, nper, pv, fv)
What is fv?
Yes, because this is the present
value of the loan balance – it is the starting balance
for the PMT function.
FV stands for Future Value.After paying
all those years you
owe the bank 0 –zero..
Payment Function ReviewSince the future value is almost
always zero, you don’t have to use it.
You mean I could leave it as
=pmt(rate/12,nper,pv)
Do I have to memorize this for
the Test?
Yes and the final argument called
type is also optional. That
argument will be saved for
business majors!!
Absolutely!