OspreyFE55

Badlands
Well-Known Member
First Name
Marc
Joined
Jul 17, 2020
Messages
84
Reaction score
165
Location
Navarre, FL
Vehicle(s)
2013 Smart ForTwo
Clubs
 
So I made some excel sheets to help me narrow down my choices when it came to picking my Bronco. So I wanted to share them with you. Keep in mind when looking at the sheets the only numbers you really need to focus on are the ones in the colored boxes on the bottom of the documents.

The 3 I have made are for the Base, BD and BL. You have the ability to adjust the options pricing along with sales tax, Dealer fees, Down payment and loan information (Loan Length & APR). You can also adjust the tax rate per your state or county (currently set at 6%).

Please let me know if you have any questions, issues or any improvements I can make to this document better. Again just something I threw together to help me out but wanted to share.





Advertisement

 

Attachments

Last edited:

wander

Badlands
Member
First Name
Terry
Joined
Jul 14, 2020
Messages
17
Reaction score
13
Location
Missouri
Vehicle(s)
1998 E250 Super Duty Sportsmobile
Awesome. Thanks!
 

PowPow

Black Diamond
Well-Known Member
Joined
Sep 29, 2020
Messages
160
Reaction score
240
Location
PA
Vehicle(s)
2018 Tahoe
awesome... but dude. 72 month loan... not a good idea!

https://www.nerdwallet.com/article/loans/auto-loans/5-reasons-say-no-long-lo

I'm not trying to get in anyone's pocket. your money is your money. but 72 month loans are not worth it man.

Plus I keep saying this, and it's like no one wants to believe me on the math. When trying to figure out invoice when you know the MSRP, it's divide by the percentage+1, not multiple by 1-percentage. Trying to figure out what markup is different than taking percentage away.

Let me demonstrate. Let's say you have $6k in options at MSRP and you want to figure out what the invoice is, and we're assuming a 5% markup.

Your equation:
6,000 * .95 = 5,700
now to check your math, if this was correct, 5700 * 1.05 would equal 6000. but it doesn't. it equals 5985.

Correct equation:
6000 / 1.05 = 5,714.29
check it: 5714.29 * 1.05 = 6000

Sure you might say it's only a 14 dollar difference. But as the options total gets higher, the difference gets higher, and this effects tax calculation, loan calculation, etc...

You could also argue it's 'close enough'. but if you're going through the trouble of creating a spreadsheet to compare costs, wouldn't you want it to be as accurate as possible?
 
OP

OspreyFE55

Badlands
Well-Known Member
First Name
Marc
Joined
Jul 17, 2020
Messages
84
Reaction score
165
Location
Navarre, FL
Vehicle(s)
2013 Smart ForTwo
Clubs
 
  • Thread starter
  • Thread Starter
  • #10
OP

OspreyFE55

Badlands
Well-Known Member
First Name
Marc
Joined
Jul 17, 2020
Messages
84
Reaction score
165
Location
Navarre, FL
Vehicle(s)
2013 Smart ForTwo
Clubs
 
  • Thread starter
  • Thread Starter
  • #11
awesome... but dude. 72 month loan... not a good idea!

https://www.nerdwallet.com/article/loans/auto-loans/5-reasons-say-no-long-lo

I'm not trying to get in anyone's pocket. your money is your money. but 72 month loans are not worth it man.

Plus I keep saying this, and it's like no one wants to believe me on the math. When trying to figure out invoice when you know the MSRP, it's divide by the percentage+1, not multiple by 1-percentage. Trying to figure out what markup is different than taking percentage away.

Let me demonstrate. Let's say you have $6k in options at MSRP and you want to figure out what the invoice is, and we're assuming a 5% markup.

Your equation:
6,000 * .95 = 5,700
now to check your math, if this was correct, 5700 * 1.05 would equal 6000. but it doesn't. it equals 5985.

Correct equation:
6000 / 1.05 = 5,714.29
check it: 5714.29 * 1.05 = 6000

Sure you might say it's only a 14 dollar difference. But as the options total gets higher, the difference gets higher, and this effects tax calculation, loan calculation, etc...

You could also argue it's 'close enough'. but if you're going through the trouble of creating a spreadsheet to compare costs, wouldn't you want it to be as accurate as possible?
The loan duration can be changed to what ever you want it to be (12, 36, 48, 54 or 17) and it will calculate a monthly price. 72 & 84 is just what was in there when I saved it. As for the MSRP to Invoice, I was just using the calculation I got from Stephens. I also used a couple calculators online and got the same numbers. I would love to know more about your explanation and advice you gave. I don't know why I like math but I love to learn something new everyday, so you have my attention. How did you come up with 1.05?
 

Boxer4

Outer Banks
Well-Known Member
Joined
Aug 8, 2020
Messages
313
Reaction score
479
Location
SC
Vehicle(s)
Pilot
awesome... but dude. 72 month loan... not a good idea!

https://www.nerdwallet.com/article/loans/auto-loans/5-reasons-say-no-long-lo

I'm not trying to get in anyone's pocket. your money is your money. but 72 month loans are not worth it man.

Plus I keep saying this, and it's like no one wants to believe me on the math. When trying to figure out invoice when you know the MSRP, it's divide by the percentage+1, not multiple by 1-percentage. Trying to figure out what markup is different than taking percentage away.

Let me demonstrate. Let's say you have $6k in options at MSRP and you want to figure out what the invoice is, and we're assuming a 5% markup.

Your equation:
6,000 * .95 = 5,700
now to check your math, if this was correct, 5700 * 1.05 would equal 6000. but it doesn't. it equals 5985.

Correct equation:
6000 / 1.05 = 5,714.29
check it: 5714.29 * 1.05 = 6000

Sure you might say it's only a 14 dollar difference. But as the options total gets higher, the difference gets higher, and this effects tax calculation, loan calculation, etc...

You could also argue it's 'close enough'. but if you're going through the trouble of creating a spreadsheet to compare costs, wouldn't you want it to be as accurate as possible?
The 5% is a rounding error in and of itself . I believe you will find no option invoice list cents. If fact I think depending on the option the market up varies.
 

TaiKoi

Wildtrak
Well-Known Member
Joined
Aug 22, 2020
Messages
201
Reaction score
485
Location
Arizona
Vehicle(s)
2007 Dodge Ram Laramie 5.9 Cummins 4x4
[/QUOTE]
That's awesome man, not sure why I never saw this. I built mine because I've been talking so much about the Bronco my wife now doesn't hear a thing I say anymore! So I had to make a colorful one so she could see my numbers! LOL.
I don't have a wife but same haha. I got to a point where I was just thinking of all the variations and doing different financials and whatnot so much that I had to make a spreadsheet. We are the same haha. I'm always down for more spreadsheets so thanks for sharing yours!
 

PowPow

Black Diamond
Well-Known Member
Joined
Sep 29, 2020
Messages
160
Reaction score
240
Location
PA
Vehicle(s)
2018 Tahoe
The loan duration can be changed to what ever you want it to be (12, 36, 48, 54 or 17) and it will calculate a monthly price. 72 & 84 is just what was in there when I saved it. As for the MSRP to Invoice, I was just using the calculation I got from Stephens. I also used a couple calculators online and got the same numbers. I would love to know more about your explanation and advice you gave. I don't know why I like math but I love to learn something new everyday, so you have my attention. How did you come up with 1.05?
Absolutely. 😁

1.05 is just 5% added onto one. So instead of taking a number and multiplying it by 5% (or .05) and then adding that to your whole number, just add the whole number in from the start. 1+5%(or .05) = 1.05.

a good example is tax. If you buy something for $100 and there's 6% tax, just multiple 100*1.06 instead of multiplying 100*.06 and then adding 100. Just a shorter way of doing it.

so in the example I gave above:
6000 / 1.05 = 5,714.29
check it: 5714.29 * 1.05 = 6000

6000 = the MSRP on options
1.05 = 1+5% markup (1+.05)
5714.29 = Invoice

It's just like the algebra we all learned probably many many many years ago...
Code:
Invoice * (1+Markup%) = MSRP
We know MSRP & we assume Markup % as 5%. So as we all learned, how do we solve this multiplication equation when missing what to multiply by? we bring over Markup & have it divide MSRP.
 
OP

OspreyFE55

Badlands
Well-Known Member
First Name
Marc
Joined
Jul 17, 2020
Messages
84
Reaction score
165
Location
Navarre, FL
Vehicle(s)
2013 Smart ForTwo
Clubs
 
  • Thread starter
  • Thread Starter
  • #15
Absolutely. 😁

1.05 is just 5% added onto one. So instead of taking a number and multiplying it by 5% (or .05) and then adding that to your whole number, just add the whole number in from the start. 1+5%(or .05) = 1.05.

a good example is tax. If you buy something for $100 and there's 6% tax, just multiple 100*1.06 instead of multiplying 100*.06 and then adding 100. Just a shorter way of doing it.

so in the example I gave above:
6000 / 1.05 = 5,714.29
check it: 5714.29 * 1.05 = 6000

6000 = the MSRP on options
1.05 = 1+5% markup (1+.05)
5714.29 = Invoice

It's just like the algebra we all learned probably many many many years ago...
Code:
Invoice * (1+Markup%) = MSRP
We know MSRP & we assume Markup % as 5%. So as we all learned, how do we solve this multiplication equation when missing what to multiply by? we bring over Markup & have it divide MSRP.
Thanks man, now it makes sense to me, appreciate it, learning has occurred and I will fix the spreadsheet to calculate that way.
 

Advertisement





 


Advertisement
Top