(Updated**** Scroll down to BOTTOM posts and clink link to get to VERSION 3)
Hey all, just wondering if anyone would mind sharing feedback and giving my Lease Template a little run to make sure my formulas are all accurate. It was inspired by the Lease Calculator here on this wonderful site but it also includes a few more areas for users to add options and such which could be helpful when comparing different packages from different dealerships.
Sometimes you want all the details broken out. I also included descriptions of the formulas in some sections that could be helpful for some
good question, I got the Capitilized tax from the dealers quote BUT my previous version of the spreadsheet I had created, I had an extra calculation in there that simply multiplied the monthly payments by the lease months then x by state tax so it looked like
Monthly payments (408) x Lease Payments (36) x Tax Rate (6.625%) which = $973 but that number was coming up higher then my dealers quote of $911.11
So I still gotta figure out whats going with the tax calculator piece
but everything else seems to be lining up nicely with the Leashckr calculator and what little info the dealers are sharing with me…I can pretty much hack out their informationless quotes and fill in the blanks …please try it and tell me if its working
thanks that may be a good idea to further break out the lease…can u share examples of what would be pretax and postax incentives, not sure i know that part
This will vary by state, but typically something like a direct to dealer incentive (volvo allowance, lease cash, etc) that reduces the selling price up front is not taxed and a rebate (loyalty, conquest, etc) that is applied as a cap cost reduction/due at sale is taxed.
Possibly a bit of circular logic happening in there…?ie, capped tax increases the total cap cost which increases the payment which increases the capped tax which increases…
Agreed its not perfect…my first version had all the aquistion, dealer, gov and taxes at the due at signing section below…which worked fine, BUT, I wanted to be able to easily calculate the new adjusted Cap Cost of a car by rolling all those fees into final price of vehicle since most dealer will simply allow you to do that and just put the first months payment at signing.
So since most of my negotiations are based on just 1st month DAS, I thought it would be better to leave it into the Adjusted CAP section but to your earlier point, I think you have to have a precalculated tax total and add that as the Capitlized Tax OR you will run the risk of that circular logic calculation. This is why I took out the tax calculating formula. i had in the original.
Still looking for some more suggestions…can you try it to see if its calculating everything properly?
Im not sure what formula to add to figure that out… What I think your looking for is a field that allows you to adjust the money factor up or down by a certain degree or percent to quickly recalculate?
but as it is now, if you just obtain the inflated MF from the dealer, it will calculate the correct payments.
I calculate the rent charge based on the “deal” MF (in our example we are assuming it is marked-up.
I show the total rent charge as well as the total broken out between a. buy rate rent charge and b. marked-up rent charge.
The marked up rent charge is used to adjust my offer price so that I account for any cost above the buy rate.
When I ask the dealer to run our deal at the buy rate and he politely says “we always mark up the MF, unless you know the owner.” I just counter with my MSRP discount MINUS incremental rent charge and hope we find a winner price. I drop any more conversation about markeup.
wow glad u guys like it…let me know how its working. I can add the sales tax calculator easily, but then I would need to remove the line item of Capitalized taxes to offset. the good thing is that it will now be auto calculated for you BUT you would have to take that calculation and manually add it to the capitalized fees section if you want to roll it into your cap cost when negotiating pricing
+added tax calculator field
+added tax calculations monthly
+total tax calculations for term
-removed Capitlized taxes and replaced field with additional fees for anything that gets rolled into CAP
+visually highlighted signifigant negotiating fields (monthly payments, due at signing, total lease cost)
Please test drive it to make sure I didnt make any not calc mistakes
Oh yeah, the green cells are input fields that require information from either the user or the dealer
Version 3 is here…grab it, use it, let me know what you think . I tried to make it cleaner, and also added another tab that explains Leasing Terms for everyone that may need help
