Results 1 to 5 of 5

Thread: Auto update cell price for inflation
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Auto update cell price for inflation

    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

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Auto update cell price for inflation

    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
    1 Article Date Acquired Value Value Adjusted
    for Inflation
    Year Inflation Rate
    2 Dining Chairs x 4 07-May-10 $ 800 $ 930.88 2009 2.70%
    3 Dining Table 07-May-18 $ 1,000 $ 1,019.00 2010 1.50%
    4 Rolecks Watch 12-Mar-09 $ 25 $ 29.88 2011 3%
    5 Gold Ring Man's 14-Feb-13 $ 1,500 $ 1,641.62 2012 1.70%
    6 Gold Ring Woman's 14-Feb-13 $ 3,850 $ 4,213.49 2013 1.50%
    7 Painting "Dogs Playing Poker" 01-Jan-88 $ 450 $ 537.76 2014 0.80%
    8 Painting "Scenic Scene" 01-Jan-10 $ 12,500 $ 14,545.07 2015 0.70%
    9 2016 2.10%
    10 2017 2.10%
    11 2018 1.90%
    12 2019 0.00%
    Sheet1

    Worksheet Formulas
    Cell Formula
    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)))))

    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

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto update cell price for inflation

    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.

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Auto update cell price for inflation

    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

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto update cell price for inflation

    Thank you

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •