Calculating Depreciation

[Melissa_Frank1]

I am trying to create a table that calculates equipment depreciation by straight line, sum of years, and double-declining, because we have a number of different equipment types that depreciate in very different ways, but want to monitor all of them in one place.

I am struggling to find a way to calculate the accumulated depreciation in each given period. Straight line was no problem, but the sum of years is reminding me a lot too much of high school calculus and I’m ready to take it all back into an Excel sheet no one will ever look at again. I haven’t tried the double-declining yet, but I’m hoping it’ll be easy after sum of years!

Is this even possible in Airtable? I suspect I will need helper fields and don’t really want to go there in case some future equipment has a much longer lifespan than I’ve built in. Excel has an SYD function to get the period Depreciation Expenses, but nothing (that I’ve found) to give you the Accumulated Depreciation without referencing another row.

[JonathanBowen]

Hi @Melissa_Frank1 - this was a challenge, but I think I’ve got the answer. Note that I’m not an accountant and just Googled the difference depreciation methods, so definitely worth you dry-running this one if you plan to use it for real (any accountants out there, feel free to pitch in :slightly_smiling_face: )

Have a look at this base:

Airtable

Depreciation - Airtable

Explore the "Depreciation" base on Airtable.

(Copy into your own workspace to look at the formulas).

The bases uses a single select field to choose the depreciation method and the Cumulative Depreciation field uses this formula:

SWITCH(
  {Depreciation Method},
  'Straight Line', ({Value to Depreciate}/{Years to Depreciate}) * Age,
  'Sum of Years', (((({Years to Depreciate} * ({Years to Depreciate} + 1))/2) - ((({Years to Depreciate} - Age) * (({Years to Depreciate} - Age) + 1))/2))/(({Years to Depreciate} * ({Years to Depreciate} + 1))/2)) * {Value to Depreciate},
  'Double Declining', (1 - POWER(1 - 2/{Years to Depreciate}, Age)) * {Value to Depreciate}
)

I won’t go through this here, but if you understand how the different methods work I’m sure you can work through this.

Hope this helps. Let me know if you find any errors!

JB

[JonathanBowen]

I tested out the Base where the assets had reached the end of their useful life, i.e. Age = Years to Depreciate - this was the result:

So, straight line and sum of years both fully depreciate the value. However, the double decline does not. I’m not sure if this is expected or if there’s some error in the formula (or if the formula should use different values for the depreciation calculation, e.g. “purchase value” rather than “value to depreciate”). Anyone with any knowledge in these matters?

[Melissa_Frank1]

Thank you for trying to tackle this!! The frustrating thing is, I need to see each year (ahh!), so that I can add the current year’s depreciation costs to an annual statement. So I had one table with the overall things like purchase cost, years of life, salvage value, etc. Then a second table with the depreciation costs for each year, along with cumulative (to-date) depreciation, and so forth. Maybe we need a set of “table types”, where you can select the usual Airtable record database-like tables, or the Excel-like spreadsheet tables for row-by-row cumulative calculations like this?

[JonathanBowen]

I think there is a way to calculate cumulative and current year depreciation. To be explicit about all years, would, I think, need a field for each year. This could get messy or tricky, especially if some assets are depreciated over many years, e.g. 25 or more, or where assets have different depreciation periods.

A second table solution feels difficult as, whilst we can get AT to populate fields (via formulas), you can’t get it to create rows, at least not without some outside help.

Anyway, let me think on this some more and see if there is a way to do this.

[Melissa_Frank1]

Thanks. I was thinking one record per year, but one field per year (per calculation) is an interesting idea.

[Natalie_Johnson]

Hi @JonathanBowen ! I am currently working on depreciation calculations for our organization’s equipment. I tried to view the Depreciation - Airtable example, but it looks like it is no longer available. I tested formula, but think I am missing something in terms of the type of depreciation. I would like the calculate a piece of equipment’s depreciation value over the life of the equipment. Any advice?

[JonathanBowen]

Hi @Natalie_Johnson - yes, I had a clear out a while back!

I’ve recreated the base here:

Airtable

Depreciation - Airtable

Explore the "Depreciation" base on Airtable.

This has the original fields and formulas in it. The type of depreciation was the method you might choose to use for each purchase. Looking at the formulas again now, I’m not totally convinced the Double Declining is correct, so you probably want to run some tests against each type before putting this to use in a real base.

[Natalie_Johnson]

Thank you so much @JonathanBowen ! This is extremely helpful! I will take a look at this now!