How can I convert pre-2009 dollars to 2009 values?

[Amethyst]

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

 

[haha]

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

 

[HFWR]

Use the PV function under FORMULAS:

 

[HFWR]

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

 

[Amethyst]

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

 

[Amethyst]

Sorry, I'm obviously on drugs and don't even know it. Nobody told me to use the NPER function! Back and try again

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

 

[HFWR]

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

 

[Amethyst]

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...

 

[Maurice]

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.

 

[Maurice]

I see Haha steered you correctly. PV and FV are not appropriate for this calculation.

 

[HFWR]

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...

 

[HFWR]

What Ha and Maurice said...

 

[travelover]

I use an online calculator like this one.

The Inflation Calculator