Pen and Pencil Lease Payment Calculation

Pencil-and-paper worksheet.

Since the lease worksheet breaks out the depreciation vs financing, why do dealers charge state sales tax on the interest portion (determined by the MF)?

Originally Posted by BayAreaBMWFan

Since the lease worksheet breaks out the depreciation vs financing, why do dealers charge state sales tax on the interest portion (determined by the MF)?

Yes, the rent charge is applied to the cap cost plus the residual.

Use tax is applied to the sum of those two figures.

Tax is also applied at maturity to any excess mileage.

Back when we had luxury tax (remember that?) in some states you
had sales tax charged on top of capitalized (financed) luxury tax...

That was double taxation.

Originally Posted by Jon Shafer

Yes, the rent charge is applied to the cap cost plus the residual.

Use tax is applied to the sum of those two figures.

Tax is also applied at maturity to any excess mileage.

Back when we had luxury tax (remember that?) in some states you
had sales tax charged on top of capitalized (financed) luxury tax...

That was double taxation.

Gas Guzzler tax is also an "option" (albeit a mandatory one) added into the final MSRP, and not listed seperately as a tax, so you pay tax on that too...

You'll also pay tax on the acqusition fee upfront (or as part of the payment of you capitalize the fee), and tax on any fees at the end of the lease. This is not BMWFS's doing, though... they're just complying with tax laws put in front of them.

Originally Posted by Jon Shafer

Yes, the rent charge is applied to the cap cost plus the residual.

Use tax is applied to the sum of those two figures.

Tax is also applied at maturity to any excess mileage.

Back when we had luxury tax (remember that?) in some states you
had sales tax charged on top of capitalized (financed) luxury tax...

That was double taxation.

actually jon, that was at least triple taxation, assuming that one had already paid income tax on the money used to purchase the car

Originally Posted by Jon Shafer

Pencil-and-paper worksheet.

In the worksheet (section 2B) for the residualizable options it mentions a handbook. Any chance you have the handbook also?

Would anyone find benefit in having this be 'interactive' with form fields and the like? I might put one together.

Originally Posted by webguy330i

Would anyone find benefit in having this be 'interactive' with form fields and the like? I might put one together.

Yes!!!!

Please do it!!!!


:-)))

Originally Posted by Jon Shafer

Yes!!!!

Please do it!!!!


:-)))

Ask and ye shall receive! It's not *exactly* the same though... different fields, different names...

I made it an excel doc, might make an HTML version later for compatibility. Can you check it over and let me know if you see any discrepencies or problems?

Right-click, save as...

http://images.330i.net/media/lease_calculator.xls

Oh and some notes:

- yes this is a reincarnation of the old skewl XLS doc I once found a long long time ago on roadfly
- you only need to change the numbers at the top under the "Adjust these..." section
- you can now account for paying tax up front or monthly, paying acquisition up front or monthly, and paying excess mileage charges up front/monthly/at termination
- I tested it quite a bit but I could have missed something. Everything seems to line up with the general leasing formulas and quotes I've seen
- YMMV

Originally Posted by webguy330i

Oh and some notes:

- yes this is a reincarnation of the old skewl XLS doc I once found a long long time ago on roadfly
- you only need to change the numbers at the top under the "Adjust these..." section
- you get output of payments with and without tax
- your down payment includes the acquisition fee (it is NOT calc'd into the monthly via the adjusted cap cost)
- I tested it quite a bit but I could have missed something. Everything seems to line up with the general leasing formulas and quotes I've seen
- YMMV

If you want to roll the acquisition fee in to the lease, you can delete it from the top, and add it to the capititalized cost section below.

Also, you could throw in a "high-mileage" lease adjustment if you wanted to. Mileage penalties at lease end are 20 cents for all models but the 7, and 25 cents for the 7. Upfront, the adjustment to the residual is 15 cents and 20 cents, respectively. If you buy extra miles above 15,000 per year, you can pay the lower price upfront, and if you don't use those miles, BMWFS will refund you for them at lease end.

All very good points thanks Sarafil. I'll make some alterations! I wasn't sure how the residual was affected by mileage paid up front, hence the post-lease cost calc.

But otherwise you think everything shakes out mathematically?

Hey Sarafil, et al.-

I updated the spreadsheet. Tell me what you think! See the link above.

I'm also going to add in a little section comparing prepaid vs. monthly vs. postpaid excess mileage costs.

v1.02 changes:

- updated residual percentage to reflect MSRP not cap cost
- added proper protection so you can't really mess anything up

I'm taking feature requests folks!

Download here:

http://images.330i.net/media/lease_calculator.xls

NOTE: if you get a message about macros being disabled, go to Tools > Macro > Security... and set the security level to "Medium". Then close Excel and re-open the spreadsheet file, and select "Enable Macros". That way the thing will be able to do the calculations required.

v1.03:

- added separate sheet for car configuration (lease calc. uses discounted msrp from that page as cap cost; uses msrp from that page as msrp)
- formatted lease calc sheet a bit

v1.04 updated:

- locked down cells you cannot edit, so now you can only select those cells that are changeable
- added additional option fields to car config sheet
- some 'behind the scenes' coding changes using named ranges, etc. (I'm still learning this excel stuff)
- colorized changeable cells

Originally Posted by JonS

Pencil-and-paper worksheet.

Thanks for the worksheet!

Can you post a copy of the 'Handbook' that the worksheet refers to by any chance?

Originally Posted by Pete Largo

Thanks for the worksheet!

Can you post a copy of the 'Handbook' that the worksheet refers to by any chance?

Unfortunately, such a handbook is no longer published per se, but
has been replaced by e-bulletins only found on the BMWFS Infobahn
for retail centers...

Originally Posted by Pete Largo

Thanks for the worksheet!

Can you post a copy of the 'Handbook' that the worksheet refers to by any chance?

I've got a hard copy of the Handbook that dates back to 2001 that I keep around for "quick reference", and it's 83 pages--- not a small file that I could easily "post" here. Otherwise, like Jon says, the info is available in the form of many bulletins/.pdf's on BMWFS Infobahn, the website for BMW dealers. You need to work for a dealer and have appropriate authorization to get access to it, it's not just available to anyone.

Oh well, I tried

Webguy, I d/led the spreadsheet and it's quite nice. However, I think there's a small problem with it--the popup help for the residual says to multiple the negotiated price by the residual percentage; I'm pretty certain that residuals are calculated against MSRP not negotiated price. Looking at the original PDF that Jon posted seems to corroborate that theory.

I'm not in the business, so I may be mistaken, but I thought I'd point it out and see what people thought. Thanks for the spreadsheet--it's really easy to work with!

Originally Posted by 02330iSP

Webguy, I d/led the spreadsheet and it's quite nice. However, I think there's a small problem with it--the popup help for the residual says to multiple the negotiated price by the residual percentage; I'm pretty certain that residuals are calculated against MSRP not negotiated price. Looking at the original PDF that Jon posted seems to corroborate that theory.

I'm not in the business, so I may be mistaken, but I thought I'd point it out and see what people thought. Thanks for the spreadsheet--it's really easy to work with!

Hey man thanks for that catch! Since that cell is a dollar amount only, it doesn't affect the calculations, only the comment box itself. So I updated it and re-uploaded.

Thanks again, glad it could help out.

when i load this [really cool] xls file it puts up this thing called "microsoft visual basic" and says Runtime Error 57121 Can't Exit design mode because Control 'mileage_upfront' can not be created

eh?

Originally Posted by glennmcgee

when i load this [really cool] xls file it puts up this thing called "microsoft visual basic" and says Runtime Error 57121 Can't Exit design mode because Control 'mileage_upfront' can not be created

eh?

Hmm, not sure about that one. Are you opening it in your web browser or in excel?

Open Excel and go to Tools > Options > Security

Click on Macro Security

What option is chosen? It should be at "medium" which will cause it to ask you if you want to enable or disable macros upon opening a spreadsheet which contains macros.