# Declining Balance Depreciation- no Useful Life for function

#### underpar

##### New Member
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.

I appreciate it.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### wavemehello

##### Board Regular
Possible to post sample of ur worksheet?

#### underpar

##### New Member
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
ABCDEFG
1DepRateTypeDescriptionInServiceDateCostAccumDepnNetBookValue
220%Furn-EquipLateralFileCabinet6/15/20021,098.00
320%Furn-EquipBatteryforForklift11/10/20041,495.00
420%Furn-EquipYale6500lbElectricCushion6/12/200628,774.99
520%Furn-EquipRepairstoForklift8/1/20021,645.00
620%Furn-EquipIndustrialBattery-Wet1/11/20037,072.00
720%Furn-EquipConduitRacking7/19/20053,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
ABCDEFG
1
2CostDepnNetDepreciationCalc
320021,098.00128.10969.90=+D3*0.2*(7/12)
420031,098.00193.98904.02=+F3*0.2
520041,098.00180.80917.20=+F4*0.2
620051,098.00183.44914.56=+F5*0.2
720061,098.00137.18960.82=+F6*0.2*9/12
HR_GBW

Thanks!
Mike

Replies
0
Views
1K
Replies
1
Views
771
Replies
0
Views
148
Replies
2
Views
494
Replies
2
Views
4K

1,171,463
Messages
5,875,658
Members
433,145
Latest member
nzltrippa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back