BimmerFest BMW Forum banner

The ultimate check your BMW 3 Series F30 deal thread

470K views 1K replies 287 participants last post by  335fresh 
#1 · (Edited)
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.
 
See less See more
#1,427 ·
Thanks

Thanks for the info,
Is Greatly appreciated,
My next bmw will be leased, lol, but thinking of buying a programmer, you can't take the screwdriver out-of-my hand, lol
I just p/u a 14 328i xdrive 22k in July 2018 1st bmw , 3x merc, 30x others, 30+ cars/trucks/toys so far
Just want to say Thanks to all who answer questions, have repaired /fixed /found out about/posted
Your questions/answers are for Real and saved me a ton of time and money,at least $1000, been searching/using the replies to fix my ride, all of it since Aug 2018, just joined now when I felt I could give more advice than questions, Lots of Smart helpful bmw lovers here CPO certified means -0-ZERO- your knowledge/sharing here is essential !!
 
#1,429 ·
Sharing my recent deal -

Picked up a 2015 325i (pretty basic model)

Summary of details

$26,8xx OTD financed 3.9% $500 down

40k miles
No Nav
No Parking sensors
No seat warmers

EasyCare extended warranty
100k up to year 2023 w/$50 deductible
Wheels and Tires included

Alpine White
Black interior with sunroof
Rain detect wipers

I think it’s just an okay deal. Theres a few better deal to be had but this seemed to be close to the middle ground.

Bought from BMW stealership under AutoNation. I have 4 more days to return if i change my mind.

California based
 
#1,434 ·
Incentives

Hello guys,
Yes, I am a fair-weather friend and I do apologize. Got great help here 3 years ago for my last lease and I am looking through all the calculations I did then. Time for the next one. Could you please remind me where the incentives are applied. I live in Florida and I am looking at 2019 330i to replace my 328. With help from here, I was able to go below invoice price last time and got a pretty good deal according to this forum. So, I negotiate the cap cost(the price they will sell me the car for) and then the incentives are applied? Seems to be $2,250 this month.
I know irregular visitors are not always looked well upon here but honestly, I am not that much of a car enthusiast, this is for my wife. I really do appreciate the help though! I did reread a lot of things here including how to search invoice prices. Some of the links not that accurate. Just going to have them show me the invoice.

Thank you!

-Brian
 
#1,435 ·
Picked up a new 2018 330i MSRP about $42K, after saying no many times the final price was $30,500. When I went into finance BMW FS lowered the interest rate to 3% as the car was below 85% below MSRP. The car only has metallic paint as an option - so it's not overly optioned but still a nice car for the price.
 
#1,437 ·
2013 f30, alpine white luxury line 6 spd, 235k miles, one owner, missing engine cover but otherwise clean, needs tires, slight vibe through the shifter, runs excellent though. Oil changes look good on carfax, unfortunately can't see much other maintenance then that. He's asking 7k. I haven't found a close mileage n55 f30 so what do you guys think? Buy or fly?
 
#1,438 ·
Im new too BMW and looking to get a wagon. Ive narrowed in down to 2 diffrent candidates and Im looking for some feedback from those that know much more than I.

The first is an M Sport model with 100k on the clock but looks well cared for. No guarantee I would win this bid and wondering what a fair price would be to offer.

The other is a Sport Line model. I love this color combo and the milage is much lower. There is an accident reported on it however.

Any feedback would be appreciated. Thanks
 
#1,440 ·
What do you know about the timing chain in each one? The N20/26 motor in the 328 is susceptible to timing chain guide failure.
Im told the original guide is orange and the replacement guide is white and that you can see these via the oil fill. The owner of the M Sport (102k miles) say it looks like the original guide is in place so it's assumed the chain has never been replaced.

I haven't inquired about the other car yet.
 
#1,441 ·
Still waiting on delivery of a 2020 430i EU-spec BMW with 8000 miles on the odometer. Got it for €29,300 fromthe dealer, my American credit union is screwing me on the exchange rate for the loan, so out the door for ~$31,000. Was a leased car in the times of COVID, so still has manufacturer warranty + dealer oil changes attached. Due to it not being privately owned,I got 19% VAT deducted. Woot!

Adaptive Cruise Control, ID6 infotainment, with color HUD.

Aftermarket purchases:

Swapping stock runflats for Continental AllWeather Contacts - $800
6WB Digital Display Cluster (from local German OEM dealer, not refurbished) + coding - $1200
Bimmertech MMI Prime for wireless Android Auto / Carplay + rearview camera + coding - $1200
Aftermarket rubber floor mats - $100

I work for the U.S. military in Germany, having taken the job while I was working in Colorado. Colorado just sent me a $1500 stimulus check, even though I don't technically pay any state tax anymore due to my overseas tax status. Paid for a good portion of the upgrades!
 
#1,442 ·
Hello. Brand New. I am going to read the pinned post but I still wanted to post this as I know next to nothing about BMWs.

Looking very hard at this f30 340i

Autotrader - page unavailable

So I drive very little, maybe 5-8k mi a year. Looking for the fun factor and handling this may offer. Never driven a 340i I've driven an older 330.

Is this the Msport package cause some other videos I see have different wheels? If it is in fact Msport what does that package provide on this car vs base model?

Anything else to look out for? Im new to BMW. Im in touch and arguing the dealer to possibly throw in the recommended 60k service as the car is at 59k. The 30k service was completed. They are a family of dealers including BMW so Im going to push hard for this to close if I end up trying to get it.

Again its not the badge for me but the looks, power and fun factor. I'm actually likely going to need a truck in a few years but I'm trying to prolong that. Either I'll keep the 340i or have a decent trade in...will finance but likely make double payments most months. Probably financing 25k. I'm in a position where I need a car and I'm tired of all the bullshit out there. Its either something like this or a complete beater under 8k. For those who will ask income is 110k and I have about $2800 per month after bills/expenses/groceries. Savings took a hit as I paid off 20k in debt recently thinking I wouldn't need a car in the next few years. Credit is very good 740s I suppose.

Based on the car/price/my situation....good or bad? I'm REALLY interested in this one. Please don't buy it tomorrow until I make my mind up.

Edit- I asked and the salesman said it did have a tune that set to be wiped when it was traded in. Im trying to get more details but I'm VERY worried about potential problems there. Other than that check the carfax, all looks good to me.
 
#1,444 ·
Hello. Brand New. I am going to read the pinned post but I still wanted to post this as I know next to nothing about BMWs.

Looking very hard at this f30 340i

Autotrader - page unavailable

So I drive very little, maybe 5-8k mi a year. Looking for the fun factor and handling this may offer. Never driven a 340i I've driven an older 330.

Is this the Msport package cause some other videos I see have different wheels? If it is in fact Msport what does that package provide on this car vs base model?

Anything else to look out for? Im new to BMW. Im in touch and arguing the dealer to possibly throw in the recommended 60k service as the car is at 59k. The 30k service was completed. They are a family of dealers including BMW so Im going to push hard for this to close if I end up trying to get it.

Again its not the badge for me but the looks, power and fun factor. I'm actually likely going to need a truck in a few years but I'm trying to prolong that. Either I'll keep the 340i or have a decent trade in...will finance but likely make double payments most months. Probably financing 25k. I'm in a position where I need a car and I'm tired of all the bullshit out there. Its either something like this or a complete beater under 8k. For those who will ask income is 110k and I have about $2800 per month after bills/expenses/groceries. Savings took a hit as I paid off 20k in debt recently thinking I wouldn't need a car in the next few years. Credit is very good 740s I suppose.

Based on the car/price/my situation....good or bad? I'm REALLY interested in this one. Please don't buy it tomorrow until I make my mind up.

Edit- I asked and the salesman said it did have a tune that set to be wiped when it was traded in. Im trying to get more details but I'm VERY worried about potential problems there. Other than that check the carfax, all looks good to me.
The F30 has the famed b58 engine also used in the Toyota Supra. Very modifiable to specs for much more horsepower without blowing the engine, if done correctly.
 
#1,443 ·
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.
This is amazing work and very helpful to prospective owners!
 
#1,445 ·
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.
Thanks for all the effort.. This has been a real help
 
#1,446 ·
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.
 
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top