Auto update cell price for inflation

jeff2813

New Member
Joined
Jul 17, 2019
Messages
30
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.

ABCDEFG
1ArticleDate Acquired Value Value Adjusted
for Inflation
YearInflation Rate
2Dining Chairs x 407-May-10 $ 800 $ 930.8820092.70%
3Dining Table07-May-18 $ 1,000 $ 1,019.0020101.50%
4Rolecks Watch12-Mar-09 $ 25 $ 29.8820113%
5Gold Ring Man's14-Feb-13 $ 1,500 $ 1,641.6220121.70%
6Gold Ring Woman's14-Feb-13 $ 3,850 $ 4,213.4920131.50%
7Painting "Dogs Playing Poker"01-Jan-88 $ 450 $ 537.7620140.80%
8Painting "Scenic Scene"01-Jan-10 $ 12,500 $ 14,545.0720150.70%
920162.10%
1020172.10%
1120181.90%
1220190.00%

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
D2=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)))))

<tbody>
</tbody>

<tbody>
</tbody>

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

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top