[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:
<tbody>
</tbody>
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.
[/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:
E | F | G | L | M | N | O | |
3 | UL (yrs) | Capitalization Date | Cost | 2010-06-30 | 2011-06-30 | 2012-06-30 | 2013-06-30 |
4 | 3 | 2010-06-10 | 116,499 | 2,128 | 38,833 | 38,939 | 36,599 |
<tbody>
</tbody>
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.