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.



0
y1097 (12)
1/6/2005 9:17:16 PM
excel 39879 articles. 2 followers. Follow

5 Replies
415 Views

Similar Articles

[PageSpeed] 34

"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
------------------------------------------------------------------------


0
NotLikely (282)
1/6/2005 9:42:09 PM
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
jim.coneXXX@rcn.comXXX

You can quickly compare costs for 
"Robert" <robert-neville310@y@ho0.com> wrote in message news:fjart092gndkm23anleacii8932nmjijpd@4ax.com...
> 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.

0
jim.coneXXX (771)
1/6/2005 10:46:18 PM
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
0
ec357201 (5290)
1/6/2005 11:49:48 PM
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. 			
0
itguru (22)
1/6/2005 11:51:38 PM
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>


0
ken.wright (2489)
1/10/2005 7:20:31 PM
Reply:

Similar Artilces:

Task Start and End Date Calculations with Part Time Resources
I have a project for a client that has a strict budget. The budget was based on buckets of time assigned to high-level tasks. These buckets (and dollars) are not based on calendar duration but actual billable hours. I have to divide the hours across lower-level tasks, and my goal is to drive out what my project milestone dates and final end-date might be based on the task work estimates and my resource availability. I have resources who are assigned to my project as well as other projects. Their availability to my project varies -- some are 80% (32 hrs/week), some are 60% (24 hrs/w...

Calculating Percentage in Query.
I have a query with the fields Quantity (Count of ID's), Product, company. My issue is to calculate the percentage for each company in a query When I put Quantity/Sum(Quantity)) I am getting subquery warning. How can I accomlish this in a query. That is , I would like to get Quantity, Percenage: Qty/Sum(Qty), Company. Any help really appreciated. Thank you Create a totals query to get your SUMs for the equation. Join the query with your table in that output query to get percent. -- KARL DEWEY Build a little - Test a little "Lin" wrote: > I have a query with the ...

How do I keep my excel files in Excel 2003, not auto 2007
I loaded a trial version of excel 2007. I want to continue using 2003 instead of buying the full version, but my old spreadsheets keep automatically changing to 2007. Delete the trial version. Control Panel > Unistall Programs > Uninstall XL2007 Trial Version. Regards, Alan. "DorothyL" <DorothyL@discussions.microsoft.com> wrote in message news:C2722C89-5C3A-4F1D-814C-585E8D49DC5D@microsoft.com... >I loaded a trial version of excel 2007. I want to continue using 2003 > instead of buying the full version, but my old spreadsheets keep > automatic...

Auto Fill?
I want to list dates in sequence by week day of Saturday. Where is the option Autofill? How about an alternative. Put your first Saturday in the topmost cell (say A1) then put =a1+7 in A2 and drag down (or fill down) "W. Wells" wrote: > > I want to list dates in sequence by week day of Saturday. Where is the > option Autofill? -- Dave Peterson Using Autofill to repeat a specific sequence requires you to identify the sequence in the first two cells. Enter the first Saturday date in the first cell. If you were using August 2006, you would enter a 5. Enter the se...

calculations not right....
in my report i have Qty, UP2, and NetAmount. NetAmount =[Qty]*[UP2] my [Qty] is = 5 my [UP2] is = 170.86 now when my NetAmount comes up is says 854.32, when it should be 854.30....... why is this? I have [UP2] format set to $#,##0.00;($#,##0.00); with decimal places set to 2. NetAmount =Nz([Qty],0)*Nz([UP2],0) Currency, Decimal set to 2..... I cant figure this out... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200711/1 What "format" you are using has little/nothing to do with how the data is stored. What are the unde...

Auto Fill button
Does anyone out there know how to program the autofill button to be used for different applications. For instance, it is currently programmed to be used with shipping carriers to fill out default customer and shipping info. I would like to use it to fill out customer info into a booking program online. Any ideas? ...

Auto copy for outgoing email #2
Hi Everybody, Hope someone can give me a idea. Server: Microsoft Exchange Server 2000. Client: Microsoft Outlook 2003 I would like to setup auto copy outgoing email of selected mailbox to another mailbox? For example, all emails sent by user A, the email be delivered to user B automatically. But the user A does not know that the email is copied to user B. Thank you for your attention in advance. Thomas S. Message journaling by putting your users in dedicated store http://www.msexchange.org/tutorials/MF011.html SelectiveJournaling for MS Exchange 2000/2003 http://www.ivas...

Calculating age #3
How to calculate ages of employees for benefits enrollment given the date of birth? -- Joe S. Hi see your other post P.S.: please don't multipost -- Regards Frank Kabel Frankfurt, Germany "Joe S." <JoeS@discussions.microsoft.com> schrieb im Newsbeitrag news:1554235A-B831-4704-A96B-0652588C1E44@microsoft.com... > How to calculate ages of employees for benefits enrollment given the date of > birth? > -- > Joe S. Joe, Use the DATEDIF function. E.g., =DATEDIF(birthdate,TODAY(),"Y") For more information on DATEDIF, see www.cpearson.com/excel/date...

Form Field Auto Resizing
I've created a form based on a table that has mostly Memo fields. I'd like to cut and paste varying length text into different form fields (that are Memos) and have the field on the form "Auto Resize" to the length of the text. Is that possible? You help is greatly appreciated! (B^>)-]=[ ...

Calculating length of time with conditions
I would like to calculate the length of time a client spends in a program. We use three columns (entry date, exit date, today's date). If a client is still enrolled I would like to base the length of time on today's date. If the client leaves the program, I would like to base the length of time on the exit date. Preference is to display the length of time in months. Thank you. You want Datedif, as in: =Datedif(entry date, if(exit date = "",today(),exit date),"m") Regards, Fred "markg" <markg@discussions.microsoft.com> wrote in ...

How do I get a value of a calculated point on an excel line chart?
I created a line chart in Excel and I wanted to find out if there was a way to find out the value on any particular point on the line (not one of the values I entered to create the line, but one in between)? On a Line or an XY chart? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "klc6778" <klc6778@discussions.microsoft.com> wrote in message news:FA711FAD-0AC7-4644-B987-0AD179CEF776@microsoft.com... >I created a line chart in Excel and I wanted to find out if there was a way > to find out the value on any particular point on the line (not on...

Calculated Fields showing error results
I am trying to get a calculated field to work within a pivot table. The two fields in the pivot table which I'm referencing are sales and Profit dollars. I want the calculated field to show gross profit dollars (percent to total sales represented by profit), but without any error values (!value, div/0, etc.) where there are no sales for a particular line (i.e. customer) in the timeframe I'm analyzing. The formulae I've tried are: =If(iserror(Profit/Sales),"",(Profit/Sales)) =if(isna(Profit/Sales),"",(Profit/Sales)) =if(isnumber(Profit/Sales),(Profit/Sal...

Auto E-mail
Good Day i would like to make an automatic e-mail that will send to a new user that i make for example if i make a user it must send an e-mail that i have earlear created telling the new user from the company and all policies that the company have can anyone help -- Juan Bredenkamp exchange does not have built in functionality to do this. you can create a script that will both create the user and send the email. "Juan" <juanb@blick.co.za> wrote in message news:0093C5BC-DB55-4253-9427-90AE45EED8A9@microsoft.com... > Good Day > > i would like to make an au...

Auto Renumbering in Excel
-- Randy ...

Different Calculations in Pivot Table Subtotal Rows
Hello, I have a pivot table containing sales data in which the products are in row headers and each month's net sales and cost of goods sold (cogs) are the column headers. eg. January 05 January 04 Product Net Sales CoGS Net Sales CoGS 101 $5000 $2000 $4000 $1800 .. .. .. etc. I have subtotals after every set of products in every product group that simply add the Net Sales and CoGS data. I want to make a third column heading named "Margin %" for each month which is equal to (Net Sales - CoGS)/Net Sales....

Auto Fill fields in table
I am creating a database for my small consultancy - around 30 clients. In addition to the table containing names, addresses, etc. I want a table to record invoices raised - dates, amounts, date paid, etc. I'm assuming i make the Client ID field the primary key in the contacts table. How do I use this to auto-complete the client firstname and surname into the invoice table? In other words - I want to just be able to enter the client ID into the invoice table and have it enter the firstname and surname automatically. Thanks in advance. Steve On Mon, 28 May 2007 11:17:30 GMT, &q...

COP AUTO-CREATE RETURN
When we use create return through extras and open Invoice Document Lookup no invoices are listed. In our other company the feature works fine. I logged in as sa and that did not help. This is a newer company, is there a setup procedure that needs to be completed? -- Thanks for your help Jayne The only way I could replicate that behavior was by not selecting a customer first from the Create Returns window. -- Charles Allen, MVP "shoedog" wrote: > When we use create return through extras and open Invoice Document Lookup no > invoices are listed. In our other co...

Calculating with named ranges
if I have a range named "Quantities" in cells C3:O3 and a range named "UnitPrice" in cells C5:O5, can I enter formulas in each of cells C7:O7 that use the range names to perform the multiplications of cells C3 * C5, D3 * D5, etc ? Hi KG in C7 the formula would be =Quantities*UnitPrice and then just drag it across to O7. Cheers JulieD "KG" wrote: > if I have a range named "Quantities" in cells C3:O3 and a range named > "UnitPrice" in cells C5:O5, can I enter formulas in each of cells C7:O7 that > use the range names to perfo...

Auto Formatting an Excel Spreadsheet
I have an automated process (batch file) that is creating a text file (it can't automatically generate an Excel file) from a database each night and saving it on a network drive with an .xls extension. I then have an intranet page (HTML) that is looking for that Excel file each day to present up-to-date information from the database. However, the formatting (e.g., column widths, colors) is overwritten each night when the file is regenerated. I know the column widths, colors, etc., that I want to apply to the spreadsheet. Is there a way in HTML, JavaScript, etc., that I can automat...

Calculating intertest
Is there a formula to caluculate interest easly. I have different deposit dates Different dates that interest rates changed mostly in the middle of a month never on the same day of a month. I must calculate interest and interest must be added to the deposit after a year. Thanks Do you mean simple interest or compound interest? - and is the firs year interest free? - or is interest to be added to the account 1 months after it is calculated, or is the interest for a yea accumulated and added to the due amount at the end of a year? If the interest is 'Simple Interest', is there a...

how to stop excel from auto formatting whole number to decimal %
how do i stop excel from auto formatting whole numbers into decimals? example: input 3 --> excel would show 0.03 Try this: From the Excel main menu: <tools><options><edit tab> UNcheck: Fixed decimal places Does that help? *********** Regards, Ron XL2002, WinXP "samo" wrote: > how do i stop excel from auto formatting whole numbers into decimals? > > example: > > input 3 --> excel would show 0.03 ron, you are amazing! thank you soooooo much. "Ron Coderre" wrote: > Try this: > > From the Excel main menu: > <...

Auto Reply
How do I put an "Out of Office" auto reply, when I am away from the office? If you do this, you will have to leave your computer on 24/7, be connected to the Internet, and have your e-mail program open. If you want do this anyway, the link below will explain how to do it. One thing I would suggest is that where it says to create a rule and check For All Messages, you change it and make the rule for people in your address book only. Otherwise you will be replying to Spam as well and could end up in an endless loop of bounces. Your ISP may offer this service which woul...

auto fill question
hello all- i'm trying to use the auto-fill function to populate a column with a 'countif' function. although the formula is counting the number of occurrences of 'x' in a given column, i want to autofill vertically while the reference columns run horizontally. must the autofill function operate in the same direction as the reference data? is there a way around this? by way of a generic example: =COUNTIF('widgets'!L:L,"x") so, when i pull down the autofill handle, the formula should change incrementally to: =COUNTIF('widgets'!M:M,"x")...

Calculating Hours and Minutes in Excel
I am doing payroll hours in an excel spreadsheet. Is there a way to enter the time in and time out and have a formula calculate how much time it comes to. For example, I have an employee that started at 8:55am and left at 10:40am. Is there a formula that calculates how much time that comes too? Thanks very much, Janet Hi Janet If A1 stores the time the employee started working and B1 the check-out time simply subtract both values: =B1-A1 If you want to use this number to calcualte his wage, multplky the result with 24: =(B1-A1)*24*hourly rate for more information have a look at http...

PDF icon in calculated cells
I have a calculated column and a PDF icon has appeared. If I delete the contents of the cells, the PDF icon remains. How can I delete the PDF icon? Right click on the icon and select cut from the popup menu. -- Regards, Tom Ogilvy "Texas Bald Eagle" <Texas Bald Eagle@discussions.microsoft.com> wrote in message news:BEBE1E3D-762B-43DE-8A60-99A86F8A8A20@microsoft.com... > I have a calculated column and a PDF icon has appeared. If I delete the > contents of the cells, the PDF icon remains. How can I delete the PDF icon? ...