# My Updated Lease Calculator



## cozia83 (Dec 24, 2007)

I've made some changes, and hopefully it's more clear. 
https://carmen.osu.edu/pages/personal/150016/BMWLeaseCalculator.xls


----------



## DoubleL (Feb 20, 2006)

You should not have MACO/Training Fee adding to your total Cap Cost, that is technically incorrect (the offered price is the offered price). I would take that line out. 

I would also change line 4 to read MSRP (total, including options *and destination fee*)


----------



## archrenov8 (Feb 5, 2007)

DoubleL said:


> You should not have MACO/Training Fee adding to your total Cap Cost, that is technically incorrect (the offered price is the offered price). I would take that line out.
> 
> I would also change line 4 to read MSRP (total, including options *and destination fee*)


I made these changes to the spreadsheet. Now I am struggling with a few things, as I calculate my X3 lease. In cell B19, the formula shows a MF of =0.00255-B20

The current MF, according to the sticky, is .00270. Is that the base rate, to which you have to add up to .0040 for dealer MF markup? When I change the MF Dealer Markup in cell B20, it seems to effect only the rate shown in cell B19, but not the MF rate in cell B25, or the monthly payment, so what is the point?

Do I insert .00270 into that formula in cell B19? What seems to happen is that it is subtracting the dealer markup, so I am wondering if the .00270 is the maximum MF rate, which would then make sense, maybe.

Also, are the rates in the February Lease Rates sticky above allowing for a 15,000 mile lease, or how does that get factored in?


----------



## cozia83 (Dec 24, 2007)

DoubleL said:


> You should not have MACO/Training Fee adding to your total Cap Cost, that is technically incorrect (the offered price is the offered price). I would take that line out.


Hmm...ok, well from what I know, you either pay it upfront, or it gets added to the cap cost. I went by my lease offer and it matches up (minus a few cents).



> I would also change line 4 to read MSRP (total, including options *and destination fee*)


Good point. I will make updates after I check what the previous poster wrote.


----------



## cozia83 (Dec 24, 2007)

archrenov8 said:


> I made these changes to the spreadsheet. Now I am struggling with a few things, as I calculate my X3 lease. In cell B19, the formula shows a MF of =0.00255-B20


That is something I was calculating for someone else. Sorry about that. I will change it.
Anyway, for the black cells, you should write in your own numbers so it doesn't matter what I had there.



> The current MF, according to the sticky, is .00270. Is that the base rate, to which you have to add up to .0040 for dealer MF markup?


Yes. Except you don't have to accept full markup...and I think full markup is 0.0004 (3 zeros). A good markup to shoot for is 0.0002; better is 0.0001. These are negotiable, despite if someone says they aren't. Just move on to the next dealer if he won't negotiate on it. Also, do the MSDs.



> When I change the MF Dealer Markup in cell B20, it seems to effect only the rate shown in cell B19, but not the MF rate in cell B25, or the monthly payment, so what is the point?


This should be fixed once I took out the mistake above. B25 should change based on number of MSDs and dealer markup.



> Also, are the rates in the February Lease Rates sticky above allowing for a 15,000 mile lease, or how does that get factored in?


There's something at the bottom of the sticky that tells you how to change the residuals based on milage.

I will update the sheet and repost. Thanks for bringing my attention to these things!


----------



## cozia83 (Dec 24, 2007)

Fixed!


----------



## archrenov8 (Feb 5, 2007)

cozia83 said:


> That is something I was calculating for someone else. Sorry about that. I will change it.
> Anyway, for the black cells, you should write in your own numbers so it doesn't matter what I had there.
> 
> Yes. Except you don't have to accept full markup...and I think full markup is 0.0004 (3 zeros). A good markup to shoot for is 0.0002; better is 0.0001. These are negotiable, despite if someone says they aren't. Just move on to the next dealer if he won't negotiate on it. Also, do the MSDs.
> ...


Thank you for correcting the template. Just so I am clear though, does the Base Rate money factor include the maximum dealer markup of .0004, and thus is that why you were subtracting it in the initial spreadsheet?


----------



## cozia83 (Dec 24, 2007)

archrenov8 said:


> Thank you for correcting the template. Just so I am clear though, does the Base Rate money factor include the maximum dealer markup of .0004, and thus is that why you were subtracting it in the initial spreadsheet?


No, it doesn't include the markup. I was manipulating the numbers to compute a lease for someone (he just gave me the final MF, so I was just trying to recreate the lease without changing the fields). Please disregard that subtraction.

You should just fill in all the things in black with your corresponding numbers. Put the markup in the markup field, and the base MF in the base MF field.


----------



## cozia83 (Dec 24, 2007)

Updated again. The tax should be closer now.

I've also included a calculation to show how much you're saving by doing MSDs and showing the equivalent of what the APR would be on the MSDs you're putting down.


----------



## psucelticXI (Nov 19, 2007)

This is one of the easier to use spreadsheets out there, nice job so far.

If you could make an ED version, you'd be the man!


----------



## cozia83 (Dec 24, 2007)

psucelticXI said:


> This is one of the easier to use spreadsheets out there, nice job so far.
> 
> If you could make an ED version, you'd be the man!


Thanks. I keep making improvements as people tell me their experiences with it.
I don't have any experience with ED, so I may do that once I actually do an ED...now just 3 more years to wait.  My first BMW hasn't even been delivered yet!

BTW, I'm not a dealer, just an enthusiast.


----------



## psucelticXI (Nov 19, 2007)

cozia83 said:


> Thanks. I keep making improvements as people tell me their experiences with it.
> I don't have any experience with ED, so I may do that once I actually do an ED...now just 3 more years to wait.  My first BMW hasn't even been delivered yet!
> 
> BTW, I'm not a dealer, just an enthusiast.


lol... I'm considering doing ED very soon, I can't wait 3 years for the new ED spreadsheet 

Someone else wanna mod this spreadsheet for ED calculation?


----------



## cozia83 (Dec 24, 2007)

I think you just need to know the difference for invoice and MF, and you should be able to use the sheet as is? Maybe there's also a difference in tax? I don't know the details of ED, but I imagine you'd just have to input different numbers.

Take your shot at it, psuceltic!


----------



## cozia83 (Dec 24, 2007)

Updated Feb. 19, 2008. Now includes:

Doc Fee row
fix to MSD calculation
let's you say whether you are paying certain things up front or rolling them into the lease

Also, play with the number of MSDs to see how the last row changes. This is the equivalent of investing money at the % APR calculated in the 3rd column.


----------



## mali77 (Jan 30, 2008)

Works great!

Can you also add one more row for BMW incentives cell B8, B9 calculation will change to B9=B6+B7-B8


----------



## cozia83 (Dec 24, 2007)

I'll work on it a little later. Thanks for the suggestion!


----------



## nathand (Jul 17, 2006)

Wow this is awesome, Thank you for putting this together. I don't know much about a lease but with this I could check my sales rep's math!


----------



## rjdoc74 (Feb 1, 2008)

Awesome lease calculator! I just have a few questions:

1. How does the 12000 miles/year vs. 10000 affect calculation? 

2. Please excuse my ignorance, but what is the lease charge and depreciation? Are these negotiable?

3. The last line shows savings with MSDs vs. w/o MSDs, correct?

Thanks a lot!


----------



## cozia83 (Dec 24, 2007)

rjdoc74 said:


> Awesome lease calculator! I just have a few questions:
> 
> 1. How does the 12000 miles/year vs. 10000 affect calculation?


It affects the residual.



> 2. Please excuse my ignorance, but what is the lease charge and depreciation? Are these negotiable?


You should read this thread: http://www.bimmerfest.com/forums/showthread.php?t=260678
And this thread: http://www.bimmerfest.com/forums/showthread.php?t=146090

Depreciation is set by BMW FS, and the mileage affects it.



> 3. The last line shows savings with MSDs vs. w/o MSDs, correct?


Yep.


----------



## SeeYal (Aug 28, 2007)

cozia83 said:


> It affects the residual.
> 
> You should read this thread: http://www.bimmerfest.com/forums/showthread.php?t=260678
> And this thread: http://www.bimmerfest.com/forums/showthread.php?t=146090
> ...


I know if affect the % residual, but how is it calculated...??

I will probably pull the trigger on a 135i this summer with 25k miles per year on a short lease -30 or 36 months... and I know this mileage will hurt the residual bad...


----------



## psucelticXI (Nov 19, 2007)

Bump for a quality spreadsheet.


----------



## cozia83 (Dec 24, 2007)

psucelticXI said:


> Bump for a quality spreadsheet.


Thanks! I appreciate it!


----------



## Snareman (Nov 24, 2006)

Works great. Thanks!


----------



## SteveinBelAir (Dec 28, 2005)

Some states like MD, VA, TX, WV, IL and a few others charge sales tax on the negotiated price of the car - just as in a traditional purchase.

For example, in Maryland, if you lease a $40,000 car, you pay 6% of $40,000 or $2400 which has to be in the lease somewhere - either up front or split into 24/36 payments.

Most other states add a % to the monthly payment.

Makes a big difference.

Nice spread sheet though. And if someone can come up with a definite list of states that do this, it would be helpful to prospective leasees.


----------

