Depreciation of assets - no. of yrs required

[Balan]

Kindly tell me whether there is any formula or readymade function to find out
the number of years required to depreciate an asset fully ( minimum value $
1)using a declining balance method. For example, if my asset is worth $ 1000
now and it has to be depreciated at 10% / year i.e., value at the end of the
1st yr would be $900, second yr $810 and so on. I want the year to be
calculated until the value becomes $1 . The minimum depreciation during any
year should be $1 i.e., if it happens that in any year the depreciation will
be less $ 1 , the minimum depreciation to be provided would be $1 so as to
reach balance asset value of $1. I could arrive at the value using a table.
But I am looking for a short cut.

 

[MartinW]

Hi Balan,

I know nothing of accounting, however, I am sure there is a statistical
relationship in your problem. I'd just like to check something first
before I go further down this line.

Using your example, if I put 1000 in A1 and put =A1-(10/100*A1) in A2
and then drag it down to A70.

This will show that it will be 67 years before the value is less than $1.
It will also show that after 46 years there will be less than $1
depreciation
each year. Is this correct? And which figure are you trying to achieve?

There is a logarithmic relationship to this problem and I think the
end result will be a nice neat formula that will handle different
percentages and different asset values, but I need some
more info as to what you are trying to achieve.

Regards
Martin

 

[ShaneDevenshire]

Hi Balan,

Create the following VBA function which I believe does what you want:

Function Years(Value, Rate)
Do Until Value <= 1
Value = Value - Value * Rate
Y = Y + 1
Loop
Years = Y
End Function

Using your numbers, if you entered 1000 in A1 and 10% in A2 then the formula
in the spreadsheet would be =YEARS(A1,A2) and would return 66.

 

[ShaneDevenshire]

Hi Balan,

If you're a programmer then:

Function Years(Value As Double, Rate As Double) As Integer
Dim Y As Integer
Do Until Value <= 1
Value = Value - Value * Rate
Y = Y + 1
Loop
Years = Y
End Function

 

[joeu2004]

Kindly tell me whether there is any formula or readymade function to find out
the number of years required to depreciate an asset fully ( minimum value $
1)using a declining balance method. For example, if my asset is worth $ 1000
now and it has to be depreciated at 10% / year i.e., value at the end of the
1st yr would be $900, second yr $810 and so on. I want the year to be
calculated until the value becomes $1 .

Try:

=1 + roundup(nper(-10%,0,-1000,1), 0)

or:

=1 + roundup((ln(1) - ln(1000)) / ln(1-10%), 0)

 

[Dana DeLouis]

the number of years required to depreciate an asset fully ( minimum value

$ 1)

Bases on $1000 is at time zero, the time to get to "1" is:

=LOG(1/1000)/LOG(1-10%)

= 65.56

I'm not sure if you need to round it up or down.
Use Floor or Ceiling. ie =FLOOR(A1,1)

 

[Balan]

Mr Martin
Many thanks for the quick response. I had already tried the formula (
similar to what you have shown) and dragged it down to arrive at the number
of years. However, as calculation of depreciation / amortization is well
known in accounting I was looking for a short function of formula to arrive
at the years required to depreciate an asset to $1, instead of constructing a
whole table of figures. Constructing a table would be, as you would agree,
cumbursome if there are large number of assets and they have different
amortization rates. I still do not find one built in formula in excel which
would help me arrive at the years ( There are functions like DB to arrive at
the depreciation amount for each year).

Now thanks to you, I have got the idea and solved it using log function. If
"V" is the value of the asset, "r" is the rate of depreciation, I could
arrive at the years using the formula : log(V)/log(1-r), where 1-r is the
discounting factor. This has been done using the relationship v/(1-r)^n for
arriving at the depreciated value at the end of a given period. Many thanks
once again.

 

[Balan]

Thanks. I am not a programmer, but trying to become one. My earlier
experience in programming was limited to writing macros in Lotus 1-2-3 ( if
you wouldn't mind calling it programming). I had occasionally done some
thinking on behalf of my son when he had to do some basic programming in C
when he was in his high school. But for these, I have not done any other
programming, that too in VBA. I have just started learning VBA.

I am grateful that you have written two sets of programmes and both are
working. How without declaration, the first one is also working I am not
clear. Perhaps I will find out as I progress. Further, after seeing your
programme, I was tempted to introduce another element in the calculations
i.e., rounding off the amount of depreciation at the end of each year. From
the Help available in VBA, I found that Round ( similar to the one we have in
Excel) could be used. However, it is not working. May the declaration of
Value as Double and Rate as Double is causing the problem. I tried to meddle
with it by converting the Value as an Integer or as a Long. It didn't work.
I tried to introduce a new variable called Dep ( to represent Depreciation)
and get the result of Value * Rate in that , round off the result in a
subsequent code and then minus it from Value to arrive at year end written
down value. Still it did n't work. If you could give me some clue, I shall
be grateful to you and work on it further. Sorry, if I am troubling you.

 

[Balan]

Mr.Joeu,

Thank you. I was not knowing that NPER can be used for this also. It
works.

 

[Balan]

Dana,
Thanks. You have corrected a mistake committed by me when I used the Log
function . Instead of log(1/1000), I had used log(1000) which was giving me
a negative value. I am thankful to Joeu2004 also as he too had corrected the
mistake, albeit in a different way. Thanks a lot for making me familiar with
Floor /Ceiling functions.

 

[joeu2004]

Thanks. You have corrected a mistake committed by me when I
used the Log function . Instead of log(1/1000), I had used log(1000)
which was giving me a negative value. I am thankful to Joeu2004
also as he too had corrected the mistake, albeit in a different way.

Your error was merely in using log(1000) instead of -log(1000) --
which is the same as log(1/1000). I assumed you would correct it on
your own because most people cannot tolerate negative results when
they expect positive numbers.

I am thankful to Joeu2004
also as he too had corrected the mistake, albeit in a different way.

In my formulation, we get -ln(1000) because ln(1) is zero, of course.
I left ln(1) in the equation to demonstrate the most general form; for
example, if you choose to depreciate until $50 is of $1. (See below.)

I assume you realize that it does not matter whether you use LN() or
LOG(). Some people would say that LN() is the more "natural"
choice ;-). Just kidding!

(To be honest, I would have used LOG() myself, but I misread the Excel
function description and did not realize the second argument is
optional.)

By the way, on second-thought, I think that my adding 1 is incorrect.

And hasten to note that I think we all got it wrong if you truly mean
that the "minimum depreciation during any year should be $1". I think
we all interpreted that to mean that the depreciated value of the
asset should not be less than $1.

But on second thought, to me, "minimum depreciation" refers to V*10%,
not V*(1-10%) -- that is, the amount of the depreciation, not the
depreciated value. That would suggest that you want to find the
number many years before the depreciated value drops below $10 or $5,
depending on whether you want the truncated or rounded depreciation to
be no less than $1.

HTH.

 

[Dana DeLouis]

....Instead of log(1/1000), I had used log(1000) which was giving me

a negative value.

Although you could use -Log(1000) in "this" situation, the "1" in 1/1000 is
the ending value. Which happens to be 1.
You could use Log(2/1000) for an ending value of 2 if your requirements
change.
That's why I wrote it in the more general way.

 

[joeu2004]

Although you could use -Log(1000) in "this" situation, the "1" in 1/1000
is the ending value. Which happens to be 1. You could use
Log(2/1000) for an ending value of 2 if your requirements change.
That's why I wrote it in the more general way.

And I'm sorry if my follow-up implied otherwise.

I recognized that as your intent when I read your posting initially.
But when I read Balan's follow-up, referring to log(1/1000) v.
log(1000), somehow I focused on the sign issue and lost sight of the
generalization.

Of course, log(a/b) = log(a) - log(b). So both forms are equally
valid. It all depends on how you choose to solve the following
equation for n:

FV = PV*(1-r)^n

I took the log of both sides first, resulting in:

log(FV) = log(PV) + n*log(1-r)

whereas presumably you isolated the exponential term first, then took
the log of both sides, resulting in:

log(FV/PV) = n*log(1-r)

Frankly, I think yours is the better approach in general. I certainly
would have gone that direction myself if the equation had been in the
form a = b + c*d^n. But somehow, my final form seems more intuitive
to me. Oh well, that's just me.

 

[Balan]

Dana,
Thanks for the clarification. I have learnt some maths also from your
explanation.

 

[Balan]

Joeu,
Thanks for sparing your time. Your understanding that I was looking for the
number of years required to depreciate the asset to $ 1 is correct. I did
not mention about it deliberately, thinking that rounding off the
depreciation (to multiples of $1) and arriving at the Written Down Value
every year may not be possible if I am looking for a single formula. I don't
think we will be able to combine the 'npr' function or log function with some
other formula for annual round off. May be a VBA program as given by
Mr.Shane Devenshire may be needed. I am trying to modify Mr.Shane's program
to round off annually the figure of depreciation and then arrive at the
written down value for the year. I have not been successful so far as I am
new to programming.

I thank you and Dana DeLouis profusely for sparing your time for me. I am
enriched by your postings.

Balan

 

[Balan]

Mr Shane,
Many thanks for the VBA function. I have modified it to fetch me the
number of years after rounding off the depreciation to the nearest $ every
year and arriving at the written down value thereafter. I have also
introduced one more factor viz., Residual Value ( which I have called as
ResVal), as from the posts of Dana and Joeu to my query I got the idea that
there could be a user who would be indicating the lowest value upto which the
depreciation should be worked out. It need not always be $ 1. It could be $
10 also. Initially I was having problem with "double" , "Integer" etc.,
after going through Excel's VBA help about using worksheet functions in VBA,
I could solve it. Kindly see whether the following programme is OK ( It is
working OK for me) and whether there could be more economical way of
achieving the same thing.
----
Function Years(Value As Double, Rate As Single, ResVal As Integer) As Integer
Dim Y As Integer

Do Until Value < ResVal
Value = Value - Application.WorksheetFunction.Max _
(Round((Value * Rate), 0), 1)
Y = Y + 1
Loop
Years = Y
End Function