Discussion Forum
1

# How to adjust for inflation excel?

For example, suppose that the average price of a commodity was \$100 before Covid. And suppose that the price fell to \$80 one year ago, because of the Covid recession. And now suppose that its price has returned to \$100.

By the standard way of calculating inflation, that commodity’s inflation rate is: \$100/\$80 – 1 = 25%

That’s a significant rate of inflation. But because the price has merely returned to its pre-Covid level, that 25% inflation rate seems overstated somehow.

But that’s not the end of the story, as this figure illustrates.

As Excel users in business, we can’t dismiss a cost increase so quickly. But we can look at it in different ways to try to get a better idea about the patterns of inflation that we’re facing.

One way to look at inflation in a different way is to extend the time period for which we calculate inflation from one year to two or three years. So, for example, we could calculate the Compound Average Growth Rate (CAGR) of prices over a period that begins before the Covid recession began.

Suppose, for example, that the cost of a commodity was \$75 three years ago, and that it’s now \$100. As I explain in How to Calculate BOTH Types of Compound Growth Rates in Excel, the three-year CAGR of the price is equal to the formula shown here. That is, the three-year rate of inflation in this example is: (\$100/\$75)^(1/3)-1 = 10% per year.

Another way to look at price increases is to compare prices that are only a few months apart. For example, it might be that until four months ago, prices hadn’t increased much at all. But now, in just a few months, prices have started to skyrocket—as the Dirty Dozen table above suggests.

That equation would look like this… …where the value for Number Months is the number of months between the Current Price and the Old Price.

[5]
Edit
Query
Report
Traquero
• Inflation = (158 – 150) / 150.
• Inflation = 5.33%
[3]
Edit
Query
Report
GENETICIST

Inflation adjustment or deflation is the process of removing the effect of price inflation from data. It makes sense to adjust only data that is currency denominated in this way. Examples of such data are weekly wages, the interest rate on your deposits, or the price of a 5 lb bag of Red Delicious apples in Seattle. If you are dealing with a currency denominated time series, deflating it will extinguish the fraction of the up-down movement in it that was a consequence of general inflationary pressure.

Before we get into the ‘How’ of inflation adjustment, let’s look at the effect that inflation adjustment can have.

The time series below represents the average yearly salary of all wage and salary earners in the United States from 1997 to 2017. The data shows a modest average year-to-year growth of roughly 3%.

When you adjust this data for inflation, the graph turns decidedly choppy:

What did we do to get this second graph? What we did was to take the Consumer Price Index, specifically the CPI-Urban Wage Earners and Clerical Workers: All Items 1982=100 published by the U.S. Bureau of Labor Statistics and we divided the yearly salary data with the CPI value for that year and multiplied the result by 100. For example for 1997, we divided the wage \$43615 by the CPI for 1997 which was 157.6 and multiplied the result by 100, to get the inflation adjusted wage (in 1982 Dollars) of 43617/157.6*100 = \$27674. This calculation was then repeated for each year to get the second plot shown above.

Here is another plot that illustrates the effects of inflation adjustment.

The blue line in the plot shows the percentage change in wages from year to year before deflation, the orange line shows the percentage change in ‘real wages’ after deflation, and the gray line shows the inflation for each year.

For the most part, the changes in the inflation-adjusted wages have tracked the changes in the non-adjusted wages. But in years with low inflation as compared to previous years (e.g. 2009 & 2015), the inflation adjusted wages have risen sharply, while in years where inflation has risen a lot as compared to the previous year (e.g. 2000 , 2008 and 2011), the inflation adjusted wages have taken a nose dive.

Now that we have seen a couple of examples of how inflation adjusted data looks like, let’s get down to the nuts and bolts of how inflation adjustment works.

As we have seen, you can adjust for inflation by dividing the data by an appropriate Consumer Price Index and multiplying the result by 100.

This is an important formula. Let’s tag it as Equation I. We’ll need to use it again soon.

There are two things you should know while using this formula:

Let’s inspect each one of these points in detail.

There are usually several kinds of CPI available and you should use the right kind for your category of data. For example the US Bureau of Labor Statistics (BLS) publishes a large number of price indexes. Following is a sample set:

Which index you use depends on what data you wish to deflate and what property of your data you wish to measure. Let me illustrate this point with two examples:

Example 1: Suppose you have a time series for the average yearly apple prices found across all urban areas of United States. If you wish to bring out the core-growth in the price of apples experienced by urban consumers in the US, after discounting the effect of the overall inflation experienced by urban consumers, you should use the CPI-All Urban Consumers: U.S. All items, 1982–84=100 or the CPI-All Urban Consumers: U.S. All items, 1967=100 to deflate your apple price data. If you make the mistake of using the very popular index CPI-Urban Wage Earners and Clerical Workers you will get incorrect results because this index measures the price inflation experienced by only urban wage & salary earners, not by all urban consumers.

Example 2: Say you wish to bring out the core growth in apple prices after cancelling out the effect of overall urban food inflation, you should use the index CPI-All Urban Consumers: Food and beverages in U.S. city average, all urban consumers, not seasonally adjusted to deflate your data. This will give you a measure of how much dearer or cheaper apples became w.r.t. to other food items and only for urban consumers. I’ll describe this particular use of CPI in more detail later.

Now that we have understood the importance of using the correct index and how to use it to get the adjusted values, let’s drill deeper into what the adjusted value is telling us. For this we’ll first need to understand the concept of CPI.

To know how to interpret the deflated values, one must understand what CPI is and how it is calculated. The technical definition of CPI sounds boring but here it is anyway:

To really understand what CPI is one must know how to calculate it, and this calculation is best illustrated by an example. So, let’s launch a mini-project to create a shiny new index. We’ll call it CPI Fictitious in Gotham City — All Items, not seasonally adjusted.

Our first task will be to calculate the yearly expenses of a fictional household in Gotham City.

We’ll also make a rather daring assumption: we’ll assume that our fictitious household’s consumption perfectly represents the consumption of all households in Gotham City. Armed with this assumption, let’s get down to the task of computing the household’s expenses.

The following table contains the breakup of the household’s yearly expenses among 8 categories measured for two consecutive years:

In this example, the market basket is the collection of goods and services that our fictional household consumes every year. For the fictional household in Gotham, the price of the market basket was \$42000 in 2018 and it was \$43260 in 2019, an increase of 3% over 2018 which we will attribute to inflation.

Notice that the market basket has been spread across eight categories. These are also some of the categories that the US Bureau of Labor Standards uses while calculating the various kinds of CPIs in the United States.

Now let’s recollect that we want to arrive at is an index and not the absolute dollar cost of the market basket. An index needs a base with which all future values can be easily compared. So we will arbitrarily assume that 2018 is our base year and we’ll set the value of the index in 2018 to 100 points. Let’s bring this out in the name of our index by renaming it to:

CPI Fictitious in Gotham City— All Items, not seasonally adjusted, 2018=100.

We can now calculate the value of CPI Fictitious in 2019 as follows:

Thus, value of index in 2019 = \$43260/\$42000 * 100 = 103.

In general:

Suppose the price of the market basket was measured in 4 consecutive years : 2020 to 2023 and was found to be \$44400 in 2020, \$46200 in 2021, \$43800 in 2022, and \$45240 in 2023.

Then our made-up index takes on the following values in those years:

In 2020, index value = \$44400/\$42000 * 100 = 105.7143

In 2021, index value = \$46200/\$42000 * 100 = 110

In 2022, index value = \$43800/\$42000 * 100 = 104.2857

In 2023, index value = \$45240/\$42000 * 100 = 107.7143

Following is the resulting table of values:

Once you have the CPI data, calculating year-to-year inflation is very easy. Here is the formula:

Using this formula, we can see that Gotham City households experienced the following inflation from 2019 to 2023.

Now let’s look at the effect of inflation on just one of the items in the market basket — say a bowl of mulligatawny soup .

During the time frame 2018–2023, suppose mulligatawny soup showed the following price trend:

Clearly some of the inflation in the price of soup was because of the general inflationary pressure in Gotham’s economy during those years. We saw that Gotham experienced an overall inflation of 3% in 2019. We’ll assume that the cost of mulligatawny soup must have also increased by at least that much in 2019.

Let \$X be the cost of soup in 2018. If after 3% inflation the cost became \$3.26 in 2019, then \$X * 103/100 = \$3.26. So the cost of soup in 2018 ought to have been:

\$3.26*100/103 = \$3.16505

So \$3.16505 is the cost that soup ought to have had in 2018 for it to have cost \$3.26 in 2019 after a 3% price rise. Another way of interpreting the value of \$3.16505 is to say that this is how much soup cost in 2019, but in 2018 dollars.

We know from the table that \$3.08 is how much soup cost in 2018 in 2018 dollars. So now we can do an apples-to-apples comparison of two costs: \$3.08 and \$3.16505 because both of them are expressed in 2018 dollars.

We can now calculate the intrinsic inflation in the cost of soup in Gotham in 2019 after discounting the overall inflation from 2018 to 2019 as follows:

Similarly, the price of soup in 2020 can be adjusted to 2018 dollars by dividing it by the amount of inflation that Gotham experienced from 2018 to 2020.

To calculate this inflation amount, recollect equation (III): the formula for inflation. We’ll reproduce it below:

We can re-purpose this formula to find the inflation rate in the current time period as compared to the base year (2018). Let’s work through the math:

Remember we said we’ll revisit Equation (I)? Here it is again:

We can express Equation (IV) in terms of (I) by expressing the denominator of Equation (IV) as follows:

Now referring back to Equation I, CPI for 2020 is the index value and CPI for 2018 is always 100 since its the base year. Therefore,

Which is the same as what we got before using a different but equivalent formula.

Either way, we now have the cost of soup for both 2019 and 2020 adjusted to the same base i.e. 2018 dollars. Therefore like we did before, let’s compare the real percentage change in the price of soup from 2019 to 2020 after discounting the effect of overall price inflation. This ‘core’ change in price is:

We can now state the general formula for calculating the real (intrinsic) change in the price of an item with respect to a CPI of interest:

The following table shows the inflation in soup price in Gotham in the years 2019 through 2023 before and after adjusting for overall inflation.

That was a healthy dose of math. Let’s take a break and enjoy some soup before ploughing ahead.

Our fictitious CPI example has served us well. So far it showed us how to calculate the index, how to calculate inflation in the index, how to interpret inflation adjusted values and how to calculate the intrinsic growth of an item’s price w.r.t. an appropriate index.

But now, at the risk of deflating the interest (and egos) of all us Batman fans, we must return to reality.

Each year the BLS in the US, (or the corresponding government agency in other countries) interviews thousands of households across different financial strata (low, middle, high income), and different professions, in order to gather data about their spending habits. The result of this exercise is the calculation of dozens of smaller, more focused indexes. These indexes are then aggregated to form several top-level CPIs using a system of weights. The exact calculations can get complicated, but the concept is simple. You want to find out how much money the ‘average’ household spends on goods and services and then index that amount to some arbitrary base year. Then repeat this calculation each month, quarter and year to get a sense for how much more expensive (or cheap) the cost of living has become as compared to the previous time period.

If you are training a model, your training will not fail if you do not deflate your currency denominated data. At the same time, you should consider deflating it because doing so will remove the portion of the ‘signal’ from your data that is due to general inflationary pressure. Along with deflation, you should also consider one or more of other transformations such as the log transformation (which makes the trend linear), seasonal adjustment, and differencing. All these operations will remove the corresponding portions of the signal from your data. What will remain is the residual trend that your training algorithm can now focus on. Plus of course there will be the noise —which, your algorithm will have to learn to ignore!

Happy deflating!

Wages and salaries by Occupation: Total wage and salary earners (series id: CXU900000LB1203M). U.S. Bureau of Labor Statistics

PREVIOUS: Estimating The Range Of A Population Parameter: A Guide To Interval Estimation

[1]
Edit
Query
Report
Revenue

You’re in the right place. In this article we will demonstrate how you can calculate the future value with inflation in Excel with elaborate explanations.

Before going into the calculations, I will introduce you to several terms like:

The prices of things go up and this is called inflation. Deflation is the antonym of inflation. The prices of things go down in the deflation period.

In the following image, we are seeing the inflation and deflation picture of the USA for the last around 100 years.

From the year 1920 to 1940 (20 years), deflation occurred more than inflation. From there, inflation dominated. So, most of the time, we see the prices of things are going up.

Suppose, you have \$100 cash today. And the projected inflation for the next 1 year is 4%. If you still hold the cash (\$100), after 1 year, your purchase power will be lower (\$96) with that \$100 cash.

If we see the general pricing of things, the \$100 product will be priced now at \$104. So, with your holding of \$100 cash, you cannot buy the same product after 1 year that you could buy 1 year before.

So, inflation devalues the cash and increases the price of the product.

This is why holding cash is a bad idea in the investment world.

The future value of money can be thought of in two ways:

If you deposit your money with a bank, the bank provides you interest in your deposits. The rate, the bank provides your interest is called the Nominal Interest Rate. For example, if your bank provides 6% per year, then the nominal interest rate is 6%.

You can use this simplified formula to calculate the real rate of return:

Nominal Interest Rate – Inflation Rate = Real Rate of Return

To get a Real Rate of Return, you have to deduct the Inflation Rate from the Nominal Interest Rate (or your yearly return).

But the accurate formula is shown below:

Let me explain this concept with an example. Suppose, you have invested \$1000 in the money market and a got 5% return from there. The inflation rate is 3% for this period.

So, your total money is now: \$1000 + \$1000 x 5% = \$1050.

But do your purchase power the same as before? Say, you could buy a product for \$1000, now its price is \$1030 (with 3% inflation).

How many of these products you can buy today?

\$1050/\$1030 = 1.019417476.

So, your REAL purchase power has increased from 1 to 1.019417476.

In % it is: ((1.019417476 – 1)/1)*100% = 0.019417476*100% = 1.9417%

We can reach this percentage also using this formula:

(1.05/1.03)-1 = 1.019417 – 1 = 0.019417 * 100% = 1.9417%.

We shall calculate the future value with inflation in more than one way:

You have some investible money, and you want to invest the money with the following details:

Steps

In the next step, we are going to implement a method incorporated with a regular deposit. Because of the deposit, the future value calculation will be slightly modified compared to the previous method.

In this example, I am showing a scenario with the following details:

Steps:

[0]
Edit
Query
Report
Petrochemical Engineer

You can get inflation data going back to 1913 from the U.S. bureau of Labor Statistics. There’s an xlsx file there that I’m going use that will be the source for my calculations.

Once in Excel, you’ll see the data is neatly formatted by both year and month:

This data will get updated so over time you may want to get the latest figures so that your calculations are as accurate as they can be. The data has 1st half and 2nd half numbers but one thing I will do is also add the 12-month average. I’ll add a new column so that it just averages the values. In most cases, you’re probably just going to want to compare data from one year to another.

Next, I’ll convert the data into a table. To do this, click anywhere on the data set and under the Insert tab, click on the Table button. Excel should auto-detect the range but if it doesn’t, you can adjust it. In my template, I’ve named this table tblInflation. It includes the average, which will auto-update as new data is included.

The next step involves creating the inputs, doing the lookups, and then calculating the value. There are three inputs I’ll set up: the base value, base year, and the calculation year. The base year and value will act as the starting points and will convert to a calculated value based on what the calculation year is.

To determine the impact of inflation, I’ll use the base and calculation years to find their respective index values. To do that, I’m going to use a formula that includes INDEX & MATCH. Here’s what it looks like for the base year:

In the table, I’m extracting the value from the Average column and I will be matching the BaseYear (the named range for my input) against the values in the Year column. I’ll use a similar formula to extract the index value for the calculation year. I’ve put these index values next to my inputs but will hide them later:

In 1913, the index average was 9.9 and for 2022 it was around 286.8 (based on the data that’s available thus far). If I take the index value from the calculation year and divide it by the index value from the base year, that tells me the prices are approximately 29 times what they were back then. That comes out to a percentage change of 2,797%. This leads me to the next part of the equation: determining the new price, or as I’ve referred to it in my template, the ‘Calculated Value.’ The formula for this output is as follows:

In the case of the above inputs, it’s doing the following calculation:

This gives me a value of \$2,901.40. That means something that was worth \$100 in 1913 would be worth \$2,901.40 in 2022. I can also do the reverse calculation. I can work backward and answer the question of how much would something in today’s dollars be worth back then. To do that, I would enter the following inputs:

The calculated value is the \$100 that I started with in the previous calculation.

My templates is complete and all that’s left at this point is just to add a header and modify some formatting: