+ Reply to Thread
Results 1 to 18 of 18

Populating a Straight Line Depreciation Annual Table Expense Schedule

  1. #1
    Registered User
    Join Date
    12-20-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    76

    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
    Attached Files Attached Files
    Last edited by Prof Sick; 04-29-2020 at 10:07 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,044

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    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.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-20-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    76

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    I guess I broke the rules. What do I do? Nobody has replied with a resolution which I'm fine with. Just like to know one way or another. I'll move on.

    -Manny

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,044

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Please post the requested link here.

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,212

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Well, I think I found a proper resolution for you....
    Last edited by belinda200; 04-27-2020 at 05:56 AM.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,212

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    *The post was deleted , as I already figured out the solution*
    Last edited by belinda200; 04-27-2020 at 05:57 AM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,044

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

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

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,212

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    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.
    Attached Files Attached Files
    Last edited by belinda200; 04-29-2020 at 04:43 AM.

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,478

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Please try at N7
    Formula: copy to clipboard
    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
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,212

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Quote Originally Posted by Bo_Ry View Post
    Please try at N7
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    very nice and creative solution.
    The only problem is that when you change the amount in Last year's depreciation - the formula is not reflecting the new data

  11. #11
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,478

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    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

  12. #12
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,212

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Thanks for your answer. I see now what you are talking about, really weird that the average of the 1st + last year does not equal to the total average, perhaps something with the yearly days counting.

    Thanks for the extensive explanation.

  13. #13
    Registered User
    Join Date
    12-20-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    76

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Very impressive. Thanks to everyone for the help on this.

    -Manny

  14. #14
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    126

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Duplicate post (?!)
    Last edited by p45cal; 05-03-2020 at 08:18 AM.

  15. #15
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    126

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    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
    Last edited by p45cal; 05-03-2020 at 08:17 AM.

  16. #16
    Registered User
    Join Date
    12-20-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    76

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Thanks. I put the formula in place in our schedule. Worked perfectly. The leap year issue did cross my mind but figured it might be getting a little to technical so didn't pursue it.

    Thanks again and stay safe.

    -Manny

  17. #17
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    126

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Forgive this post, I'm just getting my head around Power Query and used your question as an exercise/challenge.
    In the attached, I've reduced the size of your source table to exclude all calculated data, so it's all just raw data.
    Just add/remove/change the data in that table, then right-click the result table on the right and choose Refresh.
    It's far from polished and has too many steps as I'm still new to this, but it does show how powerful and quick it can be.

  18. #18
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    126

    Re: Populating a Straight Line Depreciation Annual Table Expense Schedule

    Prof Sick/Manny_Avila, please put a link to here at your cross post at ExcelGuru, it's only fair.
    (You may have to add a post or two there to be able to include links.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SLN - Straight Line Depreciation
    By beep_boop in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2020, 09:31 AM
  2. Straight Line Depreciation for Long Term Model of Depreciation
    By fornight in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2017, 03:14 PM
  3. How to use the IF Function in Straight Line Depreciation
    By tobbyexcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2015, 06:54 PM
  4. Excel 2007 : Straight line depreciation
    By Simmi7 in forum Excel General
    Replies: 7
    Last Post: 10-10-2014, 08:45 PM
  5. Straight Line Depreciation Calculation
    By andrew8008 in forum Excel General
    Replies: 4
    Last Post: 11-07-2012, 04:29 PM
  6. Straight Line Depreciation using the IF Function (Excel '03)
    By L_haynes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-09-2010, 03:34 AM
  7. SLN - Straight Line Depreciation
    By tr2yhb@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-06-2005, 10:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1