Can I compute the logarithmic return from a time series without any intermediate calculations?

Yes, you definitely can do so. To illustrate, Let's assume the price time series (i.e. $\textrm{P}_{t}$) are stored in A1:A300 range.

Logarithmic price ($p_t$)

$$p_{t}= ln(P_{t})$$

  • In Excel, in cell B1, type +LN(A1:A300) and hit return. This will compute the log of the first data cell.
  • select cell B1:B300, hit F2 to edit the formula, and ctrl+Alt+Enter together. This will populate the B1:B300 and format the formula as {=LOG(\$A\$1:\$A\$300)}

Logarithmic returns ($r_t$)

$$r_{t}= \ln(\frac{P_{t}}{P_{t-1}})= \ln(P_{t})-\ln(P_{t-1}) = (1-B)\ln(P_{t})$$

  • In Excel, in cell B1, type +DIFF(LN(\$A\$1:\$A\$300),1,1) and hit return. This will compute the log price and difference it next (i.e. log return).
  • select cell B1:B300, hit F2 to edit the formula, and ctrl+Alt+Enter together. This will populate the B1:B300 and format the fomula as {=DIFF(LN(\$A\$1:\$A\$300),1,1)}

Auto-correlation ($\rho_k$) 

  • In Excel, in cell B1, type +ACF(DIFF(LN( \$A\$1:\$A\$ 300),1,1),1,3) and hit return. This will compute the log return time series and the ACF on the derived series.
  • select cell B1:B300, hit F2 to edit the formula, and ctrl+Alt+Enter together. This will populate the B1:B300 and format the fomula as {=ACF(DIFF(LN( \$A\$ 1: \$A\$300),1,1),1,3)}

Comments

Please sign in to leave a comment.

Was this article helpful?
1 out of 11 found this helpful