Calculating inflation in a spreadsheet

[flex727]

What is the proper way to account for inflation in a spreadhseet? Say I have a row for each month from today through 2050. I want to assume a constant 3% inflation rate. How do I enter the formula for each month that calculates that correctly?

I guess what I'm really asking is when the government reports an inflation number, how should that be compounded when looking forward - daily, monthly, or annually?

[Musicat]

Pretty simple, if I understand you correctly. Increase the value of each monthly cell by 1/12 of the annual interest rate compared to the previous cell (compounding).

I can't say if that is the way the government does it, but it will work mathematically. Rounding and the number of significant digits should be taken into consideration if you are spanning a whole lot of time.

Daily compounding will give you the greatest increase over time, monthly a little less, and annual, the least. Doesn't make much difference over a year; over a century, much more.

[K364]

Excel has a function that can do it for you: "FV"

I can be used for investments (where you add money to the pot monthly or whatever) or simple compound interest.

[flex727]

Originally Posted by Musicat

Pretty simple, if I understand you correctly. Increase the value of each monthly cell by 1/12 of the annual interest rate compared to the previous cell (compounding).

I can't say if that is the way the government does it, but it will work mathematically. Rounding and the number of significant digits should be taken into consideration if you are spanning a whole lot of time.

Daily compounding will give you the greatest increase over time, monthly a little less, and annual, the least. Doesn't make much difference over a year; over a century, much more.

The problem is that over a long time period, monthly vs annual compounding makes a BIG difference. How do I determine which is the right way to go?

Another way to answer the question is: I purchase an item today for $100. That item, incredibly, increases in price in a way that perfectly matches the CPI. If I assume that, also incredibly, the CPI is exactly 3% every year for the next 50 years, what would I expect to pay for this item each month for the next 50 years?

[Freddy the Pig]

Originally Posted by flex727

Another way to answer the question is: I purchase an item today for $100. That item, incredibly, increases in price in a way that perfectly matches the CPI. If I assume that, also incredibly, the CPI is exactly 3% every year for the next 50 years, what would I expect to pay for this item each month for the next 50 years?

The price increase each month is the twelfth root of 1.03, minus 1, or 0.2466%.

[Musicat]

Originally Posted by Freddy the Pig

The price increase each month is the twelfth root of 1.03, minus 1, or 0.2466%.

But what Flex727 is asking is should the interest be compounded monthly or at some other time frame? What is the accounting standard?

I don't know what the gummit uses, but I'm sure someone will tell us, as long as this board stays working long enough. It's been pretty flaky in the last 2 days.

[flex727]

Originally Posted by Musicat

But what Flex727 is asking is should the interest be compounded monthly or at some other time frame? What is the accounting standard?

Yes! Thank you!

[Freddy the Pig]

The CPI uses annual compounding, so for a monthly equivalent you have to take the twelfth root.

[flex727]

Originally Posted by Freddy the Pig

The CPI uses annual compounding, so for a monthly equivalent you have to take the twelfth root.

Thanks for the response - when I put this in my spreadsheet, I end up with a 3% increase after 12 months, just as I should.
If had thought this through a little more carefully, I might have figured it out on my own.