Bimmerfest - BMW Forums - View Single Post - The ultimate check your BMW 3 Series F30 deal thread
View Single Post
  #1  
Old 03-03-2015, 09:32 PM
dwlink's Avatar
dwlink dwlink is offline
Officially Welcomed to the 'Fest
Location: St. Louis
 
Join Date: May 2007
Posts: 2,702
Mein Auto: 435i Gran Coupe
The ultimate check your BMW 3 Series F30 deal thread

In an attempt to reduce the number of threads created by number hungry potential leasees/purchasees, I've put together a spreadsheet that is far more comprehensive than any auto finance or lease calculator you'll find online.

In addition to merely presenting you with cash flows and monthly payment figures, this spreadsheet will also calculate roughly how good of a deal you are getting via a field called negotiating margin. Aside of just the sales price alone, the negotiating margin will increase if you are paying an excessively high money factor or doc fees which may be an indication you are leaving money on the dealer's side of the table.

The spreadsheet also allows you to explicitly set how fees and taxes are handled for leases. For example, you can configure the deal such that taxes are paid on the entire value of the car rather than just the depreciation. Additionally, you can also configure how taxes are paid whether it is cash due at signing, a cap cost adjustment, or simply a monthly component of your lease payment.

I've copied and pasted the help information below so you can get an idea of how flexible this spreadsheet is. Also, because BF does not allow .xlsx attachment, you'll have to click the link below to view the spreadsheet from my google drive. In order to edit the quantities, simply download the spreadsheet by clicking the downward pointing arrow towards the top-center of the viewer. All calculations are done using formulas, thus there are no macros embedded in the workbook.


Click here to open up the spreadsheet



Instructions
Read the instructions, this will assist you in filling out the information on the "Main" and "Data" sheets

Generally, you will spend the majority if your time on the "Main" sheet. However, occasionally you may need to change some numbers which are generally more universal.
These are found on the "Data" sheet. See section 7 for details on how to change values on the "Data" sheet.

The only values you need to fill in / choose are those in cells with a blue background

Section 1 needs to be filled in correctly to provide an accurate estimate of your negotiating power which is presented in Section 2. For purchase/lease comparisons fill out all sections.
Purchasees must fill out: Section 2, the first three questions in Section 3, and Section 5A.
Leasees must fill out: Section 2, Section 3, Section 4, and Section 6A.

Section 1: Type of sale
The purpose of this section is to provide information to the Sale Information section. This will help to determine dealer cost as well as your potential negotiating margin.
For leases, your negotiating margin will also be influenced by the lease acquisition fee as well as the money factor. If you pay in excess of their respective baseline quantities
on the "Data" sheet, this will be reflected in greater negotiating margin.

In this section, enter the following quantities:
1. Purchase Type [Lease/Buy]: Choose the appropriate value for your type of purchase
2. Delivery Type [US/Euro]: If you are picking up your car at the BMW Welt in Germany choose Euro, otherwise choose US

Section 2: Sale Information
In this section, enter the following quantities:
1. Base vehicle MSRP: The price of the base vehicle before options, fees, etc.
2. Total of Options MSRP: The price of all the options the car has summed together.
3. Dealer Incentives: The total of any amounts the dealer is reimbursed for your purchase.
4. Sales Price: The negotiated sales price of the car including handling before any fees.
5. Admin/Misc/Doc Fees: The total of all paperwork fees charged by the dealer.
Note: This may include temporary registration. Do NOT add in any full registration/titling/licensing fees.
6. Lease Acquisition Fee (leases only): The fee the dealer is charging to establish your lease agreement.

Section 3: Tax Questions
In this section, enter the following quantities:
1. Tax Rate: Enter the tax rate you'll pay based on your location - may be a combination of state and local taxes.
2. Admin/Misc/Doc Fees Taxable [Yes/No]: Typically, these fees are NOT taxable, however if they are then select yes.
3. Trade In Tax Deduction [Yes/No]: In most states, the taxable amount is reduced by the value of your trade in. If it isn't select no.
4. Lease Acquisition Fee Taxable (leases only) [Yes/No]: Typically this fee is taxable, but if it is not then select no.
5. Lease Interest Taxable (leases only) [Yes/No]: In some states, the interest paid on leases is taxable. If this is not true about your state, select no.
6. Residual Value Taxable (leases only) [Yes/No]: In most states, taxes are only paid on the depreciation, however if your state taxes the full value of the car then select yes.

Section 4: Lease Cash Flow Questions
In this section, enter how the following quantities are paid:
1. Admin/Misc/Doc Fees [Cash At Signing/Cap Cost Adjustment]: Generally these are paid as Cash due at signing.
2. Lease Acquisition Fee [Cash At Signing/Cap Cost Adjustment]: Generally this is paid as Cash due at signing.
3. Lease Taxes: Some states require taxes to be paid up front, but you may have the option to finance the taxes.
Cash At Signing: Simple, taxes are paid in a single cash payment at signing.
Cap Cost Adjustment: Taxes are added to the cap cost and are implicitly reflected in the monthly lease payments via greater cap cost.
Monthly Payments: Taxes are paid monthly and are an explicit component of the monthly lease payment.
Section 5A: Finance Information
In this section, enter the following quantities:
1. Term: The number of months your financing agreement lasts for (typically 36-72).
2. Net Trade In Value: The value offered for your trade in less any balance owed on the trade in vehicle.
3. Down Payment: The amount of cash paid upfront towards the vehicle purchase.
4. Interest Rate: The annual interest rate on your auto loan.

Section 5B: Tax Information (Purchase)
Nothing to enter here, rather this section shows a breakdown of how the tax was calculated for your purchase.

Section 5C: Finance Details
Nothing to enter here, rather this section shows a breakdown of how much was paid for the car, how much was financed, and what the total cost of the purchase was with any interest paid over the financing term.

Section 6A: Lease Information
In this section, enter the following quantities:
1. Term: The number of months your leasing agreement lasts for (typically 24-39).
2. Net Trade In Value: The value offered for your trade in less any balance owed on the trade in vehicle.
3. Down Payment: The amount of cash paid upfront towards cap cost reductions.
4. Residual Value: The percentage of the vehicle's value (based off MSRP) remaining at lease termination.
Note: BMW issues fixed percentages for residual values based on the vehicle, annual mileage, and lease term. These values typically change monthly. It is up to the leasee to enter the correct residual. Additionally, it is also up to the leasee to perform their due diligence to ensure the residual he/she is being quoted is "fair."
5. Base Money Factor: The money factor being offered to you before any additional security deposits.
6. # Extra Security Deposits: The number of extra security deposits you are making upfront to reduce your money factor.
With all leases you will pay 1 security deposit upfront, but this will not reduce your money factor. However, you can make a maximum of 7 extra security deposits (total of 8) to minimize your money factor.
Section 6B: Tax Information (Lease)
Nothing to enter here, rather this section shows a breakdown of how the tax was calculated for your lease.

Section 6C: Lease Details
Nothing to enter here, rather this section shows a breakdown of the lease depreciation, interest, and taxes. This section calculates the monthly lease payment as well as the amoutn of cash due at signing. Lastly, it also presents the amount of cash you would pay if you chose to make a cash purchase of the vehicle at the residual value upon lease termination. This may be helpful for a purchase/lease comparison.

Section 7: The "Data" Sheet
The values on this sheet can be considered to be relatively constant, however should you need to change anything here is a description of all the values on this sheet.
1. Base Discount Ratio: Percent of the base vehicle MSRP that the dealer pays. Note that this value depends on the type of delivery being taken. See column B for the respective delivery type.
2. Options Discount Ratio: Percent of the lines/packages/options that the dealer pays. Does not include destination and handling.
3. Destination Cost: Destination and Handling cost. This does not get discounted in the dealer's cost.
4. Lease Acquisition Benchmark: BMW's advertised cost for the Lease Acquisition Fee. Any Lease Acquisition fee paid in excess of this amount increases your negotiating margin.
5. Money Factor Benchmark: BMW's advertised minimum base money factor for US deliveries. Generally, this value is higher by 0.0003 for European Deliveries. MF paid in excess of this amount increases your negotiating margin.
6. SD Reduction: The amount your money factor is decreased by each additional security deposit.
7. Max # Extra SDs: The maximum number of additional security deposits you can make to lower your Money Factor.
8. SD Rounding Increment: The nearest amount your monthly payment will be rounded up to in order to determine the base cost of an individual security deposit.
Example: With a lease payment of $507 and an increment of $50, the cost of each security deposit will be $550.
9. Lease Disposition Fee: The amount you will pay at the end of the lease should you not choose to purchase the car or enter into a new lease agreement with BMW.
__________________

2015 BMW 435i GC MSport (ED Sept 29, 2014)
2004 BMW 530i (retired)

Last edited by Michael Schott; 03-04-2015 at 07:00 AM.
Reply With Quote