Declining Balance Depreciation- no Useful Life for function

underpar

New Member
Joined
Jan 26, 2005
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

underpar

New Member
Joined
Jan 26, 2005
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,991
Messages
5,678,993
Members
419,797
Latest member
ikethegenius

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top