Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
How can I convert pre-2009 dollars to 2009 values?
Old 10-04-2009, 10:00 AM   #1
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Amethyst's Avatar
 
Join Date: Dec 2008
Posts: 5,889
How can I convert pre-2009 dollars to 2009 values?

OK, you Excel wizards out there, I am an Excel newbie and I have a question for you...

I am using Excel to analyze income from our 2 rentals from 2003-2009. Using the CPI for those dates [not saying whether I believe the CPI is correct or not] I would like to convert the 2003-2008 amounts to 2009 constant dollars.

I know the CPI for 2003 - 1 Oct 2009.

I know how to use Excel to project values for future inflation by using Present Value/(1+inflation)^year.

Can't quite master the math for reversing the process.

Any help would be appreciated.

Amethyst
__________________

__________________
If you understood everything I say, you'd be me ~ Miles Davis
'There is only one success to be able to spend your life in your own way. Christopher Morley.
Amethyst is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 10-04-2009, 10:29 AM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
haha's Avatar
 
Join Date: Apr 2003
Location: Hooverville
Posts: 22,386
I would put my data in columns, beginning date at top. Make a column for the monthly CPI index for any date. Assign most recent CPI index to a horizontally fixed cell -eg. c$5.

Multiply the data from any date ( month end?) by a ratio with today's index as numerator, and index of historical date as denominator.

In effect, you old data are being inflated by measured cpi inflation between the dates.

This way you can easily bring your adjusted data up to date, with a minimum of revision.

Ha
__________________

__________________
"As a general rule, the more dangerous or inappropriate a conversation, the more interesting it is."-Scott Adams
haha is offline   Reply With Quote
Old 10-04-2009, 12:06 PM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
Use the PV function under FORMULAS:
Attached Images
File Type: jpg Inflation adjusted dollars.jpg (32.3 KB, 7 views)
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Old 10-04-2009, 12:27 PM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
Ok, I read the OP twice, and I'm still cornfused...

EXCEL has both a PV and a FV function.

As you noted, FV = PV * (1+i)^n, where i = CPI, and n = difference in years.

Conversely, PV = FV/(1+i)^n
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Old 10-04-2009, 12:32 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Amethyst's Avatar
 
Join Date: Dec 2008
Posts: 5,889
HFWR,

Say I'm trying to convert the amount $3818 from 2003 dollars to 2009 dollars. The CPI for 2003 was 2.27%.
I have Excel 2007. With the NPER function, the fields go:
Rate
Pmt
PV
FV
Type

In which fields should I enter my values?

Thanks,

Amethyst
__________________
If you understood everything I say, you'd be me ~ Miles Davis
'There is only one success to be able to spend your life in your own way. Christopher Morley.
Amethyst is offline   Reply With Quote
Old 10-04-2009, 12:34 PM   #6
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Amethyst's Avatar
 
Join Date: Dec 2008
Posts: 5,889
Sorry, I'm obviously on drugs and don't even know it. Nobody told me to use the NPER function! Back and try again

Quote:
Originally Posted by Amethyst View Post
HFWR,

Say I'm trying to convert the amount $3818 from 2003 dollars to 2009 dollars. The CPI for 2003 was 2.27%.
I have Excel 2007. With the NPER function, the fields go:
Rate
Pmt
PV
FV
Type

In which fields should I enter my values?

Thanks,

Amethyst
__________________
If you understood everything I say, you'd be me ~ Miles Davis
'There is only one success to be able to spend your life in your own way. Christopher Morley.
Amethyst is offline   Reply With Quote
Old 10-04-2009, 06:25 PM   #7
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
Quote:
Originally Posted by Amethyst View Post
HFWR,

Say I'm trying to convert the amount $3818 from 2003 dollars to 2009 dollars. The CPI for 2003 was 2.27%.
I have Excel 2007. With the NPER function, the fields go:
Rate - CPI, but see my note
Pmt - $0
PV - 2003 amount
FV -
Type

In which fields should I enter my values?

Thanks,

Amethyst
The FV will be your answer in 2009 dollars.

Note: This calculation assumes a constant interest rate over the entire period. CPI is unlikely to be exactly the same every year... To be exact, you'll have to calculate one year at a time, using the yearly CPI.

Or, CPI Inflation Calculator
Attached Images
File Type: jpg cpi.jpg (24.6 KB, 2 views)
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Old 10-04-2009, 06:37 PM   #8
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
Amethyst's Avatar
 
Join Date: Dec 2008
Posts: 5,889
Still doing something wrong. When I apply the formula to $3818 earned in 2003, =3818/(1+0.03)^6 I get a "2009" value of $3197.51. Shouldn't the 2009 value of 2003 money be higher, since a dollar bought more in 2003?

I'm sure the solution must be obvious to everyone but myself...
__________________
If you understood everything I say, you'd be me ~ Miles Davis
'There is only one success to be able to spend your life in your own way. Christopher Morley.
Amethyst is offline   Reply With Quote
Old 10-04-2009, 06:42 PM   #9
Full time employment: Posting here.
 
Join Date: Oct 2007
Location: New York
Posts: 898
Don't use PV or FV functions. Use a simple ratio.

Say the rent paid in 2003 was 1000.

To express that in 2009 dollars multiply it by the ratio of the CPI in 2009 divided by the CPI from 2003.

For example say you're using 215 as 2009 CPI, and 180 as 2003 CPI.

THat 1000 rent expressed in todays dollars would be 215/180 * 1000 = 1194.
__________________
Money's just something you need in case you don't die tomorrow.
Maurice is offline   Reply With Quote
Old 10-04-2009, 06:44 PM   #10
Full time employment: Posting here.
 
Join Date: Oct 2007
Location: New York
Posts: 898
I see Haha steered you correctly. PV and FV are not appropriate for this calculation.
__________________
Money's just something you need in case you don't die tomorrow.
Maurice is offline   Reply With Quote
Old 10-04-2009, 06:57 PM   #11
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
Quote:
Originally Posted by Amethyst View Post
Still doing something wrong. When I apply the formula to $3818 earned in 2003, =3818/(1+0.03)^6 I get a "2009" value of $3197.51. Shouldn't the 2009 value of 2003 money be higher, since a dollar bought more in 2003?

I'm sure the solution must be obvious to everyone but myself...
Use FV = PV * (1+i)^n --> to convert 2003 dollars to 2009 dollars
FV = 3818 * (1+0.3)^6
FV = 4559 (rounded)

Use PV = FV / (1+i)^n --> to convert 2009 dollars to 2003 dollars
PV = 4559 / (1+0.3)^6
PV = 3818

IOW, to go backward, convert FV to PV. To go forward, convert PV to FV.

Good thing for our youngsters that I'm not a math teacher...
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Old 10-04-2009, 07:03 PM   #12
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
HFWR's Avatar
 
Join Date: May 2005
Location: Lawn chair in Texas
Posts: 12,964
What Ha and Maurice said...
__________________
Have Funds, Will Retire

...not doing anything of true substance...
HFWR is offline   Reply With Quote
Old 10-04-2009, 07:22 PM   #13
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
travelover's Avatar
 
Join Date: Mar 2007
Posts: 9,896
I use an online calculator like this one.

The Inflation Calculator
__________________

__________________
Yes, I have achieved work / life balance.
travelover is online now   Reply With Quote
Reply

Tags
excel, income, inflation


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
2009 Predictions clifp FIRE and Money 64 01-03-2010 09:50 AM
2009 retirees CuppaJoe Life after FIRE 57 09-16-2009 05:29 PM
Traveling in 2009 jIMOh Travel Information 57 03-04-2009 07:37 PM
Resolutions for 2009 freebird5825 Other topics 22 01-01-2009 03:56 PM
2009 Contribution Limits kyounge1956 FIRE and Money 13 10-19-2008 08:24 PM

 

 
All times are GMT -6. The time now is 11:27 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.