Suggestion and the recommendation of special functions depreciation

Occasional Contributor

Excel includes 5 formulas to calculate depreciation (SLN,DB,DDB,VDB,SYD) and all of these formulas do not include cells for Starting Date and end date for the cycle to calculate depreciation using it except for VDB formula. For example if we want to calculate depreciation for a specific asset starting from 1st of july,2010 to 31st of December,2015. Using the current existing Excel formulas, depreciation can not be calculated directly. However it is used to calculate depreciation for the whole calendar year starting 1st January till 31st of December not for a partial period starting or ending in different dates. As An example of how complex to calculate depreciation using Excel formulas is SLN method.----------------------------------------------------------------------------------

=IF($A7=1,SLN($A$1,$A$2,$A$3)*((DATEDIF($A$4,DATE(YEAR($A$4),MONTH($A$5),DAY($A$5)),"M")+1)/12),
IF($A7=$A$3+1,
((12-((DATEDIF($A$4,DATE(YEAR($A$4),MONTH($A$5),DAY($A$5)),"M")+1)))/12)*SLN($A$1,$A$2,$A$3),SLN($A$1,$A$2,$A$3)))

9999999999.png

 

 

 

so I hope and recommend that every supervisor in the Arabic blog to help and support to add two more cells to depreciation formulas *Starting date *End date These recommendation make depreciation formulas more useful and matching the accounting the standards whereas depreciation expense is a major expense and miscalculating it leads to disaster. Best regards

13 Replies
Learned something new. Never knew depreciation could be calculated for non-full year. Will try read up more on this. Thanks for sharing and I hope you get your request.

i Agree with you Mr.Mohamed

yes i agree with you so we need this conection for more flexibility

Thank you. I hope that My suggestion is will be beneficial

i Agree with you Mr.Mohamed

Good suggest, I Agree with you Mr.Mohamed

=IF($A8=1,SLN($A$1,$A$2,$A$3)*((DATEDIF($A$4,$A$5,"ym")+1)/12), IF($A8=$A$3+1, ((12-((DATEDIF($A$4,$A$5,"ym")+1)))/12)*SLN($A$1,$A$2,$A$3),SLN($A$1,$A$2,$A$3)))

----------------- simple formula

Thanks a lot Mr. Mohamed for your efforts and totally agree with your suggestions and hope your suggestions will be applied ISA.
We the Arabs, are very proud of the presence of people like you Mr. Mohamed Rifi, and Mr. Hossam Khattab who added a new function that he did, this function do what we want but by VBA, you all can see and try this new function on this link below
http://excel-egy.com/forum/t327
Hi Mohammed, I have an asset, it's basis for depreciation, and life, assuming the salvage value to be zero, can you help me with an automated excel formula that can calculate the month by month depreciation till the asset value becomes zero?

Thanks,
Arth