Calculate depreciation

[anillinda]

[TABLE="width: 500"]
<tbody>[TR]
[TD]asset[/TD]
[TD]date purchased[/TD]
[TD]cost[/TD]
[TD]prior[/TD]
[TD]life[/TD]
[TD]current depreciation[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ipad[/TD]
[TD]08/5/1996[/TD]
[TD]2548[/TD]
[TD]1687[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am using the folliowing formula:
=INT(ROUND(IF(C4<(EDATE($B$1,-12)+1),SLN(D4,0,F4),IF(AND(C4>=EDATE($B$1,-12)+1,C4<=$B$1),SLN(D4,0,F4)/12*(DATEDIF(C4,$B$1,"m")))),0))

The result is incorrect
cell b1 has value "07/31/18"
Help would be appreciated, specially in calculating depreciation when the final depreciation is in the last year

 

[jorismoerings]

Hi,

take a look at this and see if this helps:

Book1
	B	C	D	E	F	G
1	31-7-2018
2
3	asset	date purchased	cost	prior	life	current depreciation
4	Ipad	8-5-1996	2548	1687	5	2548
Sheet2

Cell Formulas
Range	Formula
G4	=INT(ROUND(IF(C4<(EDATE($B$1,-12)+1),SLN(D4,0,F4)/12*DATEDIF(C4,MIN($B$1,EDATE(C4,F4*12)),"m"),IF(AND(C4>=EDATE($B$1,-12)+1,C4<=$B$1),SLN(D4,0,F4)/12*(DATEDIF(C4,$B$1,"m")))),0))