Pen and Pencil Lease Payment Calculation

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.

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.

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

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?

Originally Posted by turpiwa

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

Originally Posted by Greg220

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?

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.

Originally Posted by knmol

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.

Originally Posted by knmol

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!

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

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

Originally Posted by knmol

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.

This is a really helpful spreadsheet and I'm using it to figure out my lease for an ED 2007 X3.

One question: How should I determine the "State Taxes" amount for California (cell D19 in your spreadsheet)? Is it simply the tax rate times the Total Monthly Lease Payment times the number of months? I don't think the entire tax payment is due at the time of signing in California. So, should I zero out the number in D19 and simply add the monthly tax to the monthly lease payment?

knmol - I have attempted to download the BMW EDcalcs file and am having some difficulty opening it. I am using Adobe Acrobat Reader ver 7.0 and am getting an error message that say's it is either an unsupported file format or the file is corrupted - it suggested it could be related to the file having been an attachment in an email and not decoded properly. Do you have any suggestions on how I could resolve the problem? I will be ordering a Euro Delivery 335i tomorrow and would really love to have your worksheet to run through the lease / finance numbers.

Also, does anyone have the latest Euro Delivery Dealer Invoice numbers for the 335i? The latest I've seen were from July and I understand they have since been revised.

Thanks in advance for the help.

Mike

Unable to unprotect the sheets.

Originally Posted by robertbroussard

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

Sorry so slow to respond.

If you still want help: The problem you are having is because of cell A40, which uses the function MROUND to round the payment up to the nearest $50. If you get that #NAME? error it's because you need to load the Analysis ToolPak add-in.

Here is how (copied from Excel help):
--On the Tools menu, click Add-Ins.
--In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
--If necessary, follow the instructions in the setup program.

Hope that helps! I can't attach it as a zip - that file extension is not allowed. But it doesn't seem necessary - the file is only 33k - very small.

Originally Posted by jd101

This is a really helpful spreadsheet and I'm using it to figure out my lease for an ED 2007 X3.

One question: How should I determine the "State Taxes" amount for California (cell D19 in your spreadsheet)? Is it simply the tax rate times the Total Monthly Lease Payment times the number of months? I don't think the entire tax payment is due at the time of signing in California. So, should I zero out the number in D19 and simply add the monthly tax to the monthly lease payment?

Yes, that is how I would do it. You could insert a line between 6 and 7 so that you could add the tax payment in there if you know the calculation to determine taxes - that way if you changed something that affected taxes (like the total negotiated cost, etc.) it would change the tax accordingly. We are in Minnesota and never figured out exactly how the taxes were calculated, but we chose to pay them up front so we didn't have to worry that our estimate of taxes would muck up the payment amount.

Originally Posted by simpsmk

knmol - I have attempted to download the BMW EDcalcs file and am having some difficulty opening it. I am using Adobe Acrobat Reader ver 7.0 and am getting an error message that say's it is either an unsupported file format or the file is corrupted - it suggested it could be related to the file having been an attachment in an email and not decoded properly. Do you have any suggestions on how I could resolve the problem? I will be ordering a Euro Delivery 335i tomorrow and would really love to have your worksheet to run through the lease / finance numbers.

You have to rename the file to an xls file rather than a pdf, and then open it in Excel rather than in Acrobat. It's not really a pdf file, but the site won't let me upload an xls file so I changed it to pdf. It appears that others have gotten it to work by changing the "pdf" to "xls". Hope that helps!

Thanks!
Great tool!

I'm so pissed.... frickin leaseguide.com has been consistently screwing up anything I give it. I'm pretty patient and I know it has it's bugs from time to time, but tonight it is royally screwed up. I guess I finally got what people had been talking about, when it screws them. I have used it consistenly for 6 months now and really no problems majorly... but the last couple days... big time.

Originally Posted by schley

I'm so pissed.... frickin leaseguide.com has been consistently screwing up anything I give it. I'm pretty patient and I know it has it's bugs from time to time, but tonight it is royally screwed up. I guess I finally got what people had been talking about, when it screws them. I have used it consistenly for 6 months now and really no problems majorly... but the last couple days... big time.

Jeff, we should probably rely on a solid spreadsheet or database that we fully understand and that could run without a connection to the net.

Also, if I remember correctly, the tool you are referring to is very restrictive... you cannot easily edit information after the fact--ouch!

FWIW,

Robert

http://www.autoleaseadvisers.com/cal...ubmit=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.

Jon,

Where can I find the 2007 E63 6 series pricing. I saw you posted it for the 3 and 5 series but not the 6. Also, do you know if in 2007 an ED 650cic is in or out of dealer allocation. I saw an old said that it was considered part of their allocation. Is that still the case and where can I find dealers who will negotiate an ED 6 series from European wholesale>

Thanks for your help, Rick

Hi webguy330i, sales tax should be added onto the down payment amount and reflected on the 'due at signing' field, correct?

Does anyone have the formulas for the Single-Payment Lease?

Anyone have the excel unlock code?
Thanks,
J

I took the Excel spreadsheet and made some updates:
Replaced MROUND with CEILING (doesn't require the Analysis ToolPak)
Added support for Tax Rate as monthly payments are taxed in CA.
Added some Money Factory/Interest Rate conversions.

I filled it out for a 2007 335i at which I'm looking.

Again, rename .pdf to .xls to avoid the attachment restrictions.

Thanks Ronzer...nice spreadsheet. Is the Use Tax something for California only?. I live in Flirida and I had not heard of it before. Thanks!

Originally Posted by pepiny2k

Is the Use Tax something for California only?. I live in Flirida and I had not heard of it before.

Same as sales tax, which is how Florida works.