Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A real challenge: Straight line depreciation

    The attached file has a line called total depreciation - this is the sum of the 10 lines above. The formula are very messy and do not copy well.

    Need to be able to change the depreciation rate (eg from 4 years to 10years) and change the total number of years (eg 12 years (columns))

    I am looking for suggestions as to how I could so this calc a different way (ideally using a single formula/user function)
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A real challenge: Straight line depreciation

    Simon - this one works:

    I have created a custom function in VBA that requires three arguments:
    1) The range that includes the purchases for the asset class you are dealing with (should be entered as an absolute address (ie $C$9:$m$9);
    2) The cell that the function is located in, and;
    3) The cell that contains the number of years that the assets are depreciated over (should also be an absolute address). Note that I have changed the approach from a depreciation RATE to YEARS, since the function uses the number of years to be depreciated to determine how many years of purchase history should be included in the numerator of the depreciation calculation.

    I think the operation of the function is pretty self-evident, but there are a couple of possible improvements:

    - use the "SUM" function to determine the total assets to be included in the numerator of the calculation - I started to do it this way, then got tired of fooling around with cell offset formulas, and decided to do it as shown - I suspect that the SUM function would execute faster.
    - eliminate the reference to the cell the function is in (actually, it just has to be to the column it is in). I don't know how (or if) you can return the range where the function is being invoked - if anyone can shed some light on it, I would appreciate it.

    the function listing (also in the attached s/sheet) is:

    <pre>Option Explicit

    Function SLDepr(Purchases As Range, ThisCell As Range, Years As Integer) As Currency


    Dim CurrCol As Integer
    Dim PurRow As Integer
    Dim StrtCol As Integer
    Dim ctr As Integer

    PurRow = Purchases.Row
    CurrCol = ThisCell.Column
    StrtCol = Application.WorksheetFunction.Max(1, CurrCol + 1 - Years)
    For ctr = StrtCol To CurrCol
    If IsNumeric(Cells(PurRow, ctr)) Then
    SLDepr = SLDepr + Cells(PurRow, ctr).Value
    End If
    Next ctr
    SLDepr = SLDepr / Years
    End Function
    </pre>

    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A real challenge: Straight line depreciation

    Excellet Job Dean,

    I'm embarrassed that you made it look so simple.

    You were right about sum, you can replace your loop with:
    SLDepr = Application.WorksheetFunction.Sum(Range(Cells(PurR ow, StrtCol), Cells(PurRow, CurrCol)))


    I only have two remaining issues:

    1) It is possible that the number of years is not an integer (hence the percentage)- your code fails at this point as strtcol would obviously require a whole number.

    2) To repeat your question - How do you get a function to identify the cell it is entered in? (it is only the active cell when the formula is entered)

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A real challenge: Straight line depreciation

    Thanks for you kind comments - I had a bit of an advantage, having done something similar to this for a client - but in that case I inserted about a million (rough estimate) hidden columns so that I could create a sum that totalled the correct number of years using a "choose" function without trying to reference a cell to the left of column A (small excerpt attached)....

    Actually, I had that SUM formula in the function initially, but it wasn't working, and I put in the looping structure as it is now. In doing that I noticed that I had mis-identified the current cell as the "ActiveCell" (as you note, it is only the active cell when the function is entered), and that correction probably would've made the SUM finction operate properly. Consider the from - to loop as the veriforma appendix of this function!

    As for your question of how to deal with part years (if the depreciation rate should be 30%, representing S/L over 3 & 1/3 years, for instance): In this case, I would imagine that the depreciation amounts would be (assuming $100 initial cost) $30, $30, 30, 10. I would use a similar function that 'looked back' at the purchasing history on a cell by cell basis and added a full year of depreciation for all the full years and a part year for the stub year. It is easier to visualize than explain - I will post a function in the next little while.

    Anybody who can shed some light on how a function can access the cell it is entereed in (other than by a direct reference in the arguments supplied to the function, which strikes me as a real kludge) <font color=red>PLEASE</font color=red> chime in...
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: A real challenge: Straight line depreciation

    Okay - after much cogitation (that's what I call it when I want to increase my hourly rate!), I think we can use the following UDF (also in file attached) for straight-line depreciation over non-integer years (like a 40% sl rate, representing a 2.5 year depreciation term). Required arguments are all as for previous version of the function, except that "Year" is no longer coerced to an Integer value. It was easier to use years than depreciation rate, but if the rate is more available (or more intuitive) it is simple enough to amend the function appropriately

    <pre>Function SLDepr2(Purchases As Range, ThisCell As Range, Years As Single) As Currency

    Dim CurrCol As Integer
    Dim PurRow As Integer
    Dim StrtCol As Integer
    Dim FullYr As Integer
    Dim PartYr As Single

    FullYr = Int(Years)
    PartYr = Years - FullYr
    PurRow = Purchases.Row
    CurrCol = ThisCell.Column
    StrtCol = Application.WorksheetFunction.Max(1, CurrCol + 1 - FullYr)
    SLDepr2 = Application.WorksheetFunction.Sum(Range(Cells(PurR ow, StrtCol), Cells(PurRow, CurrCol)))
    If CurrCol - FullYr > 1 Then
    If IsNumeric(Cells(PurRow, CurrCol - FullYr).Value) Then
    SLDepr2 = SLDepr2 + Cells(PurRow, CurrCol - FullYr).Value * PartYr
    End If
    End If
    SLDepr2 = SLDepr2 / Years
    End Function
    </pre>

    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A real challenge: Straight line depreciation

    Cool that seems to work perfectly

    Thanks heaps

    Simon

    We'll just wait and see if anyone knows how to get a function to refer to itself......

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A real challenge: Straight line depreciation

    application.caller.column

    Is the missing line of code - this replaces the thiscell argument

    Thanks again for the help

Posting Permissions

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