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))

	A	B	C	D	E	F	G
1	Asset	Machinery
2	Book Value	27500
3	Recovery Period	5
4	Placed into Service	12/10/2002
5	Method	Straight Line
6	Fiscal year End	07/31/2003
7	Years	Asset	Cost	Prior Depreciation	Current Depreciation
8	1	Machinery	27500	0	3208
9	2	Machinery	27500	3208	5500
10	3	Machinery	27500	8708	5500
11	4	Machinery	27500	14208	5500
12	5	Machinery	27500	19708	5500
13	6	Machinery	27500	25208	5500	THIS SHOULD BE LESS THAN 5500/-

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 208px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

5

<tbody>
</tbody>

</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.