Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Calculating Depreciation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
5150
8

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 10, 2019 04:10 PM

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.

8 Replies 8

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 02, 2019 08:32 AM

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:

(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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 02, 2019 08:45 AM

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?

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 02, 2019 11:52 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 02, 2019 02:24 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 02, 2019 10:47 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 24, 2022 11:44 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 28, 2022 04:21 AM

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

I’ve recreated the base here:

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 28, 2022 07:15 AM

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