Probablility calculation related to start date

[Hannes]

Here is my problem:
I would like to calculate a value X which gets in effect at a specific date
T with a probability P. From chosen date D, the probability P increases 0.05
per month till the date when P=1. From that date on the output is X (because
P=1). This should be realized so that a user inputs the value, the start date
and the increase rate of the probability.

Example:
Input: value=50, start date=jan09, probability rate=0.05
Result:
Jan-09: x=2.5; Feb-09: x=5; .....; Jul-10: x=47.5; Aug-10: x=50; Sep-10:
x=50; Oct-10: x=50; .....; Dez-12: x=50;

The calculations should be based on the variable inputs.

I hope my description is clear enough! Thanks a lot!

 

[Joel]

What you havve is a good old Y=mx + b problem with b = 0 and m = 0.05. The
formula is

=.05 * t
where t is in months. the problem is how do you define a month? Is it 30
days? This gets you only 360 days in a year. Or is it 365/12 or is it
366/12 (leap years).

If you use 365/12 then the formula is

=.05 * 12.0 * (t2 - t1)/365.0
T1 is the start datte and T2 is the current date.


Note: Probaility is not usually calculate as a linear function. It is an
exponential function (success) P(s) = e ** (-Rt) where R is a failure rate
and t is time. Probabilty of failure P(f) = 1 - P(s)

 

[joeu2004]

Here is my problem:
I would like to calculate a value X which gets in effect at a specific date
T with a probability P. From chosen date D, the probability P increases 0.05
per month till the date when P=1. From that date on the output is X (because
P=1).
[....]
Example:
Input: value=50, start date=jan09, probability rate=0.05
Result:
Jan-09: x=2.5; Feb-09: x=5; .....; Jul-10: x=47.5; Aug-10: x=50; Sep-10:
x=50; Oct-10: x=50; .....; Dez-12: x=50;

Suppose A1 is the "probability rate" (i.e. initial probability and
increment), B1 is the value ("X"), and C1 is the start date. Then:

D1: =$B$1*min(1, $A$1*(row()-row($A$1)+1))
C2: =date(year(C1), 1+month(C1), 1)
D2: =$B$1*min(1, $A$1*(row()-row($A$1)+1))

Copy C22 down for as many months as desired. Format column C as
Custom mmm-yy.

Note: It was not clear to me whether the user inputs just the initial
probability, which always increases by 0.05. If that is the case,
change D1 and D2 to:

=$B$1*min(1, $A$1+0.05*(row()-row($A$1)))

 

[joeu2004]

Errata....

D1: =$B$1*min(1, $A$1*(row()-row($A$1)+1))
C2: =date(year(C1), 1+month(C1), 1)
D2: =$B$1*min(1, $A$1*(row()-row($A$1)+1))

It does not matter in my schema, but just for clarity, the formula in
D1 and D2 should be:

=$B$1*min(1, $A$1*(row()-row($D$1)+1))