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.




"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire


"Balan" wrote:

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.