Rate of return

1. Discrete returns

Let \(P_t\) be the asset price at time \(t\) and \(P_{t-1}\) be the price in the prior period – day, week, month, … For daily returns on stocks, \(P_{t-1}\) is the closing price of the stock on the previous trading day.

Example calculations are provided in figure 1 – Excel Web App #1 – Sheet1, where a sample of stock price data is used.

1.1 Net return

  • Also called “simple returns”
  • Measured by the change in price \(\Delta P\), divided by the price at the start of the period
  • \(R_t = \left(P_t – P_{t-1} \right) / P_{t-1} = \left( P_t / P_{t-1} \right) – 1 \)
  • With dividends: \(R_{d,t} = \left( P_t – P_{t-1} + D_t \right) / P_{t-1} \)
  • Rows 12 to 15 of figure 1

1.2 Gross return

  • \(R_{g,t} = P_t / P_{t-1} = R_t + 1 \)
  • With dividends: \(R_{g,d,t} = \left( P_t + D_t \right) / P_{t-1} \)
  • Rows 17 to 20 of figure 1
  • Fig 1: Excel Web App #1 Rate of return – with discrete and continuous methods

2. Log returns

  • Uses continuously compounded returns
  • Often written as log \(r_t = \log P_t / P_{t-1} \), but uses natural logs. The LN function in Excel, and the LOG function in VBA
  • Natural logs have base 2.71828. This is referred to as Euler’s number, denoted by \(e\)
  • Log return: \(r_t = \ln P_t / P_{t-1} = \ln P_t-\ln P_{t-1} \)
  • \(\ln P_t\) is called the “log price”
  • With dividends: \(r_{d,t} = \ln \left(P_t + D_t \right) / P_{t-1} = \ln (P_t + D_t) -\ln P_{t-1} \)
  • Rows 24 to 28 of figure 1
  • Converting net returns to log returns: \(r_t = \ln(1 + R_t)\)
  • Converting log returns to net returns: \(R_t = e^{r_t} – 1 \)
  • Rows 31 to 34 of figure 1

3. Returns method selector panel

  • Cell D39 contains a Data Validation selector
  • Validation criteria - Allow: List
  • Validation criteria - Source: discrete, log
  • Cell formula – range D43:D54=IF(Return_method="discrete",(RC[-1]-R[1]C[-1])/R[1]C[-1],LN(RC[-1])-LN(R[1]C[-1]))

4. Cumulative returns

4.1 Cumulative discrete returns

  • Denote \(R_{c,t}\) as the cumulative discrete return as time \(t\)
  • Cumulative discrete returns are multiplicative
  • In vector form for the 12 month period: \(\left(1+R_{c,t-12-1}\right) \times \left(1+R_{t-12}\right)-1, \left(1+R_{c,t-12}\right) \times \left(1+R_{t-11}\right)-1, \left(1+R_{c,t-11}\right) \times \left(1+R_{t-10}\right)-1, …\)
  • \(R_{c,t}\) for 12 months at 1 July 15 = 0.0072 (Cell E62). This is the same value as the Annual Discrete return for the single 12 month period – Cell E76: 0.0072

4.2 Cumulative log returns

  • Denote \(r_{c,t}\) as the cumulative log return as time \(t\)
  • Cumulative log returns are additive
  • In vector form for the 12 month period \(\left(r_{c,t-12-1} + r_{t-12}\right), \left(r_{c,t-12} + r_{t-11} \right), \left(r_{c,t-11} + r_{t-10} \right), … \)
  • \(r_{c,t}\) for 12 months at 1 July 15 = 0.0072 (Cell E81). This is the same value as the Annual Log return for the single 12 month period – Cell E95: 0.0072

  • This example was developed in Excel 2013 Pro 64 bit.
  • Last modified: 17 Aug 2015, 7:21 pm [Australian Eastern Standard Time (AEST)]