Auto Loan Calculator

Maybe, someone could parlay their car buying experience with me. My
mother is in the market for a new car. We went to the dealership to
browse cars and the salesman begins his routine. We were in the
initial stages of the car buying process (she wanted to see if she
looked good in the car. Really!) The salesman put us on the fast
track. He even offers to approach the sales manager for incentives.
The car in question is in extreme demand and the dealers only sell
them at MSRP. In addition, the cars are often pre-sold with the option
she wants. A deposit is in order to reserve a car, but I told her not
to place the deposit down until she completes the negotiation. So the
only place we can save money is in the financing package. Now, we have
to move fast and approach the negotiation in a knowledge matter.
Here's where I need help.

I would like to create an Excel spreadsheet with various loan
calculators. Web based loan calculator do not allow me to bring my
laptop into the showroom and reconfigure the numbers as the salesman
brings new deals to the table. The challenge become creates formulas
for the following scenarios.

The Salesmen goes to the Sales Manager and return with the following
offer.

Part I
$535 per month
60 month loan term
5.95% Interest

How much interest are you paying and what is the total price of the
car? You can not figure out the total cost of the car without knowing
the interest you paid. With the interest, you can subtract this amount
from the total cost and arrive at the dealer's true offer (price of
the car). I want to show the salesman what the total cost equates. So
he proceeds cautiously with the next offer.

Part II
You have good credit and your interest lowers to 4.95%.
How much do you save in interest expense? Total outlay? Car's price?

Part III
How much does your monthly payment decrease for every $1000 you put
down?

I have already figured out the monthly payment part, which is easy.
Plus, have reviewed the following formulas; PMT, PV, RATE, and NPER.
These salesmen are trained to confuse you, by changing elements of the
deal; adding fees; dealer fees; miscalculating the tax (caught them on
my last car with the tactic). Excel could help me create various
worksheets to anticipate these scenarios. Let me know if you have any
suggestions and examples.

"How much interest are you paying and what is the total price of the
car? You can not figure out the total cost of the car without knowing
the interest you paid. With the interest, you can subtract this amount
from the total cost and arrive at the dealer's true offer (price of
the car)."

If you are buying, not leasing, then why would you not know the price of the car?
You (at least I) negotiate the price 1st. The financing is just that, financing i.e. how
you're going to pay for it.

If the "price" is, as you say, the MSRP, then you know the "cost". On a personal note, I
would never pay that amount - market value is what one person is willing to pay and what
the other is willing to sell for).

The loan "$535 per month 60 month" = 32,100. Subtract the capital cost of the car (MSRP)
plus added fee's and that's the interest cost.

I'm obviously missing something here!

--
Regards;
Rob
------------------------------------------------------------------------

Robert,

You might want to take a look at my "Calculate Payments"
Excel add-in. Given any 3 of...
Loan Amount, Term, Payment, Interest Rate it give you the 4th item.

Very easy to use, displays a box with all parameters and
can transfer that info to the spreadsheet with a click.
(It can also calculate the payment required to meet
a savings goal).

Available - free - upon direct request.
Remove xxx from my email address.

Regards,
Jim Cone
San Francisco, USA
[email protected]XX

You can quickly compare costs for
"Robert" <robert-neville310@[email protected]> wrote in message news:[email protected]...
> Maybe, someone could parlay their car buying experience with me. My
> mother is in the market for a new car. We went to the dealership to
> browse cars and the salesman begins his routine. We were in the
> initial stages of the car buying process (she wanted to see if she
> looked good in the car. Really!) The salesman put us on the fast
> track. He even offers to approach the sales manager for incentives.
> The car in question is in extreme demand and the dealers only sell
> them at MSRP. In addition, the cars are often pre-sold with the option
> she wants. A deposit is in order to reserve a car, but I told her not
> to place the deposit down until she completes the negotiation. So the
> only place we can save money is in the financing package. Now, we have
> to move fast and approach the negotiation in a knowledge matter.
> Here's where I need help.
> I would like to create an Excel spreadsheet with various loan
> calculators. Web based loan calculator do not allow me to bring my
> laptop into the showroom and reconfigure the numbers as the salesman
> brings new deals to the table. The challenge become creates formulas
> for the following scenarios.
> The Salesmen goes to the Sales Manager and return with the following
> offer.
> Part I
> $535 per month
> 60 month loan term
> 5.95% Interest
> How much interest are you paying and what is the total price of the
> car? You can not figure out the total cost of the car without knowing
> the interest you paid. With the interest, you can subtract this amount
> from the total cost and arrive at the dealer's true offer (price of
> the car). I want to show the salesman what the total cost equates. So
> he proceeds cautiously with the next offer.
> Part II
> You have good credit and your interest lowers to 4.95%.
> How much do you save in interest expense? Total outlay? Car's price?
> Part III
> How much does your monthly payment decrease for every $1000 you put
> down?
> I have already figured out the monthly payment part, which is easy.
> Plus, have reviewed the following formulas; PMT, PV, RATE, and NPER.
> These salesmen are trained to confuse you, by changing elements of the
> deal; adding fees; dealer fees; miscalculating the tax (caught them on
> my last car with the tactic). Excel could help me create various
> worksheets to anticipate these scenarios. Let me know if you have any
> suggestions and examples.

MS has lots of templates at:
http://officeupdate.microsoft.com/TemplateGallery

And I'd visit Cars.com or edmunds.com
to get more info.

You may want to search google groups to find out if the car you want has a
dedicated newsgroup (like alt.autos.toyota) that you could post some questions.



Robert wrote:
>
> Maybe, someone could parlay their car buying experience with me. My
> mother is in the market for a new car. We went to the dealership to
> browse cars and the salesman begins his routine. We were in the
> initial stages of the car buying process (she wanted to see if she
> looked good in the car. Really!) The salesman put us on the fast
> track. He even offers to approach the sales manager for incentives.
> The car in question is in extreme demand and the dealers only sell
> them at MSRP. In addition, the cars are often pre-sold with the option
> she wants. A deposit is in order to reserve a car, but I told her not
> to place the deposit down until she completes the negotiation. So the
> only place we can save money is in the financing package. Now, we have
> to move fast and approach the negotiation in a knowledge matter.
> Here's where I need help.
>
> I would like to create an Excel spreadsheet with various loan
> calculators. Web based loan calculator do not allow me to bring my
> laptop into the showroom and reconfigure the numbers as the salesman
> brings new deals to the table. The challenge become creates formulas
> for the following scenarios.
>
> The Salesmen goes to the Sales Manager and return with the following
> offer.
>
> Part I
> $535 per month
> 60 month loan term
> 5.95% Interest
>
> How much interest are you paying and what is the total price of the
> car? You can not figure out the total cost of the car without knowing
> the interest you paid. With the interest, you can subtract this amount
> from the total cost and arrive at the dealer's true offer (price of
> the car). I want to show the salesman what the total cost equates. So
> he proceeds cautiously with the next offer.
>
> Part II
> You have good credit and your interest lowers to 4.95%.
> How much do you save in interest expense? Total outlay? Car's price?
>
> Part III
> How much does your monthly payment decrease for every $1000 you put
> down?
>
> I have already figured out the monthly payment part, which is easy.
> Plus, have reviewed the following formulas; PMT, PV, RATE, and NPER.
> These salesmen are trained to confuse you, by changing elements of the
> deal; adding fees; dealer fees; miscalculating the tax (caught them on
> my last car with the tactic). Excel could help me create various
> worksheets to anticipate these scenarios. Let me know if you have any
> suggestions and examples.

--

Dave Peterson

Robert wrote:
> Maybe, someone could parlay their car buying experience with me. My
> mother is in the market for a new car. We went to the dealership to
> browse cars and the salesman begins his routine. We were in the
> initial stages of the car buying process (she wanted to see if she
> looked good in the car. Really!) The salesman put us on the fast
> track. He even offers to approach the sales manager for incentives.
> The car in question is in extreme demand and the dealers only sell
> them at MSRP. In addition, the cars are often pre-sold with the option
> she wants. A deposit is in order to reserve a car, but I told her not
> to place the deposit down until she completes the negotiation. So the
> only place we can save money is in the financing package. Now, we have
> to move fast and approach the negotiation in a knowledge matter.
> Here's where I need help.
>
> I would like to create an Excel spreadsheet with various loan
> calculators. Web based loan calculator do not allow me to bring my
> laptop into the showroom and reconfigure the numbers as the salesman
> brings new deals to the table. The challenge become creates formulas
> for the following scenarios.
>
> The Salesmen goes to the Sales Manager and return with the following
> offer.
>
> Part I
> $535 per month
> 60 month loan term
> 5.95% Interest
>
> How much interest are you paying and what is the total price of the
> car? You can not figure out the total cost of the car without knowing
> the interest you paid. With the interest, you can subtract this amount
> from the total cost and arrive at the dealer's true offer (price of
> the car). I want to show the salesman what the total cost equates. So
> he proceeds cautiously with the next offer.
>
> Part II
> You have good credit and your interest lowers to 4.95%.
> How much do you save in interest expense? Total outlay? Car's price?
>
> Part III
> How much does your monthly payment decrease for every $1000 you put
> down?
>
> I have already figured out the monthly payment part, which is easy.
> Plus, have reviewed the following formulas; PMT, PV, RATE, and NPER.
> These salesmen are trained to confuse you, by changing elements of the
> deal; adding fees; dealer fees; miscalculating the tax (caught them on
> my last car with the tactic). Excel could help me create various
> worksheets to anticipate these scenarios. Let me know if you have any
> suggestions and examples.
>
>
>
As salesmans nightmare!!!!

Try this:

Deal 1
Principal amount interest (monthly)
term (in months)

Loan Payments [use the payment function]
FV [use the Future Value Function]

Set up a table for a second deal exactly the same. If you deduct FV
Deal 1 From FV Deal 2, you will get the difference in interest payments
between the two.

BTW, you can use PPMT function to give a monthly principal amount and
IMPT to give a monthly interest amount.

Just out of interest, what college course is this for? :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip>