Bimmerfest - BMW Forums

Bimmerfest - BMW Forums (https://www.bimmerfest.com/forums/index.php)
-   F30 / F31 / F32 / F33 / F34 / F36 (2012 - current) (https://www.bimmerfest.com/forums/forumdisplay.php?f=153)
-   -   The ultimate check your BMW 3 Series F30 deal thread (https://www.bimmerfest.com/forums/showthread.php?t=830104)

dwlink 03-03-2015 09:32 PM

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.

bobbydigital 03-04-2015 12:48 AM

Awesome. Well done and very timely for me as I'm in the market for a 435 GC.

msskarina 03-04-2015 05:10 AM

This is Great! Thanks Dwlink!

xsiv 03-04-2015 05:53 AM

wow! Thanks for taking the time to put this together

minn19 03-04-2015 06:04 AM

Very well written and helpful/useful post dwlink. Unfortunately it will probably be for not. Furb tried to do this last year and others have tried similar things on other topics, but to no avail. For some reason people, especially newbie hit and run members don't like to use the search function.

Maybe this time it will be different. Make it a sticky?

dwlink 03-04-2015 06:10 AM

Quote:

Originally Posted by minn19 (Post 8944028)
Very well written and helpful/useful post dwlink. Unfortunately it will probably be for not. Furb tried to do this last year and others have tried similar things on other topics, but to no avail. For some reason people, especially newbie hit and run members don't like to use the search function.

Maybe this time it will be different. Make it a sticky?

Perhaps if the title was all caps at 20 point font in flashing rainbow colors :dunno:

minn19 03-04-2015 06:15 AM

Quote:

Originally Posted by dwlink (Post 8944034)
Perhaps if the title was all caps at 20 point font in flashing rainbow colors :dunno:

Who knows, probably not. :rofl:

Somebody posted a deal review/question in the off topic section. I don't think some even read the thread topics. :tsk:

need4speed 03-04-2015 06:36 AM

cool, thanks for creating this. N4S

Michael Schott 03-04-2015 06:55 AM

Thanks for this dwlink! Awesome job. I have stickied the thread and amended your thread title. Hopefully, members who want to see how they did will click the thread. Members, if you see that kind of thread, post a response asking the OP to see this thread.

Wolves 03-04-2015 11:58 AM

Thanks for creating this!

Mykatie 03-04-2015 12:54 PM

Great job. Thank you for your time and effort.:thumbup:

BeeMarlo 03-05-2015 07:09 AM

Great!! Thank you!

nasdaq22 03-06-2015 10:05 AM

This is incredible information you are sharing with this community. Thanks!
I am not seeing a link in my app for the sheet. Am I overlooking it?

dwlink 03-06-2015 12:17 PM

Well that's interesting... The link appears in the web view, but doesn't show up using the native view in Bimmerapp. Come on app developers!!!

dfj1bmw 03-07-2015 08:59 AM

Can u post again?

dwlink 03-07-2015 11:34 PM

Quote:

Originally Posted by dfj1bmw (Post 8950856)
Can u post again?


Whats the issue? Keep in mind if you are trying to do this on a phone you're out of luck. You'll need excel for things to show up and update properly anyways.

visi107 03-08-2015 11:50 AM

Very comprehensive. Thank you for posting this.

Aileron 03-09-2015 12:28 AM

Fantastic job. You made a clear, concise and easy-to-use applet that breaks everything down in a format that's very understandable.

Not only does it make it easy to see what payments would be with various options, comparing the lease versus purchase is made easier.

However there's no helping those who don't have a finance background or understand the mathematics of net present value. In the event that you were wanting to tinker with your pricing template, one aspect I think we'd all find useful is the net present value of any of our various choices.

For those not in the know on NPV, many people simply look at which has the lowest total at the end. Unfortunately that's easily skewed by irregular payment streams to include refundable security deposit's, down payments, and balloon/residual payments.

One recent lease versus purchase article I read used an example where basically the lease and finance came out to be almost the same over 5 yrs. However the way they did it was with very low monthly lease payments for three years as usual. But then to make it equivalent to the finance deal, simply finance the residual value over the last two years so you compare 60 month versus 60 month term's. However what you actually end up with is something like a $500 a month payment during your 36 month lease term and then $1200 payments for the last two years. Yes, by adding huge payments at the end you can bring the costs in alignment but they may not meet with peoples actual budgets. So the whole comparison really is difficult to do without a very simple final check figure - which would be a net present value. Where the net present value would simply be today's value of that stream of payments. So it would be your real today's cost of entering into that agreement.

I used to write those formulas in Excel or use a program called TValue to figure NPV. Wish I could help more but it's been probably 12 years since I've done it and a bit behind the power curve on that type of thing these days. But otherwise very helpful. Thanks for your efforts.

dwlink 03-09-2015 05:28 AM

Quote:

Originally Posted by Aileron (Post 8953891)
Fantastic job. You made a clear, concise and easy-to-use applet that breaks everything down in a format that's very understandable.

Not only does it make it easy to see what payments would be with various options, comparing the lease versus purchase is made easier.

However there's no helping those who don't have a finance background or understand the mathematics of net present value. In the event that you were wanting to tinker with your pricing template, one aspect I think we'd all find useful is the net present value of any of our various choices.

For those not in the know on NPV, many people simply look at which has the lowest total at the end. Unfortunately that's easily skewed by irregular payment streams to include refundable security deposit's, down payments, and balloon/residual payments.

One recent lease versus purchase article I read used an example where basically the lease and finance came out to be almost the same over 5 yrs. However the way they did it was with very low monthly lease payments for three years as usual. But then to make it equivalent to the finance deal, simply finance the residual value over the last two years so you compare 60 month versus 60 month term's. However what you actually end up with is something like a $500 a month payment during your 36 month lease term and then $1200 payments for the last two years. Yes, by adding huge payments at the end you can bring the costs in alignment but they may not meet with peoples actual budgets. So the whole comparison really is difficult to do without a very simple final check figure - which would be a net present value. Where the net present value would simply be today's value of that stream of payments. So it would be your real today's cost of entering into that agreement.

I used to write those formulas in Excel or use a program called TValue to figure NPV. Wish I could help more but it's been probably 12 years since I've done it and a bit behind the power curve on that type of thing these days. But otherwise very helpful. Thanks for your efforts.

Thanks for the comments and suggestion!

I have the whole NPV thing in the works as well as another drop down option to prioritize whether your trade goes towards cap cost reduction or security deposits first. There will be a new sheet in the next version that shows the amortization schedules - working on making it look clean and dummy proofing right now.

shaunole 03-10-2015 05:32 AM

This is great! I'm having a difficult time understanding the Monthly interest calculation (for Lease). By the formula, it looks like it adds the Residual Value to the Adjusted Cap Cost and then multiplies the sum of those by the money factor. The sum of those first 2 figures is substantially higher than the MSRP (in my case >$25,000). Maybe this is a misunderstanding on my part of how the money factor applies. Could you help to clarify?

dwlink 03-10-2015 06:10 AM

Annual Interest = (Simple average value of car during lease) x Interest Rate (%) / 100

Monthly Interest = (Simple average value of car during lease) x Interest Rate (%) / 1200

Monthly Interest = [(Adj Cap Cost + Residual) * 1/2] x Interest Rate (%) / 1200

Now group the last 3 factors (1/2 * Interest Rate (%) / 1200) and you just get MF. (MF = Interest Rate (%) / 2400)

Monthly Interest = (Adj Cap Cost + Residual) x MF

The formula is somewhat deceiving as part of the average value calculation is built in to the MF. Intuitively you'd think you'd be paying interest only on the depreciation, rather you are paying interest on the average value of the car while it depreciates.

This brings up a good point - while high March residuals are good for a lower depreciation component to monthly lease payments, they are a doubled edge sword in the sense that they increase the average value of the car during the lease - hence higher interest. So if you have the cash on hand, by all means max out your security deposits. March residuals + max MSDs = amazing lease opportunity.

shaunole 03-10-2015 06:38 AM

That's interesting. I would think the interest would be based on the Adj Cap Cost as that factor already includes the Residual, doesn't it? In other words, it appears that I'm accounting for the Residual twice when multiplying it by the money factor.

I think I'm negotiating at a great time with these higher residuals. I've been reading a lot on MSD vs Down Payments (Cap Cost Reduction) as well and it looks like I need to remove the down payment in favor of as many MSDs as I can swing.

I appreciate the clarification and help dwlink!

dwlink 03-10-2015 08:05 AM

Quote:

Originally Posted by shaunole (Post 8956213)
That's interesting. I would think the interest would be based on the Adj Cap Cost as that factor already includes the Residual, doesn't it? In other words, it appears that I'm accounting for the Residual twice when multiplying it by the money factor.

I think I'm negotiating at a great time with these higher residuals. I've been reading a lot on MSD vs Down Payments (Cap Cost Reduction) as well and it looks like I need to remove the down payment in favor of as many MSDs as I can swing.

I appreciate the clarification and help dwlink!

Adjusted cap cost is simply Negotiated price - down payment towards cap cost reduction - trade in credit towards cap cost reduction

Residual has nothing to do with cap cost, rather it is a fraction of the MSRP based on the lease term and mileage

bobbydigital 03-10-2015 04:52 PM

Quick question on the worksheet as I'm trying to calculate the first lease "deal" a dealer has made me:

- My employer entitles me 4% off MSRP and an additional $1500 discount (which I'm told is a BMW NA, not a specific dealer offer). Do I just plug the total $ amount into the "Dealer incentive" line (Cell C13) or do I factor that implicitly as part of the "Sales Price" (Cell C14)

dwlink 03-10-2015 05:46 PM

Quote:

Originally Posted by tmchow (Post 8957646)
Quick question on the worksheet as I'm trying to calculate the first lease "deal" a dealer has made me:

- My employer entitles me 4% off MSRP and an additional $1500 discount (which I'm told is a BMW NA, not a specific dealer offer). Do I just plug the total $ amount into the "Dealer incentive" line (Cell C13) or do I factor that implicitly as part of the "Sales Price" (Cell C14)

No - dealer incentives are only for sales incentives offered to the dealer for them to lower their cost of ordering the car. They may choose to pass that discount on to you and that would be reflected in the sales price as well. For example, if the dealer is offered a $2k incentive towards premium package discounts and chooses NOT to pass that discount on to you, what you'll end up seeing is an additional $2k in negotiating power.

As for your deal, all you'll do is take [(0.96*MSRP) - $1500] and enter that as your sales price in C16. I'm gonna just assume the MSRP is 50k which would put those combined discounts around 93% MSRP which means you can probably shave a few $100 extra off.


All times are GMT -7. The time now is 07:47 AM.

Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
vBulletin Security provided by vBSecurity v2.2.2 (Pro) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
© 2001- VerticalScope Inc. All rights reserved.