Generate two negatively correlated data in excel

Question

Let's say that we have two prices that are negatively correlated to each other, for instance we have price $p_1$ and we want to generate negatively correlated price $p_2$ with the following formula

$$p_2=k p_1+b\varepsilon$$

where $k$ is correlation coefficient, $b$ is noise level and $\epsilon$ is noise, but the problem is that for negative correlation $k$ must be less then zero right? but in this case price became negative which is non logical (every price must be non-negative number), so what should I do? Instead of

$$p_2=kp_1+b\varepsilon$$

should I use

$$p_2=a-kp_1+b \varepsilon \text{ ?}$$

where $a$ is intercept

Thanks in advance

Answer 1

Yes, you can use $$p_2 = a+kp_1+b\epsilon,$$ where $k$ is negative, but this might still give you negative prices: If the noise $\epsilon$ happens to be so large in the negative direction that $a+kp_1+b\epsilon<0$, then you get a negative price.

This is not a feature of this particular model. It occurs also for the model $p_2 = kp_1+b\epsilon$, even when $k$ is positive, since the noise can take any value, and make the expression negative (if this often happens is another matter, it will depend on your choice of $a$ and $b$, but it will always happen with some positive probability).

What I've seen in practice is to cap the price at some minimum value, e.g. you can use a model $$p_2 = \max(a+kp_1+b\epsilon,L),$$ where $L\geq 0$ is some lower bound that you do not want your price to fall below.

Answer 2

Adding a constant to $p_2$ will not change the correlation with $p_1$. So, you can feel free do that to keep $p_2 > 0$. However, $k$ is not exactly the correlation coefficient.

If $\sigma^2_1$ is the variance of $p_1$. Then $k \sigma^2_1$ is the covariance of $p_1$,$p_2$.

$$\sigma^2_2 = k^2 \sigma^2_1 + \epsilon^2\sigma^2_b$$

$$\rho = \frac{k\sigma_1}{\sigma_2}$$