The Average Quarterly Return Theorem in Excel

The Average Quarterly Return Theorem is a mathematical result that states that the average quarterly return of a portfolio is equal to the sum of the average quarterly returns of the individual assets in the portfolio, weighted by their proportions in the portfolio. This theorem implies that the quarterly return of a portfolio depends only on the quarterly returns of the individual assets, and not on how they are correlated with each other.

To illustrate this theorem, suppose you have a portfolio that consists of two assets, A and B, with weights of 60% and 40%, respectively. Suppose also that the quarterly returns of A and B are 10%, 15%, 5%, and 20%, and 8%, 12%, 4%, and 16%, respectively. Then, the average quarterly return of A is (10% + 15% + 5% + 20%) / 4 = 12.5%, and the average quarterly return of B is (8% + 12% + 4% + 16%) / 4 = 10%. Therefore, the average quarterly return of the portfolio is (0.6 x 12.5%) + (0.4 x 10%) = 11.5%.

The Average Quarterly Return Theorem can be generalized to any number of assets and any time period, as long as the returns are measured over the same interval. The theorem can be useful for simplifying the calculation of the expected return of a portfolio, as well as for comparing the performance of different portfolios over time. However, the theorem does not account for the risk or volatility of the portfolio, which depends on the covariance or correlation of the individual assets. Therefore, the theorem should be used with caution, and only as a first approximation of the portfolio return.

Basic Theory

The Average Quarterly Return (AQR) is a measure that assesses the average rate of return on an investment over each quarter within a given timeframe. It is particularly useful for analyzing the performance of financial instruments, such as stocks or mutual funds, in a systematic manner. The formula for calculating AQR is as follows:

$AQR = \left( \frac{{\text{{Ending Value}}}}{{\text{{Beginning Value}}}} \right)^{\frac{1}{n}} - 1$

Where:

Ending Value: the value of the investment at the end of the period.
Beginning Value: the value of the investment at the beginning of the period.
n: the number of quarters in the period.

Procedures

Input Data: Create a table in Excel with two columns – one for the beginning value and another for the ending value of the investment for each quarter.
Calculate Returns: In a new column, calculate the quarterly return for each period using the formula:
$\text{{Quarterly Return}} = \left( \frac{{\text{{Ending Value}}}}{{\text{{Beginning Value}}}} \right) - 1$
Average Quarterly Return Formula: Use the AVERAGE function in Excel to find the average of the quarterly returns. The formula would be:
$\text{{AQR}} = \text{{AVERAGE}}(\text{{Quarterly Returns}})$
Interpret Results: Analyze the AQR to understand the average performance of the investment over the specified period.

Comprehensive Explanation

Let’s consider a scenario where an investor started with $10,000 and recorded the following ending values for each quarter over a year:

Quarter	Beginning Value ($)	Ending Value ($)
Q1	$10,000	$11,500
Q2	$11,500	$12,800
Q3	$12,800	$10,500
Q4	$10,500	$14,000

Calculation

1. Quarterly Returns:

$\text{{Q1 Return}} = \left( \frac{{11,500}}{{10,000}} \right) - 1 = 0.15$
$\text{{Q2 Return}} = \left( \frac{{12,800}}{{11,500}} \right) - 1 \approx 0.113$
$\text{{Q3 Return}} = \left( \frac{{10,500}}{{12,800}} \right) - 1 \approx -0.179$
$\text{{Q4 Return}} = \left( \frac{{14,000}}{{10,500}} \right) - 1 \approx 0.333$

2. Average Quarterly Return (AQR):

$\text{{AQR}} = \text{{AVERAGE}}(0.15, 0.113, -0.179, 0.333) \approx 0.104$

Interpretation

The calculated AQR of approximately 10.4% indicates the average quarterly return for the investment over the one-year period.

Other Approaches

Compounded Annual Growth Rate (CAGR): Another method to assess the performance of an investment is CAGR, which considers the compounding effect. The formula for CAGR is:
$CAGR = \left( \frac{{\text{{Ending Value}}}}{{\text{{Beginning Value}}}} \right)^{\frac{1}{n}} - 1$

CAGR can be useful for comparing investments with different timeframes.
Volatility Analysis: Combining AQR with measures of volatility, such as standard deviation, provides a more comprehensive view of risk-adjusted returns.