Firstly, I don't know much about Natural Logs to begin with. I am actually an SQL developer and I have a query where I am calculating a 'Return' metric as follows:
Current Row 'Return' Value = Previous Row 'Return' Value + (Previous Row 'Return' Value * Current Row 'Rate' Value)
I have also uploaded an Excel file with some actual values of the table here. Column F shows how it is calculated at the moment.
Due to some technical reasons with MySQL, I need to remove the part where I reference the previous row. At stackoverflow someone suggested using Exponential function and Natural Logs, but their formula only works if the last_return column value is '1'. For anything else, it doesn't and I am not able to figure it out.
Kindly take a look and suggest the formula I'd need. If there is any other formula I can use, that'd be great too.
You have a sequence of some return values $\{x_k\}_{k=1}^n$ - probably daily yield. You have a starting value (let say portfolio initial value) - $V_0$. And some recursive formula to calculate $V_k$, as $$V_k = V_{k-1} \cdot x_k + V_{k-1}.$$
What you've being suggested is to write $$V_k = V_{k-1} \cdot (1 + x_k).$$ This helps tremendously, in case you iteratively rewrite the product to become $$V_k = \prod_{i = 1}^{k}(1+x_i) \cdot V_0$$
The question then becomes
Which you were hinted to calculate in the next way $$\prod\limits_{i = 1}^{k}(1+x_i) = e^{\ln\prod_{i=1}^{k}(1+x_i)} = e^{\sum_{i=1}^{k}\ln(1+x_i)}$$
You can stop here, if it is possible to execute in SQL. After that starts some approximation land, $\ln(1+x) \approx x - \frac{x^2}{2}$ for $x$'s close to $0$.
and $V_k$ could be roughly approximated by $V_0 \cdot e^{\sum_{i=1}^{k}x_i}$.