From the course: Google Sheets: Advanced Formulas and Functions

Calculate the incremental effect of inflation - Google Sheets Tutorial

From the course: Google Sheets: Advanced Formulas and Functions

Start my 1-month free trial

Calculate the incremental effect of inflation

“

- [Instructor] In the previous movie, I discussed the effect of interest on money. Where interest on an investment makes your money grow in value, the inflation makes your money worth less. In this movie, I'll show you how to calculate the effects of inflation in terms of your present-day buying power. Interest makes your money grow, while inflation is the opposite. It tells you how much prices increase, which means the same amount of money buys less than it did before. If you want to calculate the effect of inflation, we need to know two things. First is the amount of money you're starting with, and the second is the inflation rate. Here, I'm assuming we have a starting amount of 200,000 dollars, and inflation over a year of 2.1%. And my goal is to find out the buying power of my 200,000 dollars in terms of today's dollars, given the inflation rate that we have here. To do that, I need to click in cell B4 to create the first formula for the year 2017. Type an equals sign, and I multiply my value in B3. So I'll type B3 and then an asterisk, followed by a left parenthesis. One minus the value in B2. I'm going to copying this formula down, and I want B3 to change as I copy the formula down, so I'm looking at the previous cell in the column for my reference, but I want the inflation rate to stay the same. So I will press F4 to create an absolute reference to cell B2, so it won't change. Now, type a right parenthesis and enter, and we get 195,800 dollars, so we have lost 4200 dollars in value, and that result makes sense. 200,000 dollars multiplied by 2.1% is 4200, so our calculation is correct. Now we can see the effect over the years by copying the formula in B4 down to the remaining cells in this column. So I'll click cell B4 and move my mouse pointer over the bottom-right corner, where the fill handle or blue square is. When my mouse pointer changes to a black crosshair, I know it's in the right place, and then I can double-click. Doing so fills in the formulas to the remaining cells in the column, with data next to them in column A, and I can see that, by the year 2025, my 200,000 dollars will have the buying power equivalent to today's 165,224 dollars and 41 cents. If you want to calculate the effect of inflation over a set number of years, then there's a different form of this formula that you can use. I'll click in cell E4 and my goal is to calculate the value of my 200,000 dollars after four years, given 2.1% inflation. So in E4, I'll type an equals sign, and the value that I'm starting with is in cell B3. And I'm going to multiply that by left parenthesis one minus the inflation rate, which is in B2. I'm only doing a single calculation, so I don't need to worry about making it a relative reference. Then I'll type a right parenthesis, and now I need to raise the quantity of one minus B2 to the power equal to the number of years. So that would be a caret, to indicate exponentiation, and then cell D4, which is the reference that contains the number of years. So why am I raising one minus B2 to the power of, in this case, four, the value of cell D4? The reason is that I am subtracting 2.1% from the value of this particular investment each year. So, as you can see, we're going from B3 to B4 and subtracting 2.1%, doing the same thing at year two, year three, year four, and so on. And, when I press the enter key to finish entering my formula, I see that I have the value of 183,721 dollars and 83 cents. And if I count down to one, two, three, four years, I see that's what I have for the fourth year, specifically, year 2020, in row seven. So there are two ways you can use these calculations, to see the trend of inflation over time, or to find out how much buying power you will have in a specific year.

Contents