Buying A Car

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