Tuesday, April 12, 2011

The Observations Inflation Calculator/Spreadsheet

This spreadsheet:
  • calculates the inflation rate between any two years
  • converts dollar values from one year to another using the CPI (Consumer Price Index)
  • calculates the change in the purchasing power of a dollar
  • and more.
NEW! Try my new interactive calculator. It does all of the above right on your screen. The spreadsheet below is not interactive, but contains additional capabilities not included in the calculator.

Note:  If you don't have spreadsheet software, see the following easy-to-use graphs: What Would $10,000 in 19xx be Equivalent to Today? What Will $100 be Worth in 10-20 Years?, and "Related Materials" at the end of this post.

The Observations Inflation Calculator/Spreadsheet

Note: Click on the screenshot below to expand it. The link to download the spreadsheet is at the end of the post.

using CPI data to calculate purchasing power and value of a dollar in 2012
The Observations Inflation Calculator/Spreadsheet

What is that in today's dollars?

That's a question my readers sometimes ask -- especially when I'm reporting financial data from long before they were born. Since intuition is often useless when comparing dollar amounts from different eras, I've developed a calculator/spreadsheet to help.

The spreadsheet above can help answer questions such as:
  • My parents' house cost $50,000 in 1970. What would that be in today's dollars?
  • My current salary is $40,000/year. What was the equivalent 1985 salary?
  • What was the inflation rate between 1929 and 1935?
  • What will $100,000 be worth in 20 years?
  • What rate of return do I need to grow $10,000 into $30,000 in 10 years?


What Was the Inflation Rate Between 1900 and 2012?

Given the start year (1900 in the example) and ending year (2012):
The line labeled "CPI" (Consumer Price Index) shows :
  • The CPI was 7.9 in 1900 (January), and 226.7 in 2012
  • The compound annual inflation rate between the start year and end year was 3.0%/year
  • Prices were 28.7 times higher in 2012 than in 1900
The line labeled "CY (current year) Inflation" shows:
  • Inflation was -2.4% in 1900; the inflation rate for 2012 is not available since the year has just started.

What Was The Inflation Rate Between 1977 & 1981?

You can calculate the inflation rate between "any" two years. For example, the inflation rate between 1977 & 1981 was approximately 10.4% per year. (Note: CPI data is available for the years 1900 through 2012 only.)

What Was $0.24 in 1900 Worth (Equivalent to) Today?

The Projected Sample Price line takes the price entered in the start year and converts it to the equivalent amount in the ending year, using the actual inflation rates. In the example $0.24 in 1900 represented purchasing power roughly equivalent to $6.89 in 2012.

The Retroactive Sample Price does the reverse. In this example, $1,000,000 in 2010 represents buying power roughly equivalent to what would have cost $34,840 in 1900. (Note: if you don't have spreadsheet software, see What Would $10,000 in 19xx be Equivalent to Today? for a way of approximating these results.)

What Was the Percentage Decrease in Purchasing Power?

Another way to look at this same data is in terms of the decline in purchasing power from the start year. In 1900, you could buy $100 worth of goods/services for $100. Because prices are now over 28 times higher, if you had put that C-note under a mattress in 1900, you could now use it to buy what would have cost you $3.48 in 1900! The impact of inflation has resulted in a 96.5% decrease in buying power. (Note: For more, see The Decline in the Value of a Dollar -- since 1900.)


Questions the Spreadsheet Can't Answer

The CPI measures changes in the general price level; it attempts to approximate changes in the purchasing power (value) of a dollar. It is not a precise measure, and, especially, it cannot be used to calculate changes in the price of specific goods or services. So, while a pound of butter cost $0.24 in 1900 (my example case), it does not follow that a pound of butter now must cost $6.89 (I pay $4.49). And, if the MSRP for a Toyota Camry is now about $20,000 that doesn't mean that you could have bought one in 1900 for less than $700 (for multiple reasons). (See article on CPI in related materials at end of this post.)

Prices for houses don't move in lockstep with prices for butter, cars or televisions. However, even when it can't give precise answers, the model can provide perspective. For example, if you (or your parents) bought a home for $50,000 in 1970 and it's now worth around $300,000, the gains are primarily attributable to inflation -- on average, all prices are close to 6 times higher than they were then.

Note: The remainder of this post is based upon the bottom, "What-If," section of the graphic and are slightly more complicated.

The "What If" Section: What if Inflation Had Been 4%?

Note: For a graph-based approach to next 2 questions, see What Will $100 be Worth in 10-20 Years?
"Projected 2"and "Retroactive 2" perform the same calculations as above, but using a hypothetical inflation rate that you supply instead of the actual inflation rate. For example, we see that had inflation been 4% instead of 3%, to buy what cost $0.24 in 1900 would now take $19.41 instead of $6.89.

For this part, since you're supplying the inflation rate, you can use dates prior to 1900 and greater than 2012. So, you can answer questions such as "What will $100,000 be worth in 20 years?" At 4% inflation, $100,000 now is equivalent to about $219,000 20 years from now.

What Will $100,000 be Worth in 20 Years?

The other way to look at the question posed in the previous paragraph is in terms of purchasing power in the "start year" -- again using your hypothetical inflation rate. This is helpful, though usually depressing, input into your retirement planning. For example, you can see that at 4% inflation, in 20 years the purchasing power of $100 is reduced by more than 50%. At that rate, in 2032 your $100,000 stash will have the buying power that $45,600 has in 2012. (Note: to approximate these results without a spreadsheet, see What Will $100 be Worth 10-20 Years From Now?)

Calculate the Inflation/Growth Rate -- Or, Rate of Return You Need

Given a price in the start year and a price for equivalent goods or services in the end year, the final section calculates the inflation rate between the two years. For example, if 1,000 widgets cost $50,000 in 1900 and and now they cost $900,000, the inflation rate for widgets was roughly 2.6%/year.

Using that same section you can also determine what rate of return you will need to grow $10,000 to $30,000 in 10 years; the answer is 11.6%/year. (For another way to do this, see What Will my $n,000 Bond/CD be Worth in 10 Years?.)

Other Uses: What Will a $10,000 CD be Worth in 10 Years at 3%?

There's nothing special about inflation -- a CD that compounds annually (i.e., you're re-investing your earnings) behaves exactly the same way. If you invest $10,000 in a CD, or the stock market for that matter, in 2012 and earn 3% per year for 10 years, you will have $13,439 in 2022 (assuming no taxes). (To do this without a spreadsheet, see What Will my Bond or CD be Worth in 10 Years?.)

Try it out.


Link to Spreadsheet

Here's the link to download the Inflation Calculator/Excel spreadsheet. If you have any problems accessing or using the model, see this link.

Related Materials

What Would $10,000 in 19xx be Equivalent to Today? convert between today & prior years' dollars.
What Will $100 be Worth in 10-20 Years?: convert between today & future years' dollars.
100 Years of Inflation History: Graph of yearly inflation rate since 1900; includes summary of impact of inflation on bonds, stocks, housing....
The Decline in Value of a Dollar: Graphs changes in purchasing power since 1900.
For more on how the CPI is calculated, and some of the issues, see this article.
Dow Compound Growth Calculator: Calculates growth/return rate between any 2 prior years -- e.g., between 1985 and 2000.  Similar to this spreadsheet.
For lists of popular posts see the sidebar; for an index of all posts, by subject area, see the header at the top of the page.

Data Sources for the CPI Spreadsheet:
Pre-1913: Robert Shiller "Irrational Exuberance" data
1913 forward: Consumer Price Index for All Urban Consumers: All Items (CPI-U) from the U.S. Department of Labor: Bureau of Labor Statistics.
Copyright © 2011.            Last modified: 2/18/2013

Share This Article

Delicious Bookmark this on Delicious To share via Facebook, Twitter, etc., see below.

Archives
using CPI data to calculate change in purchasing power and value of a dollar

3 comments:

  1. real scientific stuff that fails totally to answer the simpletons' inquiry. Being this clever (ref all the above) surely were simpletons such as I given a chart to access, a number of figures as options to enter then a result. Then your site may appeal to more folk.

    ReplyDelete
    Replies
    1. Kim,
      First, let me apologize for taking so long to reply. Unfortunately, my spam filters have been working overtime, so I just found your comment today.

      Also sorry if you were put off by this post. I'm not sure you noticed, but elsewhere on the site there are graphs that might be more suited to your taste.

      Based upon the feedback that I have received, some readers prefer the power and flexibility of the spreadsheets. However, I do understand spreadsheets are not everyone's cup of tea. As a result, I have been adding more and more graphs to highlight some of the key results from using the spreadsheets.

      In this case, one of the key graphs based upon this spreadsheet is What would $10,000 in 19xx be Equivalent to Today? (also see the links in the post listed under "Related Materials," and additional links within the post itself). I'll be adding at least one more graph fairly early next year -- probably in January or February.

      I hope this helps.
      Thanks for stopping by. Don't give up on the site yet!

      Delete
    2. Kim,
      I just ran across a great tool that will allow me to add interactive calculators to my blog! I've added one as a test, and plan to add more over the coming months.

      Delete

No spam, please! Comment spam will not be published. See comment guidelines here.
Sorry, but I can no longer accept anonymous comments. They're 99% spam.