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.

Published: 10 August 2015
Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]