Bimmerfest - BMW Forums - View Single Post - Pen and Pencil Lease Payment Calculation
View Single Post
Old 09-13-2006, 07:03 PM
knmol knmol is offline
Registered User
Location: Twin Cities, MN
Join Date: Jul 2006
Posts: 14
Mein Auto: Soon to be X3
Originally Posted by robertbroussard View Post
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?



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 View Post
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 View Post
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!
Reply With Quote