Automotive 5 Year Depreciation

[Jay]

Hey Guys:

Can one of yuz take a look at this formula related to a 5 Year Automotive
depreciation factor?

Here it is:

21,000 Initial cost =A12
2,100 Salvage value = 10% =A13
5 Lifetime in years =A14
Description (Result) are based on a purchase in July
$3,907.75 1 Yr =DB(A$12,A$13,A$14,1,7)
$5,452.43 2 Yr =DB(A$12,A$13,A$14,2,7)
$3,713.10 3 Yr =DB(A$12,A$13,A$14,3,7)
$2,528.62 4 Yr =DB(A$12,A$13,A$14,4,7)
$1,721.99 5 Yr =DB(A$12,A$13,A$14,5,7)

Test them out! Are these numbers accurate? I need yuz to verify if they are?
If they are not do yuz have suggestions or examples?

Thanks,...

 

[Ron Rosenfeld]

Hey Guys:

Can one of yuz take a look at this formula related to a 5 Year Automotive
depreciation factor?

Here it is:

21,000 Initial cost =A12
2,100 Salvage value = 10% =A13
5 Lifetime in years =A14
Description (Result) are based on a purchase in July
$3,907.75 1 Yr =DB(A$12,A$13,A$14,1,7)
$5,452.43 2 Yr =DB(A$12,A$13,A$14,2,7)
$3,713.10 3 Yr =DB(A$12,A$13,A$14,3,7)
$2,528.62 4 Yr =DB(A$12,A$13,A$14,4,7)
$1,721.99 5 Yr =DB(A$12,A$13,A$14,5,7)

Test them out! Are these numbers accurate? I need yuz to verify if they are?
If they are not do yuz have suggestions or examples?

Thanks,...

I get different values.

If you purchase the vehicle in July, depending on when in July, your months in
the first year should be five or six; you've used seven.


--ron

 

[joeu2004]

21,000 Initial cost =A12
2,100 Salvage value = 10% =A13
5 Lifetime in years =A14
Description (Result) are based on a purchase in July
$3,907.75 1 Yr =DB(A$12,A$13,A$14,1,7)
$5,452.43 2 Yr =DB(A$12,A$13,A$14,2,7)
$3,713.10 3 Yr =DB(A$12,A$13,A$14,3,7)
$2,528.62 4 Yr =DB(A$12,A$13,A$14,4,7)
$1,721.99 5 Yr =DB(A$12,A$13,A$14,5,7)

[....]
If you purchase the vehicle in July, depending on when in July, your months in
the first year should be five or six; you've used seven.

Also, if the 1st calendar year has less than 12 months of depreciation,
there is some depreciation in the 6th calendar year. So you need
=DB(...,6,...). See the example in the DB Help page.

 

[Jay]

Okay,..guys, that sounds logical enough!

Now, I need to have this function not show #NUM! when there is "NO" data
beyond the selected number of years. Say, if I want to depreciate a computer
system over two or three years instead of 5 years, the
"=DB(A$12,A$13,A$14,3)" years 5 and 6 will show #NUM! due to there not being
any data for those years. I want those years to show nothing, blank or a 0.
Is that possible?

Thanks,...

21,000 Initial cost =A12
2,100 Salvage value = 10% =A13
5 Lifetime in years =A14
Description (Result) are based on a purchase in July
$3,907.75 1 Yr =DB(A$12,A$13,A$14,1,7)
$5,452.43 2 Yr =DB(A$12,A$13,A$14,2,7)
$3,713.10 3 Yr =DB(A$12,A$13,A$14,3,7)
$2,528.62 4 Yr =DB(A$12,A$13,A$14,4,7)
$1,721.99 5 Yr =DB(A$12,A$13,A$14,5,7)

[....]
If you purchase the vehicle in July, depending on when in July, your months in
the first year should be five or six; you've used seven.

Also, if the 1st calendar year has less than 12 months of depreciation,
there is some depreciation in the 6th calendar year. So you need
=DB(...,6,...). See the example in the DB Help page.

 

[joeu2004]

Now, I need to have this function not show #NUM! when there is "NO" data
beyond the selected number of years. Say, if I want to depreciate a computer
system over two or three years instead of 5 years, the
"=DB(A$12,A$13,A$14,3)" years 5 and 6 will show #NUM! due to there not being
any data for those years. I want those years to show nothing, blank or a 0.
Is that possible?

One way to do that:

=if(iserror(DB(A$12,A$13,A$14,3)), "", DB(A$12,A$13,A$14,3))

 

[Jay]

Thanks guys,...great job! Appreciate it a bunch!

 

[Roger Govier]

Hi Jay

Another way of overcoming the error if you alter the number of years,
and of dealing with the extra depreciation in year N+1 if a full year's
worth of depreciation is not taken in the first calendar year, would be
to make use of the ROW(1:1) function to automatically change the year
number as you copy the formula down, rather than having to type in Year
number.
(If you are copying your formula across, then use COLUMN(A:A) in place
of ROW(1:1) )

=IF(ROW(1:1)>$A$14+(OR($A$15<>12,$A$15<>"")),"",
DB(A$12,A$13,A$14,ROW(1:1),MIN(12,$A$15)))