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

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: , [Australian Eastern Standard Time (AEST)]