A real challenge: Straight line depreciation

Topic

New Reply

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)

Reply | Quote

Replies

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:

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

Reply | Quote

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(PurRow, 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)

Reply | Quote

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) PLEASE chime in…

Reply | Quote

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

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(PurRow, 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

Reply | Quote

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……

Reply | Quote

application.caller.column

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

Thanks again for the help

Reply | Quote