# Sticky  Pen and Pencil Lease Payment Calculation



## Chicago Jenny (Sep 17, 2005)

Has the calculator been upgraded anywhere to reflect states where you get bent over with sales tax on the full purchase price of the car?


----------



## Ågent99 (Jan 7, 2002)

I believe this is true for the state of New York?? Just click the NY button.


----------



## stiffy1 (Aug 26, 2005)

I've got a very simple excel spreadhseet for calculating lease costs - can someone advise on how I can upload it? Trying to attach as an image doesn't work.


----------



## Ågent99 (Jan 7, 2002)

stiffy1 said:


> I've got a very simple excel spreadhseet for calculating lease costs - can someone advise on how I can upload it? Trying to attach as an image doesn't work.


You can't upload it directly to 'Fest...it isn't one of the sanctioned files.  You need to upload to some other public server and provide us the link to that.


----------



## stiffy1 (Aug 26, 2005)

Give this a try. I took a picture of the spreadsheet, and added text boxes (colored in flesh tone) to provide the formula. Excel has the ability to compute lease payments, and I just added the other info. In Ontario, taxes are added to the lease payment, after everything else is calculated. I suppose this will require entering all the info into a spreadsheet, but it makes for an quick and accurate calculator. Good luck.


----------



## BayAreaBMWFan (Aug 8, 2004)

stiffy1 said:


> Give this a try. I took a picture of the spreadsheet, and added text boxes (colored in flesh tone) to provide the formula. Excel has the ability to compute lease payments, and I just added the other info. In Ontario, taxes are added to the lease payment, after everything else is calculated. I suppose this will require entering all the info into a spreadsheet, but it makes for an quick and accurate calculator. Good luck.


Can you change the name of the file to something sanctioned like a .jpg and then ask folks to save it as a .xls.


----------



## stiffy1 (Aug 26, 2005)

Here it is as a .jpg. Try to save it as an .xls file though I don't think that'll work. If not, I'd be glad to email it as an excel file to anyone interested.


----------



## stiffy1 (Aug 26, 2005)

Here's a link to download the spreadsheet - self explanantory once you get it. Drop your numbers in and voila!


http://files.ww.com/download.html?id=13360
*******>
********>
Let me know how it works.


----------



## Ågent99 (Jan 7, 2002)

bump


----------



## Ågent99 (Jan 7, 2002)

bump


----------



## knmol (Jul 9, 2006)

*Lease Payment Calc for European Delivery*

Here's another variation on an Excel spreadsheet to help calculate the lease payment. Would love feedback if anyone has questions or sees any errors, etc. This one is based on ED, though you can use it for US if you zero out the .0003 markup on the MF and use the proper invoice numbers.

It's named as a pdf file since I can't upload Excel - just save it and rename it as an .xls file before you open it and it should work.


----------



## wco81 (Sep 17, 2005)

The ED spreadsheet has some potential.

But I'm confused by some of the values already in there, like the base ED invoice price. Is that suppose to reflect the actual price or just some sample numbers?

Likewise, the prices for the options seem to be off. Like Premium Package would be $2000 or $2640, depending on whether it's a 330i or a 325i.

Also, not all options are listed. Didn't see Comfort Access or the Rear Power Shade.

One thing which might be nice is to be able to enter a sales tax rate and have it calculate that within the monthly payment and the driveoff costs.


----------



## knmol (Jul 9, 2006)

The values already entered, as it says in the notes in the upper right corner, were for a 2006 X3. 

I have now updated the sheet so that it's easier to enter your specific options and their prices (which can be found elsewhere in Bimmerfest, usually a sticky in the section for your model). I wanted to set up the sales tax as a calculation, but there are two problems - one is that different states tax differently (some on entire cap cost, others on payments, etc.) and the other is that I wasn't able to figure out what our tax (MN) was based on  (I forgot to ask when we went back to the dealer - hopefully that wasn't a place where they would mess around and hide profit for the dealer!)

I've attached the updated sheet. It would be nice if there was a listing of how each state calculates tax, so people could easily enter their taxes into this sheet.

Thanks for your comments/suggestions. Let me know if there is anything else that could be improved.

BTW, my husband and I used this on our laptop as we sat in the sales manager's office making our final deal on Saturday (yay! :banana: ) - it was really helpful to be able to run numbers ourselves right there in the office and clearly see what kind of deal we were negotiating. We pick up our X3 in Munich on August 18th!


----------



## Greg220 (Mar 31, 2005)

This is the best ED lease calculation tool I have seen so far, thank you for sharing it!

I have only two questions:

- what exactly does cell A39 mean ["Difference in MF - $ amt over 24 mos (if used .00007 multiplier)] and why is it a negative number if dealer's MF markup is 0?

- cell D20 ("Total Paid at Signing") includes the 1st lease payment (D19). However cell D8 says "Total of Lease Pmts (not incl. 1st pmt, nor 2nd that BMW pays)". So, if BMW pays the 1st and 2nd payments, why is it included in D19 and D20?


----------



## EvilM3 (Jul 12, 2002)

turpiwa said:


> How accurate is the lease calculator posted here - the numbers I'm getting back rom the dealer is significantly different than being plugged into the tool - no offense to the great work done - just if someone who has the knowledge to do a manual comparison it would be great. :thumbup:
> Wait - the posting below must be the issue.


I am getting different numbers aswell. The dealer is always higher. I've checked the car against ordering guide and I am getting the Z4 3.0si at invoice with prem, sport, HSeats, Xenon, and Nav. I am rolling the taxes and fees into the monthly payments and the dealer is quoting me $566 a month while the calculator is showing $538.


----------



## knmol (Jul 9, 2006)

Greg220 said:


> This is the best ED lease calculation tool I have seen so far, thank you for sharing it!
> 
> I have only two questions:
> 
> ...


Glad you are finding it helpful!

Re: A39 - that was a cell I had added because our dealer insisted that we only get .00005 per extra deposit, rather than .00007, so in our calcs we were looking at the money we would lose by getting only .00005 off instead of .00007 as dealer profit. (Basically, with 7 MSD's and nothing for dealer profit, we were getting an MF of .00176, so I took the difference between that and what the dealer was offering to figure out dealer profit.) I have updated the sheet by removing that cell and inserting cell A38 to show the $ profit resulting from the dealer markup of MF (just nominal $ over the length of the lease). Let me know if that helps and/or makes sense to do it that way.

Re: D20 - Looks like my wording was confusing. For D7, I meant that it did not include the first payment that you paid at signing, nor the second payment that you will not pay at all b/c BMW pays it. The first payment is included in D8 so I wanted to be clear I had not included it in D7 and therefore double counted it. And the second payment is not included in either one since you don't pay it at all. I updated the sheet to slightly change the wording in D7. Hope that helps!

I'll try to keep checking in here to see if there are any other issues.

To turpiwa and EvilM3 - not sure if you are refering to my spreadsheet, but if you are --
I recommend taking the spreadsheet with you on a laptop and getting the dealer to account for every single number that goes into it. That is what I did, and how I got this spreadsheet to where it is - when I was done going over each number with the finance person, she and I had exactly the same numbers input and were getting the exact same numbers on the back end. (She was using the BMWFS system, so it should be the same everywhere.) Also double check how taxes are handled - I did this lease in Minnesota, which requires the dealer to pay the taxes up front, though many people spread it out in the payments (we chose to pay it up front). Assuming you know how taxes are handled by your dealer, and you have the right calculation for taxes, everything else in this spreadsheet should give you the same numbers the dealer has, assuming they are telling you everything.


----------



## EvilM3 (Jul 12, 2002)

knmol said:


> To turpiwa and EvilM3 - not sure if you are refering to my spreadsheet, but if you are --
> I recommend taking the spreadsheet with you on a laptop and getting the dealer to account for every single number that goes into it. That is what I did, and how I got this spreadsheet to where it is - when I was done going over each number with the finance person, she and I had exactly the same numbers input and were getting the exact same numbers on the back end. (She was using the BMWFS system, so it should be the same everywhere.) Also double check how taxes are handled - I did this lease in Minnesota, which requires the dealer to pay the taxes up front, though many people spread it out in the payments (we chose to pay it up front). Assuming you know how taxes are handled by your dealer, and you have the right calculation for taxes, everything else in this spreadsheet should give you the same numbers the dealer has, assuming they are telling you everything.


I was actually referring the to spreadsheet that was posted before yours but I will give yours a try. THanks.


----------



## Greg220 (Mar 31, 2005)

knmol said:


> Re: A39 - that was a cell I had added because our dealer insisted that we only get .00005 per extra deposit (...)


Thank you for the clarification! It's very helpful and answers all my questions!


----------



## SwampDaddy (Jun 2, 2006)

Hey, thanks for sharing such a tool and for putting so much work into it.

Okay, I have a problem, I tried to change the "Amt Over ED Invoice" cell A16 (before noticing the directions) and got an error (#NAME?).

Have you documented all of the rules and calculations that this document is suppose to perform? I would like to better understand it.

BTW: can you upload this document as a zip archive?

Thanks,

Robert


----------



## SwampDaddy (Jun 2, 2006)

*HELP! I need help with lease calculations!!!*

I'm trying to get a good handle on a previous Texas-based lease deal so that I can apply that knowledge to a 335 (ED) deal that I will negotiate this month.

The previous deal (X3) has the following numbers from the lease agreement that I'd like to be able to plug into a spreadsheet or be able to compute via pencil and paper. Yet, my efforts so far do not yield the same numbers that the BMW dealer generated originally or the corrected numbers that BMWFS provided.

Please show me how to calculate such a lease--THANKS!

---------------------------------------
Trade Val. 6000.00
Due at Signing 8500.00
Monthly Payment 447.11 (Later changed to 437.38 via BMWFS).
Cont. Tot. Pmts. 17,457.25 (Later changed to 17,057.82 via BMWFS).
Disposition Fee 350.00
Cont. Total Pmts 23340.18 (total of all payments at lease end...)

Cap. Cost Red. 5370.84
Sec. Deposit 2500.00
Title Fees 34.00
License Fees 98.05
Doc. Fee 50.00

Gross Cap Cost. 43,482.13 (39,842 (Agreed Val.) + Other stuff)
Adj. Cap. Cost 38,111.29
Residual Val. 26,744.25 (should be 65%)
Depreciation, etc. 11,367.04
Rent Charge 6070.25 (Later changed to 5,690.78 via BMWFS).
Tot. Base M. Pmts. 17,437.29
Lease Term 39 Months

Agreed Value 39,842.00
Sales Tax 2115.13 (Remember this is TEXAS--So calc. accordingly)
MBP Contract 700.00 (Mechanical Breakdown Protection, err wheel thingy)
Aquisition Fee 825.00
Total 43,482.13
---------------------------------------

BTW: the MF should be .0024 or .0028.

I will attempt to attach my current Excel SS efforts... Please change the file ending from ".pdf" to ".zip".

Anyway, Many thanks!!!

Robert


----------



## Taktix1 (Oct 25, 2007)

I was using a lease calculator at leaseguide.com and these are the numbers it spit out:

335xi Coupe 
MSRP 50700
Neg. Price: 48000 (just guessing, includes all fees but not tax)
58% Residual
.00196 MF (lowest possible including 7 MSDs)
36 months
5% tax

Total came out to 701.63


Seems pretty high, am I doing something wrong?


----------



## oghowie (Feb 3, 2006)

Seems correct. Advertised lease payments are always low because a down payment is being paid. I assume you aren't putting any money down and that you are paying the bank/acquisition fee at signing? That may be why the payments seem higher than you are used to seeing.


----------



## 1985mb (Apr 2, 2008)

schley said:


> http://www.autoleaseadvisers.com/ca...use_tax=7.75&license=&stage=1&submit=Continue
> 
> After getting pissed off with leaseguide lease calculator I tried using everyone's excel calculator and either didn't work for me or wasn't user friendly enough for me. I went on a crusade to find an online lease calculator I could use that is reliable. Here it is..... works very well so far as I know today after a thorough testing. Use it or don't use it, doesn't matter to me but I'm just glad to be able to dump the leaseguide calc.


I compared this to what Ronzer posted in post #73.

By zeroing out the ED markup on the MF (and using US MSRP/invoice numbers), I can get the same results.

1 thing to note is that the ALA calculator automatically rolls the Acq fee into the adjusted cap cost.

Ronzer's one allows one to play around with options and is a bit more transparent


----------



## 528iDC (Mar 29, 2008)

Are there any lease calculators that determine the monthly payment amount by rolling in state taxes? Trying to find one that will work for leasing a car in Virginia, since unlike other states, Virginia doesn't charge a "use tax" but instead charges a 3% rate of the negotiated purchase price of the vehicle. It looks like most calculators just compute the "use tax."


----------



## 53 TURK (Feb 22, 2008)

ok now, i will need some help,
let's say the msrp is 52070 and the invoice price is 48500. If you say 1000 over invoice, does that mean the cap cost will be 49500, including the dmv, paperwork, maco, etc... ?and on top of this there is tax depenend on the state you live in of course. Am I wrong? Please let me know. Thanks to you all.


----------



## FastMarkA (Apr 21, 2008)

I'm doing my lease calcs, and have a question about the depreciation. I'm using this formula:

Cap Cost (Adj Cap (Selling Price + Doc + Process + Tax - Cap Rdxn) + Acq Fee)
Depreciation (Cap Cost - Residual)

Question is...when I use "Cap Cost" for computing the Depreciation, should aquisition fee be included or removed?

TIA.


----------



## ponchim (Jul 6, 2008)

webguy330i said:


> You got it.
> 
> Glad some people are finding this thing useful!!


Webguy-

Can you please email me the password as well? I'm trying to use the spreadsheet on a Mac and can't get the worksheet values (MSRP, negotiated price) to flow over to the lease calc sheet. If I can hard-code those values I can proceed with the calc. Email to [email protected].

Thanks in advance. Great and useful tool, btw.


----------



## enigma135 (Oct 4, 2008)

Hey i'm thinking of taking ED and leasing a 135 convertible going off the invoice pricing the car has come out to $43,990 (Premium, Sport, CA, PDC, NAV, Hi-Fi, iPod, Metallic Paint, Smartphone, Heated Seats, and Delivery and Handling) When i sat with a CA and he worked out payments on a 135 MSRP of $45,545 (selling price of $42,945 + 6% tax ($2576.70) + BMWFS Assignment 825.00 + tag and registration $270.00 + Cash Down Payment $2685.00. Making the total Capitalized Cost $43,931.70. Residual Value $31,881.50. 24 month Lease. Monthly Lease Charge .00295 ($223.65). Monthly depreciation $502.09 Monthly Payment of $725.74 with $3,410.74 due on delivery. Does that seem legit? Seems as though people are paying less per month on similar priced leases. Sorry for the novel post but am admittedly a noob when it comes to leasing, before i get completely flamed i have been reading up on it. Just hoping for some of your insights, since every lease option my CA has worked out has been right around $700 a month with 3-4K due on delivery.


----------



## mclaren (Jan 5, 2005)

What you call BMWFS Assignment $825 is the lease acquisition fee. It was $625 but may be $725 now ( I heard it was being raised Oct 1 ) so they marked it up $100 or $200. The Money factor is .00285 so they marked it up .0001. These are reasonable. The residual is 73% so if MSRP is $43,990 the residual should be exactly $32,112.70. So if you make 0 down payment, pay the tax $2576.70 and $270 and 1st month of $709.57 = $3,556.27 drive off and $709.57 per month. There is one problem and that is the security deposit. Unless you're a returning BMWFS customer you either have to pay a security deposit or a .00015 adder to the money factor. If you are a returning customer then it makes sense but then they marked up the money factor more because of the owner loyalty program. So the key is DON'T PUT A DOWN PAYMENT, do it like I outlined. I can't say if it is a fair deal because this particular car may be in demand but they are making about $2250 on the car, $100 or $200 on the lease acquisition fee, and a few 100 on the money factor markup. So the deal is strong money for the dealer and they structured it wrong. BTW, these numbers are for a 15k miles per year lease.


----------



## enigma135 (Oct 4, 2008)

Thanks for your help mclaren


----------



## BigMoneyWasted (Oct 30, 2008)

*password*

Anyone have the password to the lease calculation spreadsheet? It does not accept any numbers under Excel 2007, just prompts me to unlock the sheet. Tx.


----------



## gizmoboy (Feb 16, 2009)

BigMoneyWasted said:


> Anyone have the password to the lease calculation spreadsheet? It does not accept any numbers under Excel 2007, just prompts me to unlock the sheet. Tx.


Ditto.


----------



## xtac (Jan 29, 2009)

what's the password =/


----------



## BlueC (Jan 13, 2007)

xtac said:


> what's the password =/


Enable macros. Then you should be able to enter the info.


----------



## asaseaban (Aug 9, 2005)

See these link for more helpful and detail lease information and calculation
http://www.bimmerfest.com/forums/attachment.php?attachmentid=195594&d=1249255376

http://www.bimmerfest.com/forums/attachment.php?attachmentid=195595&d=1249255376


----------



## ooati (Feb 15, 2010)

Passwords to webguy330i's spreadsheet are as follows:
sheet password: 2b3b3b7b1b3b5 
book password: 3b7b5b1b3b1b7


----------



## Jon Shafer (Dec 15, 2001)

Back to the future. Here's there worksheet again.


----------



## 01Byte (Jun 22, 2003)

That paper guide is sooo yesterday. 

http://www.leaseguide.com/calc.htm

Just plug in the numbers and enjoy the ride.


----------



## Jon Shafer (Dec 15, 2001)

Arkady said:


> That paper guide is sooo yesterday.
> 
> http://www.leaseguide.com/calc.htm
> 
> Just plug in the numbers and enjoy the ride.


You missed the point of it! Some people like to do it. The old fashioned way.

What would happen if the computer crashed?

I've sent customers home in new cars, leases mind you, by candle light during power outages... 

TRY IT. IT CAN BE FUN!!! IT WILL ALSO STIMULATE YOUR NEO-CORTEX... :stickpoke


----------



## chrischeung (Sep 1, 2002)

Jon Shafer said:


> You missed the point of it! Some people like to do it. The old fashioned way.
> 
> What would happen if the computer crashed?
> 
> ...


I personally like it. I've used it a few times. You can fax it in advance to the finance person. They immediately know you understand all about leasing, and any opportunity for manipulation on either side goes away. It's easier to prove the accuracy of the pen and paper sheet than the XLS. It also teaches you the relationships between MF, cap cost, residual and depreciation and how each of those affect the payment. I still have the LRFS version somewhere.


----------

