Spreadsheet Calculator 46218 | Auto Loan Calculator

Partial file snippet.

Sheet 1: AutoLoanCalculator

	
	
	
	
	
	
	
	
	
		Auto Loan Calculator
		

		

		

		
 
		
		

		Using This Worksheet
	
	
		http://www.vertex42.com/Calculators/auto-loan-calculator.html
		

		

		

		© 2007-2022 Vertex42 LLC
		

		This worksheet provides a break-down of the sales cost, charges, fees, and taxes associated with buying a car from a dealer. Many of the values will need to be estimated, and you may need to consult the policies of your state to determine how sales tax is applied and what the registration and other fees are.
	
	
		

		

		

		

		

		

		

		
	
		

		Purchase Price (before tax)
		

		

		

		

		

		
	
		

		

		
		Sale Price
Include the sale price of the new car plus any additional options. Do not include the sales tax.
		Sale Price + Options
		 $20,000 
		

		

		

		
	
		

		

		
		Destination Charge:
The cost that the automaker charges to deliver the auto to the dealer. This amount is usually a fixed charge and is not negotiable. It varies depending on the car.
		Destination charge
		 $-   
		 Set to 0 if included in sale price
		

		

		
	
		

		

		
		Title Transfer Fee:
The title transfer fee is payable by the new owner to the state's DMV. This fee is usually taxable.
		Title Transfer Fee
		 $-   
		e.g. $10
		

		

		
	
		

		

		
		Taxable Fees:
Fees that are subject to State Sales Tax, such as fees that are due at delivery.
		Other Taxable Fees
		 $-   
		

		

		

		
	
		

		

		Purchase Price
		 $20,000 
		

		

		

		
	
		

		

		

		

		

		

		

		
	
		

		State Sales Tax
		

		

		

		

		

		

	
	
		

		

		
		Check Your State:
In some states, the value of the trade-in is not tax deductible. If you choose TRUE, then the trade-in value is subtracted from the sale price BEFORE calculating the taxes.

At the time of creation of this calculator, the following states DO NOT allow the trade-in value to be deducted:
California, District of Columbia, Hawaii, Maryland, Michigan
		Trade-In is Tax Deductible
		 $-   
		 If you don't know, set to FALSE
		

		

		At the bottom of the Download page at Vertex42.com you will find numerous links to online calculators and resources that may help you complete this worksheet.
	
	
		

		

		
		Check Your State:
In some states, the manufacturer's Cash Rebate can be substracted from the sale price before the sales tax is calculated.
		Cash Rebate is Tax Deductible
		 $-   
		 If you don't know, set to FALSE
		

		

		
	
		

		

		State Sales Tax Rate
		6.25%
		e.g. 6.25%
		

		

		
	
		

		

		Net Taxable
		 $20,000 
		

		

		

		
	
		

		

		State Sales (Excise) Tax
		 $1,250.00 
		

		

		

		

	
	
		

		

		

		

		

		

		

		To make comparisons, you can make multiple copies of Column D (select column D and press Ctrl+c, then right-click on column E and select Insert Copied Cells)

	
	
		

		Non-Taxable Fees
		

		

		

		

		

		
	
		

		

		
		Registration Fee:
The registration fee usually varies depending on the vehicle type, fuel type, county, and other factors. Call your DMV to find out the registration cost.
		Registration
		 $40 
		e.g. $40 to $100
		

		

		
	
		

		

		
		Late Registration:
Many states due not have a grace period after registration has expired, so make sue you get the titled transferred and the registration fee paid right away.
		Late Registration Fee
		 $-   
		e.g. $10 per month
		

		

		
	
		

		

		
		Service Contract:
Very likely, the dealer will try to get you to purchase a service contract. This can be a very significant cost, so don't neglect considering it in your calculation.
		Service Contract
		 $-   
		e.g. $250 to $1000
		

		

		
	
		

		

		Special Plate Fee
		 $-   
		e.g. $25
		

		

		

	
	
		

		

		Administration Fee
		 $3 
		e.g. $3
		

		

		

	
	
		

		

		Transaction Fee
		 $3 
		e.g. $3
		

		

		

	
	
		

		

		
		Late Title Transfer Fee:
In many states, you have a limited amount of time (often 30 days) to transfer the title of the new car. If you don't meet the deadline, you end up paying late fees. Don't be late! The fees can add up quickly.
e.g. $25 to $200
		Late Title Transfer Fee
		 $-   
		e.g. $25 to $200
		

		

		

	
	
		

		

		Duplicate Title Fee
		 $-   
		e.g. $25
		

		

		

	
	
		

		

		
		Non-Taxable Fees
Fees that are NOT subject to sales tax, such as document fees or fees due at delivery.
		Other Non-Taxable Fees
		 $-   
		

		

		

		

	
	
		

		

		Total Non-Taxable Fees
		 $46 
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		Loan Amount
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		
		Unpaid Balance on Previous Loan:
If you have an unpaid balance on a loan for a car that you are trading in, enter the unpaid loan balance here.
		Unpaid Loan Balance on Trade-In
		 $-   
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		
		Down Payment:
The amount you pay in cash (or by check). 
		Less Down Payment
		 $-   
		

		

		

		

	
	
		

		

		
		Trade-In:
If you are trading in an older car, enter the value accepted by the dealer here. In some states, the trade-in value can be deducted from the purchase price prior to calculating the sales tax.
		Less Value of Trade-In
		 $-   
		

		

		

		

	
	
		

		

		
		Manufacturere's Cash Rebate:
Sometimes, the manufacturer will provide a cash rebate or low-interest financing. In some states, the cash rebate can be deducted from the purchase price before calculating the sales tax.
		Less Cash Rebate
		 $-   
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		Total Loan Amount
		 $21,296 
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		[42]
		

		

		

		

		

		

		

	



Sheet 2: PaymentCalculator

	
	
	
	
	
	
	
	
	
		Auto Loan Payment Calculator
		

		

		

		

		
 
		
		

		Using This Worksheet
	
	
		http://www.vertex42.com/Calculators/auto-loan-calculator.html
		

		

		

		

		© 2007-2022 Vertex42 LLC
		

		This spreadsheet creates a payment schedule for a fixed-rate auto loan, with optional extra payments. Use the spreadsheet to compare different terms, rates, and loan amounts. The spreadsheet allows complete flexibility in how you make additional payments.

	
	
		

		

		

		

		

		

		

		

		

		
	
		

		Inputs
		

		

		

		Effect of Extra Payments
		

		

		

		
	
		

		

		
		Loan Amount:
This is the amount that you have borrowed.
It is NOT the price of the automobile. It is the amount financed, which often consists of the price of the auto + fees + sales tax - down payment.
		Auto Loan Amount
		 $20,000 
		

		Total Payments
		 $22,728.62 
		

		

		
	
		

		

		Annual Interest Rate
		8.50%
		

		Total Interest
		 $2,728.62 
		

		

		
	
		

		

		
		Term of Loan
Auto loans are usually between 1 and 6 years.

If you want to specify a specific number of months, enter the formula:  =months/12

For example:
For a 6-month loan, enter =6/12
For a 18-month loan, enter =18/12
		Term of Loan in Years
		3
		

		
		Reduced Interest
The reduced interest expense associated with making extra payments. The result may be off by a few cents due to rounding. When you make extra payments on the principal above your normal payment, then you pay less interest in the long run.
		Reduced Interest
		None
		

		

		
	
		

		

		
		First Payment Date
Assumes that the first payment date is at the end of the first period.
Shortcut: To enter today's date, press Ctrl+;
		First Payment Date
		1/1/2007
		

		Number of Payments
		36
		

		

		
	
		

		

		Frequency of Payment
		Monthly
		

		Last Payment Date
		12/1/2009
		

		

		
	
		

		

		

		

		
 
		
		

		

		Use the AutoLoanCalculator worksheet to figure out the Auto Loan Amount.
	
	
		

		Summary (with no extra payments)
		

		

		

		

		
	
		

		

		Number of Payments
		36
		

		

		

	
	
		

		

		Rate (per period)
		0.708%
		

		

		The payment is rounded to the nearest cent. The last payment is adjusted to bring the balance to zero.

	
	
		

		

		Payment (per period)
		$631.35 
		

		

		
	
		

		

		
		Total Interest:
If you don't make any extra payments, this will be the total amount of interest paid over the life of the loan.
		Total Interest
		$2,728.63 
		

		

		
	
		

		

		
		Total Payments:
If you don't make any extra payments, this will be the total amount paid over the life of the loan (including interest). The amount may be off by a few cents due to rounding.
		Total Payments
		$22,728.63 
		

		

		

	
	
		

		

		

		.
		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		No.
		Due Date
		Payment Due
		
		Additional Payment
The amount paid directly towards the principal. In order to pay off the remaining balance, the additional payment must be the last period balance - payment due + interest due. For example, to pay off after the first period, the additional payment would be: =G24-C25+E25

(Assumes no penalties for making additional payments.)
		Additional Payment
		Interest
		Principal
		Balance
		

		

		

	
	
		

		

		

		

		

		

		$20,000.00 
		

		

		

	
	
		1
		1/1/2007
		631.35
		0.00
		141.67
		489.68
		19,510.32
		

		

		

	
	
		2
		2/1/2007
		631.35
		0.00
		138.20
		493.15
		19,017.17
		

		

		

	
	
		3
		3/1/2007
		631.35
		0.00
		134.70
		496.65
		18,520.52
		

		

		

	
	
		4
		4/1/2007
		631.35
		0.00
		131.19
		500.16
		18,020.36
		

		

		

	
	
		5
		5/1/2007
		631.35
		0.00
		127.64
		503.71
		17,516.65
		

		

		

	
	
		6
		6/1/2007
		631.35
		0.00
		124.08
		507.27
		17,009.38
		

		

		

	
	
		7
		7/1/2007
		631.35
		0.00
		120.48
		510.87
		16,498.51
		

		

		

	
	
		8
		8/1/2007
		631.35
		0.00
		116.86
		514.49
		15,984.02
		

		

		

	
	
		9
		9/1/2007
		631.35
		0.00
		113.22
		518.13
		15,465.89
		

		

		

	
	
		10
		10/1/2007
		631.35
		0.00
		109.55
		521.80
		14,944.09
		

		

		

	
	
		11
		11/1/2007
		631.35
		0.00
		105.85
		525.50
		14,418.59
		

		

		

	
	
		12
		12/1/2007
		631.35
		0.00
		102.13
		529.22
		13,889.37
		

		

		

	
	
		13
		1/1/2008
		631.35
		0.00
		98.38
		532.97
		13,356.40
		

		

		

	
	
		14
		2/1/2008
		631.35
		0.00
		94.61
		536.74
		12,819.66
		

		

		

	
	
		15
		3/1/2008
		631.35
		0.00
		90.81
		540.54
		12,279.12
		

		

		

	
	
		16
		4/1/2008
		631.35
		0.00
		86.98
		544.37
		11,734.75
		

		

		

	
	
		17
		5/1/2008
		631.35
		0.00
		83.12
		548.23
		11,186.52
		

		

		

	
	
		18
		6/1/2008
		631.35
		0.00
		79.24
		552.11
		10,634.41
		

		

		

	
	
		19
		7/1/2008
		631.35
		0.00
		75.33
		556.02
		10,078.39
		

		

		

	
	
		20
		8/1/2008
		631.35
		0.00
		71.39
		559.96
		9,518.43
		

		

		

	
	
		21
		9/1/2008
		631.35
		0.00
		67.42
		563.93
		8,954.50
		

		

		

	
	
		22
		10/1/2008
		631.35
		0.00
		63.43
		567.92
		8,386.58
		

		

		

	
	
		23
		11/1/2008
		631.35
		0.00
		59.40
		571.95
		7,814.63
		

		

		

	
	
		24
		12/1/2008
		631.35
		0.00
		55.35
		576.00
		7,238.63
		

		

		

	
	
		25
		1/1/2009
		631.35
		0.00
		51.27
		580.08
		6,658.55
		

		

		

	
	
		26
		2/1/2009
		631.35
		0.00
		47.16
		584.19
		6,074.36
		

		

		

	
	
		27
		3/1/2009
		631.35
		0.00
		43.03
		588.32
		5,486.04
		

		

		

	
	
		28
		4/1/2009
		631.35
		0.00
		38.86
		592.49
		4,893.55
		

		

		

	
	
		29
		5/1/2009
		631.35
		0.00
		34.66
		596.69
		4,296.86
		

		

		

	
	
		30
		6/1/2009
		631.35
		0.00
		30.44
		600.91
		3,695.95
		

		

		

	
	
		31
		7/1/2009
		631.35
		0.00
		26.18
		605.17
		3,090.78
		

		

		

	
	
		32
		8/1/2009
		631.35
		0.00
		21.89
		609.46
		2,481.32
		

		

		

	
	
		33
		9/1/2009
		631.35
		0.00
		17.58
		613.77
		1,867.55
		

		

		

	
	
		34
		10/1/2009
		631.35
		0.00
		13.23
		618.12
		1,249.43
		

		

		

	
	
		35
		11/1/2009
		631.35
		0.00
		8.85
		622.50
		626.93
		

		

		

	
	
		36
		12/1/2009
		631.37
		0.00
		4.44
		626.93
		0.00
		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		0.00
		

		

		

		

		

		

	
	
		

		

		

		

		

		Ending Balance:
		

		

		

		

	
	
		[42]
		

		

		

		

		

		

		

		

		

	



Sheet 3: LoanComparisons

	
	
	
	
	
	
	
	
	
		Auto Loan Payment & Interest Comparisons
		

		

		

		

		

		
 
		
		

		Using This Worksheet
	
	
		http://www.vertex42.com/Calculators/auto-loan-calculator.html
		

		

		

		

		© 2007-2022 Vertex42 LLC
		

		Unless otherwise indicated, the comparison tables are based on the Loan Amount, Interest Rate, Term, and Frequency listed to the right. These values can be changed in the PaymentCalculator worksheet.

	
	
		

		

		

		

		

		

		

		

		

		
	
		

		Auto Loan Amount
		 $20,000 
		

		Term of Loan in Years
		3
		

		

		

		
	
		

		Annual Interest Rate
		8.50%
		

		Frequency of Payment
		Monthly
		

		

		

		
	
		

		

		

		

		

		

		

		

		

		
	
		

		

		

		

		
 
		
		

		
	
		Annual Interest Rate
		Total Paid
		Payment
		Total Interest
		

		

	
	
		2.00%
		20,622.60
		572.85
		622.60
		

		

	
	
		3.00%
		20,938.32
		581.62
		938.32
		

		The calculations in this worksheet assume that no extra payments are made.
	
	
		4.00%
		21,257.28
		590.48
		1,257.28
		

		
	
		5.00%
		21,579.12
		599.42
		1,579.12
		

		
	
		6.00%
		21,903.84
		608.44
		1,903.84
		

		

	
	
		7.00%
		22,231.44
		617.54
		2,231.44
		

		

	
	
		8.00%
		22,562.28
		626.73
		2,562.28
		

		

	
	
		9.00%
		22,895.64
		635.99
		2,895.64
		

		

	
	
		10.00%
		23,232.24
		645.34
		3,232.24
		

		

	
	
		

		

		

		

		

		

	
	
		

		

		

		

		
 
		
		

		

	
	
		# of Payments
		Total Paid
		Payment
		Total Interest
		

		

	
	
		6
		20,498.76
		3,416.46
		498.76
		

		

	
	
		12
		20,932.80
		1,744.40
		932.80
		

		

	
	
		18
		21,372.66
		1,187.37
		1,372.66
		

		

	
	
		24
		21,818.64
		909.11
		1,818.64
		

		

	
	
		30
		22,270.80
		742.36
		2,270.80
		

		

	
	
		36
		22,728.60
		631.35
		2,728.60
		

		

	
	
		42
		23,192.40
		552.20
		3,192.40
		

		

	
	
		48
		23,662.56
		492.97
		3,662.56
		

		

	
	
		54
		24,138.00
		447.00
		4,138.00
		

		

	
	
		60
		24,619.80
		410.33
		4,619.80
		

		

	
	
		66
		25,107.72
		380.42
		5,107.72
		

		

	
	
		72
		25,601.04
		355.57
		5,601.04
		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		
 
		
		

		

	
	
		Down Payment
		Loan Amount
		Payment
		Total Interest
		

		

	
	
		$0.00 
		20,000.00
		631.35
		2,728.60
		

		

	
	
		$1,000.00 
		19,000.00
		599.78
		2,592.08
		

		

	
	
		$2,000.00 
		18,000.00
		568.22
		2,455.92
		

		

	
	
		$3,000.00 
		17,000.00
		536.65
		2,319.40
		

		

	
	
		$4,000.00 
		16,000.00
		505.08
		2,182.88
		

		

	
	
		$5,000.00 
		15,000.00
		473.51
		2,046.36
		

		

	
	
		

		

		

		

		

		

	
	
		[42]
		

		

		

		

		

	
	
		

		

		

		

		

		

	
	
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		Frequency
		# of Payments
		Payment
		Total Interest
		

		

		

		

		

		

	
	
		Annually
		3
		7,830.78
		3,492.34
		

		

		

		

		

		

	
	
		Semi-Annually
		6
		3,846.35
		3,078.10
		

		[42]
		

		

		

		

	
	
		Quarterly
		12
		1,905.74
		2,868.88
		

		

		

		

		

		

	
	
		Bi-Monthly
		18
		1,266.60
		2,798.80
		

		

		

		

		

		

	
	
		Monthly
		36
		631.35
		2,728.60
		

		

		

		

		

		

	
	
		Semi-Monthly
		72
		315.19
		2,693.68
		

		

		

		

		

		

	
	
		Bi-Weekly
		78
		290.91
		2,690.98

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet autoloancalculator auto loan calculator using this worksheet httpwwwvertexcomcalculatorsautoloancalculatorhtml copy vertex llc provides a breakdown of the sales cost charges fees and taxes associated with buying car from dealer many values will need to be estimated you may consult policies your state determine how tax is applied what registration other are purchase price before sale include new plus any additional options do not destination charge that automaker deliver amount usually fixed negotiable it varies depending on set if included in title transfer fee payable by owner s dmv taxable eg subject such as due at delivery check some states value tradein deductible choose true then subtracted calculating time creation following allow deducted california district columbia hawaii maryland michigan don t know false bottom download page vertexcom find numerous links online calculators resources help complete manufacturer cash rebate can substracted calculated rate net excise make ...

Related files

Share

Help

Auto Loan Calculator					Using This Worksheet
http://www.vertex42.com/Calculators/auto-loan-calculator.html				© 2007-2022 Vertex42 LLC	This worksheet provides a break-down of the sales cost, charges, fees, and taxes associated with buying a car from a dealer. Many of the values will need to be estimated, and you may need to consult the policies of your state to determine how sales tax is applied and what the registration and other fees are.

	Purchase Price (before tax)
		Sale Price Include the sale price of the new car plus any additional options. Do not include the sales tax. Sale Price + Options	$20,000
		Destination Charge: The cost that the automaker charges to deliver the auto to the dealer. This amount is usually a fixed charge and is not negotiable. It varies depending on the car. Destination charge	$-	Set to 0 if included in sale price
		Title Transfer Fee: The title transfer fee is payable by the new owner to the state's DMV. This fee is usually taxable. Title Transfer Fee	$-	e.g. $10
		Taxable Fees: Fees that are subject to State Sales Tax, such as fees that are due at delivery. Other Taxable Fees	$-
		Purchase Price	$20,000

	State Sales Tax
		Check Your State: In some states, the value of the trade-in is not tax deductible. If you choose TRUE, then the trade-in value is subtracted from the sale price BEFORE calculating the taxes. At the time of creation of this calculator, the following states DO NOT allow the trade-in value to be deducted: California, District of Columbia, Hawaii, Maryland, Michigan Trade-In is Tax Deductible	$-	If you don't know, set to FALSE	At the bottom of the Download page at Vertex42.com you will find numerous links to online calculators and resources that may help you complete this worksheet.
		Check Your State: In some states, the manufacturer's Cash Rebate can be substracted from the sale price before the sales tax is calculated. Cash Rebate is Tax Deductible	$-	If you don't know, set to FALSE
		State Sales Tax Rate	6.25%	e.g. 6.25%
		Net Taxable	$20,000
		State Sales (Excise) Tax	$1,250.00
					To make comparisons, you can make multiple copies of Column D (select column D and press Ctrl+c, then right-click on column E and select Insert Copied Cells)
	Non-Taxable Fees
		Registration Fee: The registration fee usually varies depending on the vehicle type, fuel type, county, and other factors. Call your DMV to find out the registration cost. Registration	$40	e.g. $40 to $100
		Late Registration: Many states due not have a grace period after registration has expired, so make sue you get the titled transferred and the registration fee paid right away. Late Registration Fee	$-	e.g. $10 per month
		Service Contract: Very likely, the dealer will try to get you to purchase a service contract. This can be a very significant cost, so don't neglect considering it in your calculation. Service Contract	$-	e.g. $250 to $1000
		Special Plate Fee	$-	e.g. $25
		Administration Fee	$3	e.g. $3
		Transaction Fee	$3	e.g. $3
		Late Title Transfer Fee: In many states, you have a limited amount of time (often 30 days) to transfer the title of the new car. If you don't meet the deadline, you end up paying late fees. Don't be late! The fees can add up quickly. e.g. $25 to $200 Late Title Transfer Fee	$-	e.g. $25 to $200
		Duplicate Title Fee	$-	e.g. $25
		Non-Taxable Fees Fees that are NOT subject to sales tax, such as document fees or fees due at delivery. Other Non-Taxable Fees	$-
		Total Non-Taxable Fees	$46

	Loan Amount

		Unpaid Balance on Previous Loan: If you have an unpaid balance on a loan for a car that you are trading in, enter the unpaid loan balance here. Unpaid Loan Balance on Trade-In	$-

		Down Payment: The amount you pay in cash (or by check). Less Down Payment	$-
		Trade-In: If you are trading in an older car, enter the value accepted by the dealer here. In some states, the trade-in value can be deducted from the purchase price prior to calculating the sales tax. Less Value of Trade-In	$-
		Manufacturere's Cash Rebate: Sometimes, the manufacturer will provide a cash rebate or low-interest financing. In some states, the cash rebate can be deducted from the purchase price before calculating the sales tax. Less Cash Rebate	$-

		Total Loan Amount	$21,296



[42]

Auto Loan Payment Calculator							Using This Worksheet
http://www.vertex42.com/Calculators/auto-loan-calculator.html					© 2007-2022 Vertex42 LLC		This spreadsheet creates a payment schedule for a fixed-rate auto loan, with optional extra payments. Use the spreadsheet to compare different terms, rates, and loan amounts. The spreadsheet allows complete flexibility in how you make additional payments.

	Inputs				Effect of Extra Payments
		Loan Amount: This is the amount that you have borrowed. It is NOT the price of the automobile. It is the amount financed, which often consists of the price of the auto + fees + sales tax - down payment. Auto Loan Amount	$20,000		Total Payments	$22,728.62
		Annual Interest Rate	8.50%		Total Interest	$2,728.62
		Term of Loan Auto loans are usually between 1 and 6 years. If you want to specify a specific number of months, enter the formula: =months/12 For example: For a 6-month loan, enter =6/12 For a 18-month loan, enter =18/12 Term of Loan in Years	3		Reduced Interest The reduced interest expense associated with making extra payments. The result may be off by a few cents due to rounding. When you make extra payments on the principal above your normal payment, then you pay less interest in the long run. Reduced Interest	None
		First Payment Date Assumes that the first payment date is at the end of the first period. Shortcut: To enter today's date, press Ctrl+; First Payment Date	1/1/2007		Number of Payments	36
		Frequency of Payment	Monthly		Last Payment Date	12/1/2009
							Use the AutoLoanCalculator worksheet to figure out the Auto Loan Amount.
	Summary (with no extra payments)
		Number of Payments	36
		Rate (per period)	0.708%				The payment is rounded to the nearest cent. The last payment is adjusted to bring the balance to zero.
		Payment (per period)	$631.35
		Total Interest: If you don't make any extra payments, this will be the total amount of interest paid over the life of the loan. Total Interest	$2,728.63
		Total Payments: If you don't make any extra payments, this will be the total amount paid over the life of the loan (including interest). The amount may be off by a few cents due to rounding. Total Payments	$22,728.63
			.

No.	Due Date	Payment Due	Additional Payment The amount paid directly towards the principal. In order to pay off the remaining balance, the additional payment must be the last period balance - payment due + interest due. For example, to pay off after the first period, the additional payment would be: =G24-C25+E25 (Assumes no penalties for making additional payments.) Additional Payment	Interest	Principal	Balance
						$20,000.00
1	1/1/2007	631.35	0.00	141.67	489.68	19,510.32
2	2/1/2007	631.35	0.00	138.20	493.15	19,017.17
3	3/1/2007	631.35	0.00	134.70	496.65	18,520.52
4	4/1/2007	631.35	0.00	131.19	500.16	18,020.36
5	5/1/2007	631.35	0.00	127.64	503.71	17,516.65
6	6/1/2007	631.35	0.00	124.08	507.27	17,009.38
7	7/1/2007	631.35	0.00	120.48	510.87	16,498.51
8	8/1/2007	631.35	0.00	116.86	514.49	15,984.02
9	9/1/2007	631.35	0.00	113.22	518.13	15,465.89
10	10/1/2007	631.35	0.00	109.55	521.80	14,944.09
11	11/1/2007	631.35	0.00	105.85	525.50	14,418.59
12	12/1/2007	631.35	0.00	102.13	529.22	13,889.37
13	1/1/2008	631.35	0.00	98.38	532.97	13,356.40
14	2/1/2008	631.35	0.00	94.61	536.74	12,819.66
15	3/1/2008	631.35	0.00	90.81	540.54	12,279.12
16	4/1/2008	631.35	0.00	86.98	544.37	11,734.75
17	5/1/2008	631.35	0.00	83.12	548.23	11,186.52
18	6/1/2008	631.35	0.00	79.24	552.11	10,634.41
19	7/1/2008	631.35	0.00	75.33	556.02	10,078.39
20	8/1/2008	631.35	0.00	71.39	559.96	9,518.43
21	9/1/2008	631.35	0.00	67.42	563.93	8,954.50
22	10/1/2008	631.35	0.00	63.43	567.92	8,386.58
23	11/1/2008	631.35	0.00	59.40	571.95	7,814.63
24	12/1/2008	631.35	0.00	55.35	576.00	7,238.63
25	1/1/2009	631.35	0.00	51.27	580.08	6,658.55
26	2/1/2009	631.35	0.00	47.16	584.19	6,074.36
27	3/1/2009	631.35	0.00	43.03	588.32	5,486.04
28	4/1/2009	631.35	0.00	38.86	592.49	4,893.55
29	5/1/2009	631.35	0.00	34.66	596.69	4,296.86
30	6/1/2009	631.35	0.00	30.44	600.91	3,695.95
31	7/1/2009	631.35	0.00	26.18	605.17	3,090.78
32	8/1/2009	631.35	0.00	21.89	609.46	2,481.32
33	9/1/2009	631.35	0.00	17.58	613.77	1,867.55
34	10/1/2009	631.35	0.00	13.23	618.12	1,249.43
35	11/1/2009	631.35	0.00	8.85	622.50	626.93
36	12/1/2009	631.37	0.00	4.44	626.93	0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
			0.00
					Ending Balance:
[42]

Auto Loan Payment & Interest Comparisons						Using This Worksheet
http://www.vertex42.com/Calculators/auto-loan-calculator.html					© 2007-2022 Vertex42 LLC	Unless otherwise indicated, the comparison tables are based on the Loan Amount, Interest Rate, Term, and Frequency listed to the right. These values can be changed in the PaymentCalculator worksheet.

	Auto Loan Amount	$20,000		Term of Loan in Years	3
	Annual Interest Rate	8.50%		Frequency of Payment	Monthly


Annual Interest Rate	Total Paid	Payment	Total Interest
2.00%	20,622.60	572.85	622.60
3.00%	20,938.32	581.62	938.32			The calculations in this worksheet assume that no extra payments are made.
4.00%	21,257.28	590.48	1,257.28
5.00%	21,579.12	599.42	1,579.12
6.00%	21,903.84	608.44	1,903.84
7.00%	22,231.44	617.54	2,231.44
8.00%	22,562.28	626.73	2,562.28
9.00%	22,895.64	635.99	2,895.64
10.00%	23,232.24	645.34	3,232.24


# of Payments	Total Paid	Payment	Total Interest
6	20,498.76	3,416.46	498.76
12	20,932.80	1,744.40	932.80
18	21,372.66	1,187.37	1,372.66
24	21,818.64	909.11	1,818.64
30	22,270.80	742.36	2,270.80
36	22,728.60	631.35	2,728.60
42	23,192.40	552.20	3,192.40
48	23,662.56	492.97	3,662.56
54	24,138.00	447.00	4,138.00
60	24,619.80	410.33	4,619.80
66	25,107.72	380.42	5,107.72
72	25,601.04	355.57	5,601.04


Down Payment	Loan Amount	Payment	Total Interest
$0.00	20,000.00	631.35	2,728.60
$1,000.00	19,000.00	599.78	2,592.08
$2,000.00	18,000.00	568.22	2,455.92
$3,000.00	17,000.00	536.65	2,319.40
$4,000.00	16,000.00	505.08	2,182.88
$5,000.00	15,000.00	473.51	2,046.36

[42]



Frequency	# of Payments	Payment	Total Interest
Annually	3	7,830.78	3,492.34
Semi-Annually	6	3,846.35	3,078.10		[42]
Quarterly	12	1,905.74	2,868.88
Bi-Monthly	18	1,266.60	2,798.80
Monthly	36	631.35	2,728.60
Semi-Monthly	72	315.19	2,693.68
Bi-Weekly	78	290.91	2,690.98

Related files

Share

Share to social media

Help

Login Area