Current Depreciation Not to exceed cost of asset

[anillinda]

I would like to calculate current depreciation which should not exceed the cost of the asset, which is in cell b2.
Thanks.

The current depreciation is being calculated as under:
=IF(A8="","",ROUND(IF(A8=1,SLN($B$2,0,$B$3)/12*DATEDIF($B$4,$B$6,"M"),SLN($B$2,0,$B$3)),0))



[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Asset[/TD]
[TD]Machinery[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Book Value[/TD]
[TD]27500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Recovery Period[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Placed into Service[/TD]
[TD]12/10/2002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Method[/TD]
[TD]Straight Line[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Fiscal year End[/TD]
[TD]07/31/2003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Years[/TD]
[TD]Asset[/TD]
[TD]Cost[/TD]
[TD]Prior Depreciation[/TD]
[TD]Current Depreciation[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]Machinery[/TD]
[TD]27500[/TD]
[TD]0[/TD]
[TD]3208[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2[/TD]
[TD]Machinery[/TD]
[TD]27500[/TD]
[TD]3208[/TD]
[TD]5500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3[/TD]
[TD]Machinery[/TD]
[TD]27500[/TD]
[TD]8708[/TD]
[TD]5500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4[/TD]
[TD]Machinery[/TD]
[TD]27500[/TD]
[TD]14208[/TD]
[TD]5500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]Machinery[/TD]
[TD]27500[/TD]
[TD]19708[/TD]
[TD]5500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6[/TD]
[TD]Machinery[/TD]
[TD]27500[/TD]
[TD]25208[/TD]
[TD]5500[/TD]
[TD]THIS SHOULD BE LESS THAN 5500/-[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 208px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[joeu2004]

Assuming that the formula in D8 is =IF(A8="", "", SUM(D7,E7)) , which returns zero if D7 and E7 are text (see D8), the formula in E8 should be:

=IF(A8="", "", MIN(C8-D8, ROUND(IF(A8=1, SLN($B$2,0,$B$3)*DATEDIF($B$4,$B$6,"M")/12, SLN($B$2,0,$B$3)),0)))

Note the subtle reordering of the "/12" term. IMHO, that makes your intention clearer, to wit: prorate SLN by fraction of year.

Aside.... You might want to look at the half-year convention to see if it applies. The MIN term works in either case.