How to calculate cpi in excel?

First we need to know how much of each good were purchased each year and what the prices were:

Hamburger             Jeans                     Movie Ticket

1984 Price             $.80                       $24                       $5.00

1984 Quantity       40                         1                           4

Then find total expenditure by multiplying price times quantity and adding them:

Expenditure = (.80 x 40) + (24 x 1) + (4 x 5) = $75

$32 +  $24 + $20 = $75

Now, 20 years later, we have new prices:

2004:  Hamburger           Jeans            Movie Ticket

$1.20                     $30             $7.00

Assume that the market basket (the amount purchased) stays the same in 2004 as it was in 1984 and the only thing that’s changed are prices.  Now what does that market basket cost in 2004?   Use 1984 prices and 2004 quantities.

(The BLS assumes the same market basket each year)

($1.20 x 40) + ($30 x 1) + ($7 x 4) = $106

$48   +  $30        +  $28    =    $106

To find the CPI in any year, divide the cost of the market basket in year t by the cost of the same market basket in the base year.

The CPI in 1984 =  $75/$75 x 100  = 100  The CPI is just an index value and it is indexed to 100 in the base year, in this case 1984.

To find the CPI in 2004 take the cost of the market basket in 2004 and compare it to the same basket in 1984:

CPI in 2004 = $106/$75 x 100 = 128.0

Now we can calculate the inflation rate between 1984 and 2004:

(128 – 100) /100    = 28/100 = 28%

So prices have risen by 28% over that 20 year period.  If the period was 1984 to 1985 we would say that inflation was 28% in 1985.

Now suppose that we know that the CPI in 1972 was 37.5 (where 1982 = 100) and that gasoline costs 36 cents per gallon.  The CPI in 2004 is 188.9.  The 36 cents is a nominal figure.  In 2004 dollars, what did gasoline cost in 1972?

Price in dollars of the original year x (Price level in 2004/Price level in original year)

36 cents x (188.9/37.5) = .36 x 5.03 = $1.81

So 36 cents in 1972 is the same as $1.81 in 2004 dollars.  Gasoline is about $1.95 today so it is only a 14 cents more expensive than it was 34 years ago.

You can find a calculator that does this automatically at http://minneapolisfed.org/Research/data/us/calc/index.cfm

---Art Woolf

February 25, 2005

Note: While creating this article, we are assuming the purchased quantity of products to be constant.

To calculate the Consumer Price Index between two years in Excel, take a sum of all the amounts spent on the basket of products over those two years. Then use the following formula to find the CPI ratio:

To find the CPI percentage, simply select the cell with the CPI ratio and hit the percentage symbol.

The same could be done over a range of years.

Eg. We have created a sheet of data with prices of a basket of products from 2011 to 2016. The total of the values of the products is summed in row 9 of the respective columns. The CPI for each year 2012 onward is mentioned in column K, starting from cell K3.

Since the summed up price for 2011 is in cell C9 and the summed up price for 2012 is in cell D9, the formula for the CPI ratio for 2012 would become:

Let us enter this formula in cell K3. Similarly, the formula for the CPI ratio for 2013 would become:

Just like this, we will create the list until we get the CPI ratio till 2016. However, this ratio is in decimal format. To convert the values to a percentage, select all of them, and hit the Percentage symbol.

The best graph type for CPI values is the bar graph. To create it, select the CPI percentages. Then go to Insert > Bar and select the type of the Bar Graph.

Change the size and location of the bar graph accordingly.

The graph created above is static in nature. If you wish to create a dynamic graph, you would have to use tables for the data.

Hope it helps!

• Consumer Price Index = ($48.65 / $43.00) * 100.
• Consumer Price Index = 113.14.