Dynamic asset depreciation calculation

[David W 123]

Hi everyone - I was hoping someone could please help me (this is my first time posting so fingers crossed!)

I have a list of assets (many thousand) with different purchase dates. I would like the cost to be depreciated over 12 months in line with the top table (% varies by month). I've manually completed asset number 1 as an example.

Is there a nice formula that could please help me do this analysis for several thousand assets?

Many thanks!

 

[Eric W]

Welcome to the forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1			Depreciation Schedule (per month)
2			1	2	3	4	5	6	7	8	9	10	11	12
3			2%	2%	4%	6%	8%	10%	12%	16%	18%	14%	6%	2%
4
5
6		Asset Cost	Purchase date		Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22	May-22	Jun-22		Total
7	1	10000	Aug-20		$ -	$ 200.00	$ 200.00	$ 400.00	$ 600.00	$ 800.00	$ 1,000.00	$ 1,200.00	$ 1,600.00	$ 1,800.00	$ 1,400.00	$ 600.00	$ 200.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -		$ 10,000.00
8	2	12000	Oct-20		$ -	$ -	$ -	$ 240.00	$ 240.00	$ 480.00	$ 720.00	$ 960.00	$ 1,200.00	$ 1,440.00	$ 1,920.00	$ 2,160.00	$ 1,680.00	$ 720.00	$ 240.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -		$ 12,000.00
9	3	6500	Nov-20		$ -	$ -	$ -	$ -	$ 130.00	$ 130.00	$ 260.00	$ 390.00	$ 520.00	$ 650.00	$ 780.00	$ 1,040.00	$ 1,170.00	$ 910.00	$ 390.00	$ 130.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -		$ 6,500.00
10	4	18000	Mar-21		$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 360.00	$ 360.00	$ 720.00	$ 1,080.00	$ 1,440.00	$ 1,800.00	$ 2,160.00	$ 2,880.00	$ 3,240.00	$ 2,520.00	$ 1,080.00	$ 360.00	$ -	$ -	$ -	$ -		$ 18,000.00
11	5	15000	Apr-21		$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ 300.00	$ 300.00	$ 600.00	$ 900.00	$ 1,200.00	$ 1,500.00	$ 1,800.00	$ 2,400.00	$ 2,700.00	$ 2,100.00	$ 900.00	$ 300.00	$ -	$ -	$ -		$ 15,000.00
Sheet23

Cell Formulas
Range	Formula
E7:AB11	E7	=$B7*IFERROR(INDEX($C$3:$N$3,DATEDIF($C7,E$6,"m")+1),0)
AD7:AD11	AD7	=SUM(E7:AB7)

For this I assumed that the values in E6:AB6 and C7:C11 are actual Excel dates, and that they refer to the first of the month. They are just formatted to show month-year. If your actual dates are otherwise, we might have to adjust things.

 

[David W 123]

Hi Eric
Thank you so much - this is ideal and you're a star!
Many many thanks!
David

 

[Eric W]

Happy to help!