# Money Spent on Cigarettes over the years...

Hello Experts,

I am working on a spreadsheet, which calculates \$\$ spent on cigarettes over the years.

This question isn't so much as how to write the formula (at least not yet), but more about - what is the proper way to calculate this.

When I started smoking 21 years ago - I was paying \$2.50 per pack.  When I quit smoking on 11/03/13, I was paying \$10.00 per pack.

I first got my average of those two values, which would be \$6.50 - but is that the value I should use to calculate the cost of cigarettes?

I understand this value won't be 100% exact, because we have no idea how much a pack was for each year I smoked.

So what is the proper way of calculating this, knowing how much they were when I started - and how much they were when I quit?

LVL 1
###### Who is Participating?

x

Older than dirtCommented:
In order to find the total you would need to have kept track of what you actually spent, because using \$6.25 assumes that you smoked the same number of packs each year and that the price changed only on (and every) January 1st of each year and by the same increment.
0

Author Commented:
Actually, it was \$6.25 per pack as my average.
0

IT ConsultantCommented:
As Martin says, calculating how much you spent it's tricky.

As an ex-smoker myself (smoked for 13 years, gave up 9 years ago) I just calculate how much I save each year to make me feel good.

In your case, if you used to smoke a pack a day, you're saving \$3650 every year :)

HTH,
Dan
0

Author Commented:
@ MartinLiss,

I smoked approx. 10 packs of cigarettes a week - which was true throughout almost all the years.

That would be approx. 1.43 packs a day. So if my average price was \$6.25 for 1 pack, then my adjusted pack would be 6.25 * 1.43 = \$8.94 per day.

I understand there are flaws in calculating this out, over the years with nothing being tracked.  But being that we only have the starting and ending price - is this my ONLY way of tracking this and being "somewhat" (and I use that word loosely) accurate?
0

IT ConsultantCommented:
If you go by that simplistic value, then \$8.94 * 365 days * 21 years = \$68,525.1
0

Author Commented:
Dan,

So would you agree, knowing the pieces of information we DO know - this is the most accurate way I'll be able to calculate this?
0

IT ConsultantCommented:
Using only the information in the OP that is the most accurate way.

Although "accurate" is a bit of a stretch, as the price grew more rapidly in the past 10 years (at least in Europe).
0

Author Commented:
Dan,

It grew more rapidly here in the US as well.

Thank you for your feedback! :)
0

Older than dirtCommented:
If you go by that simplistic value, then \$8.94 * 365 days * 21 years = \$68,525.1
In the vein of how many angels can fit on the head of a pin you probably should consider leap years so
\$8.94 * 365.25 days * 21 years = \$68,572.04
0

IT ConsultantCommented:
Yup. And so we're 0.06% closer to an approximation :)
0

Author Commented:
Haha! :)
0

IT ConsultantCommented:
To make the angels dance faster, in 21 years there were only 5 leap years.
So it's more like \$68,525.1 + \$8.94 * 5 days = \$68569.8  :)
0

Commented:
You might as well calculate it based on today's price.   \$10 today might be about equivalent to \$2.50 back then.    I would calculate it based on the price today and assume that's the value of what you smoked.

Did you try this,
http://www.cancer.org/healthy/toolsandcalculators/calculators/app/smoking-cost-calculator.aspx
0

Author Commented:
tailoreddigital,

Yes, I seen that.  And many others, but they are all calculating against today's price of cigarettes.  I'm not sure I'm a fan of calculating it that way, it's such a skewed way of looking at it.
0

Commented:
Congrats on quiting. :)
0

Author Commented:
I appreciate that!  Thank you! :)
0

Commented:
Calculating on todays price isn't all wrong as your/the average income will have increased.
So if you originally spent a tenth of your income on cigarettes and still do, then you can depress yourself by thinking what else you could have done with the money.
Much better to look at what you aren't spending. I have seen prices nearly double since I stopped about 8 years ago. I find it amazing that people find that amount of money to spend, I don't think I could afford it now.
0

Commented:
The procedure to use depends entirely on how accurate you want your answer to be.
Your procedure is completely correct for an estimate of what you spent.
The value of the time spent getting a better answer is probably not worth it.
0

Commented:
It is all fudged, so
I agree that were you to derive the total quantity, then apply average, this is best without knowing more.

I recall per pack @.25, 5 pack per day non-filtered habits, carton prices from 20 to 8 to well over 40. I don't see where you have start date or avg pack per day either, or inconsistent usage or salary, so minimally considering leap year is even more negligible fudge, at best you can just use round # years and a calculator.

Given avg habit, avg price, for using excel the application to salary would follow to using its average rather than presuming it rose at same rate. While you may have began with minimum wage working part time, the amount of wage increase will likely decrease over time, the cost increase of smokes had been influenced more than inflation by the lawsuits applied across the industry, and effects are regional (NY vs bible belt tobacco producers).

For fudging the overall ROI consider other implications such as for health and insurance and fuel and time. For example, vehicle insurance costs for some was reduced for non-smokers (thus cost for smokers). Where you buy by the pack, what happens when you run out? Hop in a car to get more? (maybe buy a soda or candy as well, but for one with a habit admit to the why you went out). That's time and money, cost of fuel, vehicle wear and tear. For assessing that I think there's specific numbers IRS permits based on miles that could be averaged and amount to much than a day a year. Possible cost/savings on residence for smoking, time away from work or event where not permitted. Where time is money, your time smoking (and nothing else) can be translated to savings/cost.

Were you light or heavy smoker? Did you also get some from others, give some to others? I suppose using an average of pack and a half a day could be a norm, and the 10 packs per week could be as justified, suppose doing 5 during the avg work week of five days and double that or more during idle time of weekend having three nights (Fri,Sat,Sun). For average, there's ten in a carton so that's a carton a week.

A lot of fudge factor any way you look at it, but your method is a good as any. even if 100% off either way, the exercise itself is worthy effort that'll give you a good "what if" for both the times of prior smoking and the present days savings - for what could have been bought before, what can be bought now. For the 10/week, fudge to 40/mo, find cost, what else can you do now having that much more available. Your \$10 * a 40 gets to \$400/mo (approx). Check out ads for cars on their deals and this can get to be more meaningful, more so now than back when.
0

Commented:
The question has multiple interpretations.

To determine what you actually spent, you could assume a general rate of increase that makes per-pack costs to rise from 2.50 to 10.00 in the 21st year. An increase of 7.1773% per year gets pretty close (depending on rounding.) Increases will be a little greater as each year passes due to "compound interest", but that's similar to how costs actually rose.

To determine some kind of comparative cost based on current per-pack prices, you might as well use the current price. Units of currency were worth more in terms of purchasing power back then, so 2.50 bought what 10.00 buys today. (Not quite, but it's a meaningful thought.)

To determine how much money you'd have today if you hadn't spent it on cigarettes, you'd have to decide whether or not you'd have simply spent it on something else. E.g., would you have bought a less efficient car and spent more on gas? Gone to more movies? Paid for more cable channels? Bought more expensive food items? Fancier shirts?

Lots of people think they'd have more money, but in reality they would've just spent it in other ways. Not all, but quite a few. Only you know your tendencies. (I'd probably spend.)

Tom
0

Commented:
That's an interesting savings idea for a non-smoker Tom. You estimate how many packd a day you aren't going to smoke and put that amount of money away in a bank account each month. Not only would you have a nice little nest egg, but after 21 years you would have an accurate answer to this question - you could check ypur deposits over the period and see what rate the price rose.
Actuall there may be figures available, at least for the average price per yea from organisations that track the retail prices and cost of living after each government budget.
0

Commented:
That's an interesting savings idea for a non-smoker Tom. You estimate how many packd a day you aren't going to smoke and put that amount of money away in a bank account each month. Not only would you have a nice little nest egg, but after 21 years you would have an accurate answer to this question - you could check ypur deposits over the period and see what rate the price rose.
Actuall there may be figures available, at least for the average price per yea from organisations that track the retail prices and cost of living after each government budget.
0

Commented:
I found this which contains a url of its source data, but unfortunately I can't grab that link on my phone. There may be an updated version containing more recent years.
http://tobaccofreekids.org/research/factsheets/pdf/0091.pdf
0

Commented:
From the table in that pdf:
Price in nov 93 was 1.69
Price in Jul 07 was 4.34

so at the half way point 1.69+( 4.34-1.69 )/2 a pack may have been 3.01.
Looking at the prices during 2000 the price was very close to that. I think you can assume that at least for those years that the price increase was steady and you should be ok to use your average price.
That does indicate that today's price should be around 5.66  so more up to date data is needed.
0

Commented:
Here is a graph showing a bit of a kick after 2008, it does look fairly straight over the period covered by that pdf.
http://www.tobaccoinaustralia.org.au/images/content/ch13taxation/13.3.4.jpg
0

Commented:
Another way of looking at it -
Calculating the internal rate for a present value (the starting value) of \$2.50 and a future value (the ending value) of \$10.00 over a period of 21 years equals approximately 6.82%. Multiply the present value by 1.0682 = \$2.67 to get the value at the end of year one, then that value by 1.0682 to get year two and continuing that for 21 periods results in a price at the end of year 21 of \$10.00 (actual rounding made this \$9.99). Add up each of the calculated prices per year and divide by 21 to get an average price of \$5.70. The average price multiplied by 7670 (the number of days in 21 years) then by 1.43 (number of packs per day) would create a total cost of \$62,533.84.

BTW, I smoked for more than 40 years, but quit in 1999 after suffering a heart attack. Glad you didn't have to wait for that wake-up call. Congratulations.
0

Technical Writer - ConsultantCommented:
Congratulations on quitting!! That is a tremendous accomplishment. :)
Steve
0

Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.