Bimmerfest - BMW Forums

Bimmerfest - BMW Forums (http://www.bimmerfest.com/forums/index.php)
-   Ask-A-Dealer (http://www.bimmerfest.com/forums/forumdisplay.php?f=20)
-   -   Pen and Pencil Lease Payment Calculation (http://www.bimmerfest.com/forums/showthread.php?t=60089)

Jon Shafer 05-06-2004 02:46 PM

Pen and Pencil Lease Payment Calculation
 
1 Attachment(s)
Pencil-and-paper worksheet.

BayAreaBMWFan 08-07-2004 09:52 PM

Sales Tax on Interest
 
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)?

Jon Shafer 08-07-2004 11:04 PM

Quote:

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.

SARAFIL 08-20-2004 10:09 AM

Quote:

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... :tsk:

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.

hector 08-27-2004 04:51 AM

Quote:

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

RocklinX3 08-31-2004 09:35 AM

Residualizable options handbook
 
Quote:

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?

webguy330i 09-08-2004 03:34 PM

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

Jon Shafer 09-08-2004 03:55 PM

Quote:

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!!!!


:-)))

webguy330i 09-09-2004 11:41 AM

Quote:

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

webguy330i 09-09-2004 11:45 AM

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 ;)

SARAFIL 09-09-2004 01:50 PM

Quote:

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.

webguy330i 09-09-2004 02:03 PM

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?

webguy330i 09-09-2004 07:23 PM

Hey Sarafil, et al.-

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

webguy330i 09-09-2004 07:23 PM

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

webguy330i 09-10-2004 11:32 AM

v1.02 changes:

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

I'm taking feature requests folks! :p

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.

webguy330i 09-11-2004 08:19 PM

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

webguy330i 09-13-2004 04:19 PM

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

Pete Largo 10-10-2004 05:02 PM

Quote:

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?

Jon Shafer 10-10-2004 05:05 PM

Quote:

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...

SARAFIL 10-11-2004 04:09 PM

Quote:

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.

Pete Largo 10-11-2004 05:04 PM

Oh well, I tried :D

02330iSP 03-07-2005 02:27 PM

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!

webguy330i 03-07-2005 02:37 PM

Quote:

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.

glennmcgee 03-10-2005 09:27 PM

ok i know i must be stupid but...
 
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?
:dunno:

webguy330i 03-11-2005 08:53 AM

Quote:

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?
:dunno:

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.


All times are GMT -7. The time now is 08:31 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
© 2001-2011 performanceIX, Inc. All Rights Reserved .: guidelines .:. privacy .:. terms