Recursion

[Chaddyb]

I have some data that i want modeled over the course of the year.
The model depreciates a value by a set percentage and a set number and then multiplies it by a set value

Eg.
A0 = A*5
A1 = (A0 - A0*B -C)*5
A2 =( A1 - A1*B-C)*5
etc
eg
A= 10000
B = 10%
C = 100
January = 50000
Feb = 44500
March =39550
etc

I know this can be done by setting up a table with the values. I am wondering if this can be done all in a formula, whether through standard excel or VBA

 

[StephenCrump]

Your formula and numbers don't match. Did you mean
A1 = A0 - A0*B -C*5 ?

If so, the Nth term (starting from N=0) will be:

5A(1-B)^N-50C(1-(1-B)^N)

 

[Paul Ked]

Book1
	A	B	C
1	10000	10.00%	100
2
3	A0	50000
4	A1	44500
5	A2	39950
6	A3	35855
7	A4	32169.5
8	A5	28852.55
9	A6	25867.295
10	A7	23180.5655
Sheet1

Cell Formulas
Range	Formula
B3	B3	=A1*5
B4	B4	=(A1-A1*B1-C1)*5
B5:B10	B5	=(B4-B4*B$1-C$1)

 

[Chaddyb]

Your formula and numbers don't match. Did you mean
A1 = A0 - A0*B -C*5 ?

If so, the Nth term (starting from N=0) will be:

5A(1-B)^N-50C(1-(1-B)^N)

Hi,

No I mean the entire term is then multiplied by 5
not just C

 

[StephenCrump]

No I mean the entire term is then multiplied by 5 not just C

In simple terms, I can replicate your numbers:
A1 = 50,000
A2 = 90% A1 - 500 = 44,500
A3 = 90% A2 - 500 = 39,550

These don't match the formulae you've provided.

My formula for An produces the next two values as 35,095 (n=3) and 31,085.50 (n = 4). Are these the numbers you want?