Straight Dope Message Board > Main Calculating inflation in a spreadsheet
 Register FAQ Calendar Mark Forums Read

#1
03-16-2008, 10:17 AM
 flex727 Guest Join Date: Mar 2002

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?
#2
03-16-2008, 10:33 AM
 Musicat Charter Member Join Date: Oct 1999 Location: Sturgeon Bay, WI USA Posts: 14,744
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.
#3
03-16-2008, 10:42 AM
 K364 Member Join Date: Nov 2001 Location: Edmonton, Alberta Posts: 1,632
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.
#4
03-16-2008, 12:34 PM
 flex727 Guest Join Date: Mar 2002
Quote:
 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?
#5
03-16-2008, 01:08 PM
 Freddy the Pig Guest Join Date: Aug 2002
Quote:
 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%.
#6
03-16-2008, 01:25 PM
 Musicat Charter Member Join Date: Oct 1999 Location: Sturgeon Bay, WI USA Posts: 14,744
Quote:
 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.
#7
03-16-2008, 08:43 PM
 flex727 Guest Join Date: Mar 2002
Quote:
 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!
#8
03-16-2008, 10:09 PM
 Freddy the Pig Guest Join Date: Aug 2002
The CPI uses annual compounding, so for a monthly equivalent you have to take the twelfth root.
#9
03-17-2008, 01:30 PM
 flex727 Guest Join Date: Mar 2002
Quote:
 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.

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     Straight Dope Chicago     General Questions     Great Debates     Elections     Cafe Society     The Game Room     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit Side Conversations     The Barn House

All times are GMT -5. The time now is 12:53 PM.