Populating a Straight Line Depreciation Annual Table Expense Schedule

Hi,

Looking for help in creating a simple straight line depreciation schedule. Highlighted in yellow in the attached file is an area that I need to population based on the asset item in the header row. The table returns the total depreciation for the item, per year.

The dataset in the left table contains the info pertaining to the asset and it's depreciation info.

I've manually gone thru and populated each item with the value that pertains to the year and item. I'd like to have a formula created that will populate the table automatically.

Thanks in advance

-Manny

Question also is at https://www.excelguru.ca/forums/show...eciation-Table

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Please post the requested link here.

Well, I think I found a proper resolution for you....

*The post was deleted , as I already figured out the solution*

Belinda - where is the solution you have found for the OP?

Hi,
Basically I used multiple If/Vlookup formulas to find both the year and item, and directed it to the proper column depends on the year position.

If it's the 1st year of the purchase it will go to "1st Year Deprc", if it's the year's of the end date - it will go to "Last year depreciation", and whatever is between will be calculated as such:

(Cost-1st Year Deprc-Last Yr. Deprc)/Yr.'s Service-2

Hope this achieves you goal.

Please try at N7

Formula:

Please Login or Register  to view this content.

I use different terms.
yearly average: Net SL Depreciation / by Yr.'s Service
Last Yr. Deprc: yearly average - 1st Year Deprc

I intent to calculate this way because from OP #1
AC unit
1st Year Deprc = 547.63
Last Yr. Deprc = 159.24
547.63+ 159.24 = 706.87

But yearly average is 709.51
This really weird results where the sum of 1st Year and Last Yr. Deprc is $3 different from yearly average.


547.63+161.18

Where my formula
1st Year Deprc = 547.63
Last Yr. Deprc = 161.70
547.63+ 161.70 = 709.33 equal to yearly average
10,640/15 = 709.33

Duplicate post (?!)

One (slightly shorter) formula which takes into account leap years:

Please Login or Register  to view this content.

This formula belongs in cell N7, but in order not to foul up table structured references while dragging across, you need to select N6:Q6 and commit the formula to those cells while holding down the Ctrl key (this is NOT array/CSE entering the formula). If the table does not then automatically fill down you can then drag down.
2020-05-03_130232.jpg