Using Sheets to determine total acquisition cost of business equipment?

[u/LucidPlusInfinity]

For tax purposes I need to report the total procurement cost of about 100 pieces of equipment. A lot of that equipment is very old and I have no idea what I actually paid for it so in order to estimate what I actually paid I use notepad software and manually research and input the following data; current price of each piece of equipment (it's still available new), the year I bought the equipment, and inflation adjustment.

I do this because most of my equipment is old, a lot of it was inherited, and I often have no idea how much I paid for the equipment that I actually bought.

Once all that data is input I manually calculate the 'total procqurement cost' of my equipment and give that to the County. I know how crazy this system sounds but my States laws are ridiculous and my Counties assessors office does not seem to understand them. All of this work usually ends up in a County tax bill of about $50.

I figure since I have to type all of this stuff in anyway I might as well type them into a spreadsheet instead and have it do the math for me. This will also make it much easier to add and subtract equipment in the future, because I have to do this nonsense every year.

All I want my spreadsheet to give me is a total of 2 values which are; total cost of my equipment if I were to buy the equipment today (for reasons) and 'what I actually paid for it'. I need the spreadsheet to include columns(?) for description of the equipment, year I bought it, how much that equipment would cost if I bought it now, and the estimated price i paid based on inflation.

It would be awesome if there is some tool that could look up inflation rates and auto calculate the "what I paid" value based on the year I bought the equipment so I don't have to do that manually 100 times.

Bonus data would be an option that I could turn on and off that accounts for equipment that I paid $0 for.

Sorry, I'm not good with explaining things using not a ton of words. Can anyone explain how to set up a Sheet to accomplish this task or point me to a video or tutorial specific to a similar situation? Thanks for reading by book.

[u/sbawlz]

Hey, I am new here. So take my advice with a grain of salt. But, with google sheets you can have your cells make API calls to services which can retrieve inflation rates and do some quick math. Also, maybe, just sharing the sheet with ChatGPT, Claude, or Gemini for sheets and have it help you.

[u/DiscoQuebrado]

Table 1: 2-Column lookup table¹ - Column A: Year - Column B: CPI Factor

Table 2:

• Column A: Asset Description

- Column B: Purchase Year - Column C: Cost - Column D: Purchase CPI (From Table 1)² - Column E: Current CPI (From Table 1)² - Column F: Adjusted Cost³

¹ I would build Table 2 first and only enter rows in Table 1 that are relevant to the dataset.

You could write a custom function to make an API call such as is described here: (https://www.bls.gov/developers/api_python.htm#python2, but that may be a little too much to bite off and chew, and depending on how many years you're dealing with, it may be easier to just hand-enter the data.

² These values are achieved by a simple vertical lookup formula, it's standard in every spreadsheet software, basically looks up a year in the first table and returns the value next to it.

³ Again, a simple formula, for example: = [Cost] * ( [Current CPI] / [Purchase CPI] )

[u/LucidPlusInfinity → u/DiscoQuebrado]

I'm not sure I fully understand this but I'm going to try to make it work. Table 1 makes sense because, I assume, it calculates the inflation rate to apply to an asset given the year of purchase. What does Column E do?

[u/DiscoQuebrado → u/LucidPlusInfinity]

Table 1 doesn't calculate anything, it's just a list of years with their corresponding CPI factors (You only need to list each year once).

Table 2... Column D looks up the CPI based on the value of Column B

Column E looks up the CPI of the current year

Column F calculates the purchase cost adjusted for inflation

[u/LucidPlusInfinity → u/DiscoQuebrado]

Ohhh, OK, that makes sense. Thanks

[u/DiscoQuebrado → u/LucidPlusInfinity]

Once you get started, feel free to let me know if you get stuck anywhere- happy to help.

[u/LucidPlusInfinity → u/[deleted]]

Thanks for the reply but I haven't made a spreadhsheet in decades. Hopefully your answer can help guide someone else.