Nth power dilemma

[Andre Croteau]

Hello All,

I have a list of fixed assets with the following data

A B
C D E
F
1 Description Cost value Depreciation
Net Book value Depr Rate Number years

2 Vehicle #1 100,000
48,800 51,200 20% this is
what I want to know
3 Vehicle #2 100,000
51,040 40,960 20% this is
what I want to know



I would like to have a formula to determine how many years each asset has
been depreciated (declining balance) based of the data in columns B to E

In the first example, the asset has been depreciated 3 years
=100000*(1-20%)^3= 51200.
In the second example, the asset has been depreciated 4 years
=100000*(1-20%)^4= 40,960.

In other words, I would like to know how to get the "N" value in the
following:

100000 x (1-20%)^N = 51200

or (0.8)^N = 0.512

or 0.8 = extract the Nth power of 0.512

or N=3

Does anyone have a formula to obtain the result that I want? or is there a
user defined function through VBA that could be used?

Thanks in advance

André

 

[Toppers]

=POWER(0.512,1/n)

 

[Toppers]

Ignore my reply ... senior moment!

=POWER(0.512,1/n)

 

[Franz Verga]

Nel post *Toppers* ha scritto:



[cut]

=POWER(0.512,1/n)

I think the OP would like to find the "n" value, as you can read above...

 

[David Biddulph]

Hello All,

I have a list of fixed assets with the following data

A B C
D E F
1 Description Cost value Depreciation
Net Book value Depr Rate Number years

2 Vehicle #1 100,000 48,800
51,200 20% this is what I want to know
3 Vehicle #2 100,000 51,040
40,960 20% this is what I want to know



I would like to have a formula to determine how many years each asset has
been depreciated (declining balance) based of the data in columns B to E

In the first example, the asset has been depreciated 3 years
=100000*(1-20%)^3= 51200.
In the second example, the asset has been depreciated 4 years
=100000*(1-20%)^4= 40,960.

In other words, I would like to know how to get the "N" value in the
following:

100000 x (1-20%)^N = 51200

or (0.8)^N = 0.512

or 0.8 = extract the Nth power of 0.512

or N=3

Does anyone have a formula to obtain the result that I want? or is there
a user defined function through VBA that could be used?

One way is Goal Seek, but as a formula for N, you want
=(LOG(51200)-LOG(100000))/LOG(0.8)

 

[Toppers]

=LN(0.512)/LN(0.8)

One way is Goal Seek, but as a formula for N, you want
=(LOG(51200)-LOG(100000))/LOG(0.8)

 

[Andre Croteau]

Hello David,

Amazing stuff... This is exactly what I needed.

Many thanks!

André