Calculation of monthly depreciation within limit of useful life (without VBA)

[askall1000]

Hello All, I need help about depreciation calculation, there is a lot of rows to calculate depreciation in J column. What i want to try to if there is more than 12 months left for depreciation for 2021 according to purchase date i want to write 12 months, if less than 12 months i want to write that number of month in sample it should write 3 month. if useful life exceeded it should write zero. I did zero and twelve month but i couldn't did less than 12 months. And I don't want to use helper column and there are lot of cells that have different useful life and purchase dates.Thank you very much for your help in advance.

Sample Data:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
2									31.12.2020	31.12.2021	31.12.2021	31.12.2021
3	Asset Type	Location	Office Unit	New Asset Code	New Asset Description	Total Value	Purchase Date	Useful Life (Month)	Acc. Amortization 31.12.2020	Depreciation&Amortization Expense (Month) 31.12.2021	Depreciation&Amortization Expense 31.12.2021	Acc. Amortization31.12.2021
4					xxxxxxxxxxxxx	2.000,00	15.04.2016	60,00	1.900,00	0,00	0,00	1.900,00
Sheet1

Cell Formulas
Range	Formula
I3	I3	="Acc. Amortization" &" "&TEXT(I2,"gg.aa.yyyy")
J3	J3	="Depreciation&Amortization Expense (Month) "&TEXT(J2,"gg.aa.yyyy")
K3	K3	="Depreciation&Amortization Expense "&TEXT(K2,"gg.aa.yyyy")
L3	L3	="Acc. Amortization"&TEXT(L2,"gg.aa.yyyy")
I4	I4	=+IFERROR(ROUND(IF(DATEDIF(((EOMONTH(G4,-1)+1)),$I$2,"m")>=H4,F4,(DATEDIF((EOMONTH(G4,-1)+1),$I$2,"m")+1)*F4/H4),2),0)
J4	J4	=+IF(DATEDIF(G4,$K$2,"m")>=H4,0,IF(DATEDIF(G4,$K$2,"m")>=12,12,DATEDIF(G4,$K$2,"m")+1))
K4	K4	=+ROUND(F4/H4*J4,2)
L4	L4	=+I4+K4

 

[Candyman8019]

Give this a try:
=IF(YEARFRAC(TODAY(),G4)*12>H4,"Zero",IF(ROUND(YEARFRAC(TODAY(),G4),2)&" months","12 months"))

NOTE: Column H will need to be a number and column G will need to be date format.

 

[askall1000]

Give this a try:
=IF(YEARFRAC(TODAY(),G4)*12>H4,"Zero",IF(ROUND(YEARFRAC(TODAY(),G4),2)&" months","12 months"))

NOTE: Column H will need to be a number and column G will need to be date format.

Thank you very much for your time and reply. But the result should be 3 months when i copy your formula it returns zero.

 

[Candyman8019]

check your values in column H and G to ensure they're in the correct format. I had to overwrite them with actual number format and date format values.

 

[askall1000]

check your values in column H and G to ensure they're in the correct format. I had to overwrite them with actual number format and date format values.

I checked H is number and G is date.

 

[Candyman8019]

April 15, 2016 to today is approx 68 months...which exceeds your useful life, hence the "zero"

 

[Candyman8019]

Rather than YearFrac, you could use DateDif formula; however, it returns the same value...68 months.

 

[askall1000]

April 15, 2016 to today is approx 68 months...which exceeds your useful life, hence the "zero"

Maybe my used formula in sample not clear, sorry for that but calculation is according to this:

April 15, 2016 to December 31=9 months
2017 to 2020 :12*4=48 months
total: 57 months
and for 2021 i need to depreciate 3 months more
so there should be 3 months but if useful life depreciated like if it is 68 no depreciation.

 

[askall1000]

Maybe my used formula in sample not clear, sorry for that but calculation is according to this:

April 15, 2016 to December 31=9 months
2017 to 2020 :12*4=48 months
total: 57 months
and for 2021 i need to depreciate 3 months more
so there should be 3 months but if useful life depreciated like if it is 68 no depreciation.

One note i would like to add: my calculation need to be as of december 31, 2021. not until today.

 

[askall1000]

Hello All, I come up with formula, I am sharing it for anybody in the future would be utilize somehow. And if anybody has shorter or easier alternative very very much appreciated.

Rapor_Metrohm 2020 Sabit Kıymet.xlsx
	B	C	D	E	F	G	H
2				31.12.2020	31.12.2021	31.12.2021	31.12.2021
3	Total Value	Purchase Date	Useful Life (Month)	Acc. Amortization 31.12.2020	Depreciation&Amortization Expense (Month) 31.12.2021	Depreciation&Amortization Expense 31.12.2021	Acc. Amortization31.12.2021
4	8.474,48	01.06.2019	60	2.683,59	12	1.694,90	4.378,49
5	2.650,00	23.07.2014	60,00	2.650,00	0	0,00	2.650,00
6	1.607,44	01.04.2016	60,00	1.527,07	3	80,37	1.607,44
7	2.000,00	01.03.2021	60,00	0,00	10	333,33	333,33
Sheet1 (2)

Cell Formulas
Range	Formula
E3	E3	="Acc. Amortization" &" "&TEXT(E2,"gg.aa.yyyy")
F3	F3	="Depreciation&Amortization Expense (Month) "&TEXT(F2,"gg.aa.yyyy")
G3	G3	="Depreciation&Amortization Expense "&TEXT(G2,"gg.aa.yyyy")
H3	H3	="Acc. Amortization"&TEXT(H2,"gg.aa.yyyy")
E4:E7	E4	=+IFERROR(ROUND(IF(DATEDIF(((EOMONTH(C4,-1)+1)),$E$2,"m")>=D4,B4,(DATEDIF((EOMONTH(C4,-1)+1),$E$2,"m")+1)*B4/D4),2),0)
F4:F7	F4	=+IF(YEAR(C4)>YEAR($E$2),DATEDIF(C4,$F$2,"m")+1,IF(AND(YEAR(C4)<=YEAR($E$2),DATEDIF(C4,$E$2,"m")>=D4),0,IF(AND(D4-DATEDIF(C4,$E$2,"m")+1<=12,D4-DATEDIF(C4,$E$2,"m")+1>0),D4-DATEDIF(C4,$E$2,"m")-1,12)))
G4:G7	G4	=+ROUND(B4/D4*F4,2)
H4:H7	H4	=+E4+G4