Proportionate Depreciation per Month

[AugBB]

[FONT=Arial, Helvetica, sans-serif]I'm trying to break down the annual straight line depreciation into a more granular level of monthly depreciation for thousands of assets. Also considering year end is June 30th, not December 31st.

[/FONT]What I am trying to do is to find a formula that can calculate depreciation per month, prorating the depreciation if purchased in the middle of the month.

What I have thus far:
[TABLE="width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]UL (yrs)[/TD]
[TD]Capitalization Date[/TD]
[TD]Cost[/TD]
[TD]2010-06-30[/TD]
[TD]2011-06-30[/TD]
[TD]2012-06-30[/TD]
[TD]2013-06-30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]2010-06-10[/TD]
[TD]116,499[/TD]
[TD]2,128[/TD]
[TD]38,833[/TD]
[TD]38,939[/TD]
[TD]36,599[/TD]
[/TR]
</tbody>[/TABLE]

Formula in cell L4 is =IF(L$3-$F4>$E4*365,$G4-SUM($K4:K4),IF(L$3-$F4<0,0,SLN($G4,0,$E4*365)*(L$3-$F4)-SUM($K4:K4)))
Formula in cell M4 is =IF(M$3-$F4>$E4*365,$G4-SUM($K4:L4),IF(M$3-$F4<0,0,SLN($G4,0,$E4*365)*(M$3-$F4)-SUM($K4:L4)))
Formula in cell N4 is =IF(N$3-$F4>$E4*365,$G4-SUM($K4:M4),IF(N$3-$F4<0,0,SLN($G4,0,$E4*365)*(N$3-$F4)-SUM($K4:M4)))
Formula in cell O4 is =IF(O$3-$F4>$E4*365,$G4-SUM($K4:N4),IF(O$3-$F4<0,0,SLN($G4,0,$E4*365)*(O$3-$F4)-SUM($K4:N4)))

What I would like to see is depreciation per month instead of per year. For example depreciation for, 2010-06-30 then 2010-07-31, 2010-08-31, 2010-09-30, etc. All the way until the last month of depreciation in 2013.

 

[konew1]

Try this in L4 and copy across and down
=IF(L$3-$F4>$E4*365,($G4-SUM($K4:K4))/(B4*12-(DATEDIF($F4,K$3,"m")+1)),IF(L$3-$F4<0,0,SLN($G4,0,$E4*365)*(L$3-$F4)-SUM($K4:K4))/IF($F4>DATE(YEAR(L$3)-1,6,30),DATEDIF($F4,L$3,"M")+1,12))
I suggest that you consider a column for salvage value, rather than hard coding a zero into the formula, unless you are confident that every asset will be zero salvage

 

[AugBB]

Thank you for your reply. Something doesn't look right.

In the formula, what is cell B4 referencing?

=IF(L$3-$F4>$E4*365,($G4-SUM($K4:K4))/(B4*12-(DATEDIF($F4,K$3,"m")+1)),IF(L$3-$F4<0,0,SLN($G4,0,$E4*365)*(L$3-$F4)-SUM($K4:K4))/IF($F4>DATE(YEAR(L$3)-1,6,30),DATEDIF($F4,L$3,"M")+1,12))

The annual depreciation for 2011 should total $38,833, but when adding up the months using this formula it only equates to $19,404.

Partial depreciation should be charged in the month of acquisition and thereafter it should calculate equal monthly depreciation expense until the final month in 2013. In the final month of 2013 it should calculate partial depreciation once again.

 

[konew1]

Sorry, beginners error. I set up the formula in O4 and copied back to L4 without $
Try this in L4
=IF(L$3-$F4>$E4*365,($G4-SUM($K4:K4))/($E4*12-(DATEDIF($F4,K$3,"m")+1)),IF(L$3-$F4<0,0,SLN($G4,0,$E4*365)*(L$3-$F4)-SUM($K4:K4))/IF($F4>DATE(YEAR(L$3)-1,6,30),DATEDIF($F4,L$3,"M")+1,12))

 

[evan88]

hi , what is column K?