How to calculate and return exact number of days in a month based on criterias

Hi,

I am creating a spreadsheet for my depreciation schedule and I'm having problems on calculating the depreciation expense base on the number of days in a month.

Depreciation expense is calculated as =Opening WDV*Dep.Rate*(No.of days in the reporting month/365) since i'm calculating and reporting on a per month basis.

Under cell E2 of Sheet 1, I am to change the month on a monthly basis depending on which prior month I am working on. So in this case, I am to calculate expense for the month of Feb 2018. Please note that I need to calculate base on the exact number of days in a month.

Basically I need a formula under column K of Sheet 2 wherein,

1. If I acquire an asset on the same reporting month, and it is a day other than the first day of the month, then my formula in cell column K should be able to calculate base on the remaining days from the date the asset was acquired until the last day of that month. For example, the furniture. the number of days should be from 16/02/2018-28/02/2018.
2. If the asset is being depreciated over the course of the year on a normal basis, then it should just calculate base on the number of days for that month. Ex. Container - 01 Feb 2018 - 28 Feb 2018
3. If an asset is on it's last term, say in here for example, Tel Equipment was acquired on 20/02/2013, and it has a life of 5 years. on Feb 2018, it is supposed to be fully depreciated, thus, the number of days should only be from 01 Feb 2018 to 20 Feb 2018.

I hope this makes sense and I really hope someone could really help.

Originally Posted by FlameRetired

Try this in K6 and fill down. This is done with the understanding that the 1st of the reporting month is in J5 and the last in K5.

Formula:

Please Login or Register  to view this content.

wow this is great! I have a little problem though, I did a check to see if the expense will give the correct results, and it does except for when I am doing the last month in which the asset is fully depreciated. It returns a bigger amount. Please see Sheet 3. The amount should be just for $42.19 instead of $54.25 as returned by the formula,

So the max formula, it's supposed to force the final depreciation to give an amount which is not in excess of the acquisition value?
This is really really helpful. Thank you so so much for the time and effort. God bless you!