Results 1 to 7 of 7

20010220, 20:58 #1
 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)

20010220, 22:43 #2
 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 selfevident, 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>

20010220, 23:51 #3
 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)

20010221, 00:38 #4
 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 misidentified 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...

20010221, 02:37 #5
 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 straightline depreciation over noninteger 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>

20010221, 02:54 #6
 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......

20010222, 18:44 #7
 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