Depreciation over variable number of months

[Arie Bos]

Dear Excel world,
I have an amount to be depreciated over X month. X is a variable so the user can choose the number of months.
So, 1200 dollar in (X=12) months = 100/month, and the 13th cell should know the total has been absorbed bij the 12 months amount, so that cell should show zero, as there is nothingh more to depreciate from that month on. But if the user chooses X=24, the monthly amount is 50 dollars, and cell 25 should show the zero.

I think it is not difficult to make an IF formula in every cell in this row, but they would be all different, which is not elegant. So is there a function in Excel that can do this? or how should I compose this as a general formula, and not as a per-cell formula?

Many thanks in advance,

 

[etaf]

i'm sure better ways - BUT SLN() is a depreciation function in excel

so if you enter SLN($A2,0,$B2) in the first month and then =IF(SUM($C2:C2)=$A2,"",SLN($A2,0,$B2))
Where A2 is the amount and B2 the time

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	amount	months	Mth1	Mth2	Mth3	Mth4	Mth5	Mth6	Mth7	Mth8	Mth9	Mth10	Mth11	Mth12	Mth13	Mth14	Mth15	Mth16	Mth17	Mth18	Mth19	Mth20	Mth21
2	1200	12	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00	$100.00
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SLN($A2,0,$B2)
D2:W2	D2	=IF(SUM($C2:C2)=$A2,"",SLN($A2,0,$B2))

as i say maybe better ways - with VBA to actually write the first amount and use
With Excel - I get a cicular reference if i try and use SLN and SUM on first cell

 

[6StringJazzer]

Hard to give you a specific solution without knowing how you have your data set up. But here is the idea:

12 periods:

$scratch.xlsm
	A	B	C	D	E
1	Amount:	$1,200.00		Period	Depreciation
2	Number of Periods:	12		1	$100.00
3				2	$100.00
4				3	$100.00
5				4	$100.00
6				5	$100.00
7				6	$100.00
8				7	$100.00
9				8	$100.00
10				9	$100.00
11				10	$100.00
12				11	$100.00
13				12	$100.00
14				13	$0.00
15				14	$0.00
depreciation

Cell Formulas
Range	Formula
E2:E15	E2	=IF(D2<=$B$2,$B$1/$B$2,0)

24 periods:

$scratch.xlsm
	A	B	C	D	E
1	Amount:	$1,200.00		Period	Depreciation
2	Number of Periods:	24		1	$50.00
3				2	$50.00
4				3	$50.00
5				4	$50.00
6				5	$50.00
7				6	$50.00
8				7	$50.00
9				8	$50.00
10				9	$50.00
11				10	$50.00
12				11	$50.00
13				12	$50.00
14				13	$50.00
15				14	$50.00
16				15	$50.00
17				16	$50.00
18				17	$50.00
19				18	$50.00
20				19	$50.00
21				20	$50.00
22				21	$50.00
23				22	$50.00
24				23	$50.00
25				24	$50.00
26				25	$0.00
27				26	$0.00
depreciation

Cell Formulas
Range	Formula
E2:E27	E2	=IF(D2<=$B$2,$B$1/$B$2,0)

 

[Arie Bos]

Wayne! You are a rocket!... Thank you for your super quick help on this. Much much much appreciated!.
Arie

 

[Arie Bos]

And Jeff, same to you... Many thanks for helping out so fast!

 

[etaf]

you are welcome
hope it does answer your question, as was not sure

i think excel has 7 different built in depreciation functions - years since i have used in a work environment

 

[Arie Bos]

Wow... time for me to get better educated... thanks again.