Solved

Money Spent on Cigarettes over the years...

Posted on 2014-04-26
29
156 Views
Last Modified: 2014-11-06
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?

Thank you in advance for your help!
0
Comment
Question by:Geekamo
  • 7
  • 6
  • 5
  • +7
29 Comments
 
LVL 1

Author Comment

by:Geekamo
ID: 40024777
Actually, it was $6.25 per pack as my average.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 168 total points
ID: 40024780
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40024792
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
 
LVL 1

Author Comment

by:Geekamo
ID: 40024794
@ 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
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 166 total points
ID: 40024797
If you go by that simplistic value, then $8.94 * 365 days * 21 years = $68,525.1
0
 
LVL 1

Author Comment

by:Geekamo
ID: 40024800
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40024808
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
 
LVL 1

Author Comment

by:Geekamo
ID: 40024810
Dan,

It grew more rapidly here in the US as well.

Thank you for your feedback! :)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40024825
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40024829
Yup. And so we're 0.06% closer to an approximation :)
0
 
LVL 1

Author Comment

by:Geekamo
ID: 40024834
Haha! :)
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40024840
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
 
LVL 23

Expert Comment

by:tailoreddigital
ID: 40024841
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
 
LVL 1

Author Comment

by:Geekamo
ID: 40024848
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 23

Expert Comment

by:tailoreddigital
ID: 40024850
Congrats on quiting. :)
0
 
LVL 1

Author Comment

by:Geekamo
ID: 40024854
I appreciate that!  Thank you! :)
0
 
LVL 17
ID: 40024884
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
 
LVL 27

Expert Comment

by:aburr
ID: 40024922
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
 
LVL 24

Expert Comment

by:SunBow
ID: 40025174
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.

Maybe your having quit helped you to save your job.

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
 
LVL 27

Expert Comment

by:tliotta
ID: 40025374
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
 
LVL 17
ID: 40025495
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
 
LVL 17
ID: 40025496
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
 
LVL 17
ID: 40025504
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
 
LVL 17
ID: 40025577
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
 
LVL 17
ID: 40025583
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 40029966
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
 
LVL 4

Expert Comment

by:Stephen Kairys
ID: 40217244
Congratulations on quitting!! That is a tremendous accomplishment. :)
Steve
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40425775
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now