Declining Balance Depreciation

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!

Hi Bryan: Thanks for your reply.

Yes, 6/01/2006 would be June 1, 2006 in my example. Regarding the layout, I've attached 2 pdf's:

(1) Fixed Assets Depreciation.pdf
In Column F, I want to show the depreciation (starting with the month of the Service Date and ending with Sept 30, 2006). Column G would be the difference between Col E and Col F.

(2) Example.pdf
This shows the manual calculation that I need to do for each asset. The 1st month of depreciation will be the month of the "in service date" and the last month will be September, 2006. Each asset has a different "in service date"

I really appreciate your help with this.
Regards,
Mike