Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Suggestion and the recommendation of special functions depreciation

Brass Contributor

# Suggestion and the recommendation of special functions depreciation

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)))```

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

# Re: Suggestion and the recommendation of special functions depreciation

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.

# Re: Suggestion and the recommendation of special functions depreciation

i Agree with you Mr.Mohamed

# Re: Suggestion and the recommendation of special functions depreciation

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

# Re: Suggestion and the recommendation of special functions depreciation

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

# Re: Suggestion and the recommendation of special functions depreciation

i Agree with you Mr.Mohamed

# Re: Suggestion and the recommendation of special functions depreciation

Good suggest, I Agree with you Mr.Mohamed

# Re: Suggestion and the recommendation of special functions depreciation

Thank you so much

# Re: Suggestion and the recommendation of special functions depreciation

GO on Sir God bless you

Nice work

# Re: Suggestion and the recommendation of special functions depreciation

=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

# Re: Suggestion and the recommendation of special functions depreciation

Thanks a lot Mr. Mohamed for your efforts and totally agree with your suggestions and hope your suggestions will be applied ISA.

# Re: Suggestion and the recommendation of special functions depreciation

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

# Re: Suggestion and the recommendation of special functions depreciation

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