Depreciation tabel

[okvals48]

Hi,

i have one problem that i can not figure out. I have to make depreciation tabel for my forcast, but issue is that i have asset for wich i have to make depreciation for the whole year, for some it will start in certain point during a year, and for some it will end during the year. With X i have marked for what period depreciaiton should be calculated. Also i have entered 30.05.2021. as my reproting period.

I was trying to do calculation based on date when i accquired the asset with when asset will be depreciated (Column years) and my reporting period as criteria if we need to calculate depreciation.

Thanks in advance

					30.05.2021
ID	Asset number	Asset name	Depreciation Rate	Accquired	Years	Purchase value	Book value	1	2	3	4	5	6	7	8	9	10	11	12
1	211122	x	0.25	30.11.2020	4	100	80	X	X	X	X	X	X	X	X	X	X	X	X
2	311113	y	0.33	03.03.2021	3	100	95				X	X	X	X	X	X	X	X	X
3	411114	z	0.25	24.05.2018	4	100	25	X	X	X	X	X

 

[Saba Sabaratnam]

Hi

I assume that you are using straight line depreciation and you start to depreciate fully from the month of acquisition of your assets irrespective date of purchase in that month.

Insert a column and calculate end of depreciation date for each asset using the following formula and copy it down:

=DATE(YEAR(E5)+F5,MONTH(E5),DAY(E5))

Enter reporting start date in J4 as 31/1/2021 (can be changed) and then enter the following formula in K4 and copy it across:

=EOMONTH(J4,1)

Finally, enter the following formula in J5 and copy it down and across:

=IF(MEDIAN($E5,$G5,J$4)=J$4,$H5*$D5,"")

Kind regards

Saba

 

[okvals48]

Thanks a lot, i would never get idea to solve it with the help of Median. But one question, but my depreciation does not start the same month when i procured the asset insted it starts next month.
I could make another row ti Dep start date or add + 30 on cell referenc =IF(MEDIAN(($F6+30),$J6,N$4)=N$4,$H6*$E6,""), but that probably would not be the most optimal solution.

I was just wondering if there is anything more elegant,

 

[Saba Sabaratnam]

Or you could use the following formula

=IF(MEDIAN(EOMONTH($F6,0)+1,$J6,N$4)=N$4,$H6*$E6,"")

You can see the formula EOMONTH($F6,0)+1 takes 3 March to 1 of April month which be the start of depreciation month after asset acquisition in the previous month.

Kind regard

Saba