Auto update cell price for inflation

[jeff2813]

Just wondering if its possible to update prices by inflation on specific dates. As an example, i'd like a loaf of bread at $1 to automatically adjust for a predetermined inflation rate on every Jan 1. I know i can make columns but I'd like only one specific cell to constantly update annually. I have a home inventory and id like the individual items to update yearly to reflect inflation. Thanks

 

[Toadstool]

jeff2813,

I've put something together for you using the FVSCHEDULE function which is described here: FVSCHEDULE

It's meant for calculating the future value of an investment with variable rates so I can't see why it shouldn't be used for future values with inflation rates. I'm not an accountant but it seems to do the job.

It needs to know what the inflation rates have been so columns F and G give annual inflation rates back to 2009 (and I kept them on Sheet1 but they could easily be cut&pasted to another sheet).

The calculation starts with the year of purchase (so it doesn't account for the difference between a purchase on 1st January or 31st December) and applies the inflation rates up to current year-1.

For example, you purchase a Dining Table in 2018 so only the 1.9% is applied to the initial value but for the Gold Rings purchased in 2013 they have 1.50% then 0.80% then 0.70% then 2.10% then 2.10% then 1.90% applied.

	A	B	C	D	E	F	G
Dining Chairs x 4
Dining Table
Rolecks Watch
Gold Ring Man's
Gold Ring Woman's
Painting "Dogs Playing Poker"
Painting "Scenic Scene"

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EDEDED]#EDEDED[/URL] "]Article[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EDEDED]#EDEDED[/URL] , align: center"]Date Acquired[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EDEDED]#EDEDED[/URL] "] Value[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EDEDED]#EDEDED[/URL] "] Value Adjusted
for Inflation[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D0CECE]#D0CECE[/URL] , align: center"]Inflation Rate[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]07-May-10[/TD]
[TD="align: right"] $ 800[/TD]
[TD="align: right"] $ 930.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2009[/TD]
[TD="align: right"]2.70%[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]07-May-18[/TD]
[TD="align: right"] $ 1,000[/TD]
[TD="align: right"] $ 1,019.00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2010[/TD]
[TD="align: right"]1.50%[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]12-Mar-09[/TD]
[TD="align: right"] $ 25[/TD]
[TD="align: right"] $ 29.88[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2011[/TD]
[TD="align: right"]3%[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"] $ 1,500[/TD]
[TD="align: right"] $ 1,641.62[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"]1.70%[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]14-Feb-13[/TD]
[TD="align: right"] $ 3,850[/TD]
[TD="align: right"] $ 4,213.49[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"]1.50%[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]01-Jan-88[/TD]
[TD="align: right"] $ 450[/TD]
[TD="align: right"] $ 537.76[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2014[/TD]
[TD="align: right"]0.80%[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]01-Jan-10[/TD]
[TD="align: right"] $ 12,500[/TD]
[TD="align: right"] $ 14,545.07[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2015[/TD]
[TD="align: right"]0.70%[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2016[/TD]
[TD="align: right"]2.10%[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2017[/TD]
[TD="align: right"]2.10%[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2018[/TD]
[TD="align: right"]1.90%[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2019[/TD]
[TD="align: right"]0.00%[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(YEAR(B2)>=YEAR(TODAY()),C2,FVSCHEDULE(C2,INDIRECT(ADDRESS(MATCH(MAX(YEAR(B2),$F$2)+1,$F$2:$F$99,0),COLUMN(G1))&":"&ADDRESS(MATCH(YEAR(TODAY()),$F$2:$F$99,0),COLUMN(G1)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

When 2019 comes to an end you'll need to enter the annual inflation rate (currently at 1.8% YTD) and as soon as the clock changes to 2020 you can press F9 to recalculate the column D values.

Regards,
Toadstool

 

[jeff2813]

Thank you very much for the wealth of info. Im a relative newbie so it woll take me a while to get figured out. Thanks again.

 

[Toadstool]

Jeff,

Let me give a little explanation:

• =IF(YEAR(B2)>=YEAR(TODAY()),C2, says "Is the year purchased greater than or equal to the current year?". If that's true it puts in the original value from C2 as there can have been no inflation yet. If it's false it falls into the FVSCHEDULE statement
• FVSCHEDULE(C2, The first part of FVSCHEDULE is the Principal, which in this case is the starting value in C2, $800 in my example.
• The next part of FVSCEDULE is a list of interest rates, or in this case inflation rates. I can't tell when I'm typing in the function what that range will be, and it changes every year, so I manufacture an address range. The Dining Chairs were purchased 7-May-2010 (B2) so I need to manufacture the range for that so INDIRECT can point the FVSCHEDULE at those values. The range I'll be building is $G$3:$G$11 so I get the rates for 2010 through 2018.
  
  
• INDIRECT( says I'm going to build the address range $G$3:$G$11
  
  
• ADDRESS( says get me the address for this row and column where
• MATCH(MAX(YEAR(B2),$F$2)+1,$F$2:$F$99,0), takes the MAX of my year of purchase or the first year in $F$2 (so I don't get a mismatch for my 1988 purchase) and return that row number which MATCHes from F2:F99
  ​

• COLUMN(G1)) will always give me a column number of 7. The ADDRESS therefore gets row 3 as year 2010 is in row 3 and column 7 as that the G column. ADDRESS therefore returns $G$3
  
  ​
• &":" just adds a colon so I've now got $G$3:
  
  
• &ADDRESS(MATCH(YEAR(TODAY()),$F$2:$F$99,0),COLUMN(G1))))) does similar as above but MATCHing the year of TODAY (so gives me row 11 because I started searching from F2) and the same column number as before of 7 for G1. ADDRESS(11,7) therefore give me $G$11 which is concatenated to the previous part by the & and I get the string $G$3:$G$11 which INDIRECT passes to FVSCHEDULE as the list of inflation rates to apply.

OK, maybe the explanation wasn't little...

Regards,
Toadstool

 

[jeff2813]

Thank you