Monthly CPI (cost of living) in Spreadsheet

[prunerfly]

I'd like to create a formula that returns a monthly present cost of each past purchase/gift made that is adjusted for yearly cost of living (Consumer Price Index) increases. I plan to reference the Federal Reserve's yearly CPI averages. Seems imbedded "IF" formulas would play a part. These records would eventually span many years. Any ideas would be greatly appreciated.

[Barry]

I'd like to create a formula that returns a monthly present cost of each past purchase/gift made that is adjusted for yearly cost of living (Consumer Price Index) increases. I plan to reference the Federal Reserve's yearly CPI averages. Seems imbedded "IF" formulas would play a part. These records would eventually span many years. Any ideas would be greatly appreciated.

Hi pf,

I'm outside the US, so am not up on the details of the (US) CPI. My assumptions are that each year's CPI is referred back to a specific year in which the CPI was set to 100. If that's so, here's a plan of attack with 1993 set as the base year, and the ANNUAL result reported.

Row 1 (headings)

A: Item, B: Purchase price, C: Year of purchase, D: Index value (1993), E:( leave empty), F: 2000, G: =F1+1, then select G1..J1 and press command-R to fill the formula into the next three cells.

Row 2: CPI values. The values given are for illustrative purposes only, not the actual CPI for that year.

A, B, C and E: leave blank, D: 100, F: 125, G: 129, H: 133, I: 139

Rows 3 and 4: Left blank for the illustration.

Row 5: Data entry and calculations

A: Item 1, B: 2075, C: 2001, D: =B5*D$2/LOOKUP(C5,F$1..M$1,F$2..M$2), E: (empty), F: =$D5*F$2/$D$2 Select F5..M5 and Fill Right (command-R)

Click on the row 5 row header to select the entire row, then press shift-command-N to open the Number Format dialogue. In the dialogue, select Fixed and set Precision to 0 to show whole dollar values.

Results: For the values given above, you should see 1609 in cell D5 and the following results in cells F5..M5: 2010, 2075, 2139, 2236, 0, 0, 0, 0

The formulas:

D5: =B5*D$2/LOOKUP(C5,F$1..M$1,F$2..M$2)

The formula gets the purchase price (B5), multiplies it by the base index value (in D2), then divides the result by the index value in the year of purchase (determined by the LOOKUP function) to determine a base (1993) value for the item.

LOOKUP uses the Year values entered in F1..M1 and corresponding CPI values entered in F2..M2 as a lookup table. The function finds the Year of Purchase (entered in C5) in the first line of the table and returns the CPI value from the cell below theat year.

The $ symbol before the row number in cell references in this formula hold that row reference fixed when the formula is Filled Down to provide for additional itemsin the list.


F5: =$D5*F$2/$D$2

This formula gets the base year price (D5), multiplies it by the CPI for the year of the column containing the formula (F2) and divides it by the base year CPI (D2) to give that year's present cost.

The $ symbol in $D5 holds the column reference fixed as the formula is filled right., the one in F$2 holds the row reference fixed when the formula is filled down. in $D$2, both column and row references remain fixed on D2.

From the results, you will notice that values are calculated and displayed for years prior to the purchase year, and years where no CPI has been entered show a present cost of zero. To suppress both of these results (leaving the cell blank), make this revision to the formula in F5, then fill right.

F5: =IF(OR(F$1<$C5,F$2<1),"",$D5*F$2/$D$2)

Expanding the sheet:

To add more years:

Select G1..n1 where n= the last column you need to write a year in, then press command-R.

Select F5..n5 (same n as above), then press command-R.

To add more rows for items:

Always keep at least one row (at the end of the list) that contains the formulas, but is empty of recorded data (item, price and year of purchase). Select this row and as many more as you wish to add, then press command-D to Fill down.

Try this, then post back if you have further questions.

Regards,
Barry

[prunerfly]

Thanks Barry,

Your solution worked perfectly. I substituted the CPIs from the Federal Reserve:
<http://minneapolisfed.org/Research/data/us/calc/hist1800.cfm> which then gave me the results I needed.

I particularly appreciated your explanation of how the formulas produced the results.

Thanks again, PF