Declining Balance Depreciation- no Useful Life for function

[underpar]

Hi Everyone:
I have a declining balance depreciation issue that could use your input. I have about 1,000 assets in a spreadsheet, all with different acquisition dates. I have to calculate the Accumulated Depreciation and Net book Value as of September 30, 2006. I don’t believe that Excel’s depreciation formulas will help because we don’t take an estimated useful life into account (i.e. in theory, the depreciation, regardless of how small it is, will go on ”forever”). Here is an example:

Cost $10,000:
Depreciation starts June , 2004
Declining balance rate = 20%

The manual calculation would be:
2004 Depreciation = 10,000 X 20% X 7/12 = 1,166.66
2005 Depreciation = (10,000-1166.66) X 20% X 12/12 = 1,766.67
2006 Depreciation to 9/30/06 = (10,000-1166.66-1766.67) X 20% X 9/12 = 1,060.00

The Net Book Value = 10,000 – 1166.66 – 1766.67 – 1060 = 6,006.67

My spreadsheet contains the date in date format as 6/01/2006 .

Would anyone have any suggestions? Because there are over 1,000 of these items to calculate with different acquisition dates, I can’t do this manually.

Thanks for your help!
I appreciate it.

 

[wavemehello]

Possible to post sample of ur worksheet?

 

[underpar]

Hi Wavemehello: Thanks for responding.

Here is an example of the layout. The depreciation starts in the month shown in the In Service Date column and ends Sept 30, 2006. The manual calculation of the depreciation for this item is shown at the bottom of this post.

Example.xls
	A	B	C	D	E	F	G
1	DepRate	Type	Description	InServiceDate	Cost	AccumDepn	NetBookValue
2	20%	Furn-Equip	LateralFileCabinet	6/15/2002	1,098.00
3	20%	Furn-Equip	BatteryforForklift	11/10/2004	1,495.00
4	20%	Furn-Equip	Yale6500lbElectricCushion	6/12/2006	28,774.99
5	20%	Furn-Equip	RepairstoForklift	8/1/2002	1,645.00
6	20%	Furn-Equip	IndustrialBattery-Wet	1/11/2003	7,072.00
7	20%	Furn-Equip	ConduitRacking	7/19/2005	3,937.03
HR_GBW

Here is an example of the depreciation. In this example, the asset has been depreciated for 7 months in 2002 (acquired in June), 12 months for each of 2003-2005 and 9 months in 2006 (I want to stop at Sept 30, 2006).

db_depn.xls
	A	B	C	D	E	F	G
1
2				Cost	Depn	Net	DepreciationCalc
3			2002	1,098.00	128.10	969.90	=+D3*0.2*(7/12)
4			2003	1,098.00	193.98	904.02	=+F3*0.2
5			2004	1,098.00	180.80	917.20	=+F4*0.2
6			2005	1,098.00	183.44	914.56	=+F5*0.2
7			2006	1,098.00	137.18	960.82	=+F6*0.2*9/12
HR_GBW

Thanks!
Mike