Projected Depreciation in Excel

[Suhail]

I have to calculate monthly projected depreciation in Excel. I am facing TWO problems


1. I want excel stop calculation of Depreciation, when book value is is zero. In the attached file at column AF, book value is zero and after this excel calculates depreciation which leads to negative book value.


2. I have to calculate the depreciation on asset which is going to be purchase in future. If an asset would be purchase in Dec-12, then its depreciation automatically starts from Dec-12 instead start calculation from Aug-12.

Sample file is attached.


Your assistance required.

Thanks

Sohail

[Howardc]

Hi Sohail

Top simpify matters I added a row on the top with numbers and mended your depreciation formula and it now puts a blank in the dep col once the number of month depreciation has been reached-see attached workbook which I amended.

[Excel Fox]

I've kept it same, but corrected your month headers to start with the first day of each month.

And used this formula =IF(AND($C2<=E$1,(EOMONTH($C2,$B2-1)+1)>E$1),$D2/$B2,0)

[Howardc]

Hi Excelfox

Thanks for your input. This is a great way of computing the Depreciation for the number of periods. It's been a long time since I've used the EOMONTH formula

Regards

Howard

[Suhail]

Wonderful.

Thanks for your support.

Regards

Sohail

[Suhail]

Hi Excel Fox

This formula is working wonderfully. But I stuck in another problem. When I change day in purchase date and put any other day other than 1 day of the month, the formula does not depreciate the last month and there appears book value of last month which should be ZERO.

In the attached sheet, I have changed the purchase dated from 01-Aug-12 to 02-Aug-12, notice that in CELL AM2, the book value of last month is still appearing. Please resolve.

Can we set below criteria:

1. If purchase date appears between 1 to 15 days, the depreciation should be start with same month. For example if we purchase an assets between 1st Aug to 15 Aug then depreciation starts from Aug other wise it should be start from next month i.e. Sep


Regards


Sohail

[Suhail]

Hi Excel Fox

This formula is working wonderfully. But I stuck in another problem. When I change day in purchase date and put any other day other than 1 day of the month, the formula does not depreciate the last month and there appears book value of last month which should be ZERO.

In the attached sheet, I have changed the purchase dated from 01-Aug-12 to 02-Aug-12, notice that in CELL AM2, the book value of last month is still appearing. Please resolve.

Can we set below criteria:

1. If purchase date appears between 1 to 15 days, the depreciation should be start with same month. For example if we purchase an assets between 1st Aug to 15 Aug then depreciation starts from Aug other wise it should be start from next month i.e. Sep


Regards


Sohail

Dear All


Waiting for your support.

[ssgpatton@msn.com]

What about applying the mid-month convention to the formula? If the asset is purchased between 1 - 15, a full month of depreciation is calculated, if purchased from 16 - EOM, then only 50% of depreciation is expensed in the first month. Please advise. Thank you.



Hi Excel Fox

This formula is working wonderfully. But I stuck in another problem. When I change day in purchase date and put any other day other than 1 day of the month, the formula does not depreciate the last month and there appears book value of last month which should be ZERO.

In the attached sheet, I have changed the purchase dated from 01-Aug-12 to 02-Aug-12, notice that in CELL AM2, the book value of last month is still appearing. Please resolve.

Can we set below criteria:

1. If purchase date appears between 1 to 15 days, the depreciation should be start with same month. For example if we purchase an assets between 1st Aug to 15 Aug then depreciation starts from Aug other wise it should be start from next month i.e. Sep


Regards


Sohail