The Common Stock Theorem in Excel

The Common Stock Theorem is a financial concept that relates the value of a company’s common stock to its other financial securities. Common stock represents a residual ownership stake in a company, the right to claim any other corporate assets after all other financial obligations have been met.

The theorem states that the value of common stock is equal to the present value of all future dividends paid to the shareholders, plus the present value of any proceeds from issuing new shares or repurchasing existing shares. The theorem also implies that the value of common stock is independent of the capital structure of the company, meaning that the mix of debt and equity does not affect the stock price.

The Common Stock Theorem can be used to estimate the cost of equity for a company, which is the required rate of return that investors demand for investing in the common stock. The cost of equity can be calculated by dividing the expected dividend per share by the current stock price, and adding the expected growth rate of dividends. Alternatively, the cost of equity can be estimated using the capital asset pricing model (CAPM), which considers the risk-free rate, the market risk premium, and the beta of the stock.

The Common Stock Theorem is based on some assumptions, such as perfect capital markets, no taxes, no transaction costs, and constant dividend growth rate. In reality, these assumptions may not hold, and the value of common stock may be affected by other factors, such as market sentiment, investor expectations, and information asymmetry. Therefore, the theorem should be applied with caution and supplemented with other valuation methods.

Basic Theory

The Common Stock Theorem is based on the principle that the value of a common stock is the present value of all future cash flows that an investor expects to receive from owning that stock. The formula for the Common Stock Theorem can be expressed as follows:

$\text{Common Stock Value} = \sum_{t=1}^{n} \frac{D_t + P_t}{(1+r)^t}$

Where:

$D_t$ is the expected dividends in period $t$ ,
$P_t$ is the expected stock price in period $t$ ,
$r$ is the required rate of return, and
$n$ is the number of periods.

Procedures

To apply the Common Stock Theorem in Excel, follow these steps:

Gather Data: Collect information on expected dividends ( $D_t$ ), expected stock prices ( $P_t$ ), the required rate of return ( $r$ ), and the number of periods ( $n$ ).
Create a Table in Excel: Set up a table with columns for period ( $t$ ), expected dividends, expected stock prices, and calculations.
Enter Data: Input the gathered data into the appropriate columns in the Excel table.
Calculate Present Value: Use the formula $\frac{D_t + P_t}{(1+r)^t}$ to calculate the present value for each period.
Sum Present Values: Sum all the present values to obtain the Common Stock Value.

Comprehensive Explanation

Let’s consider a scenario to illustrate the Common Stock Theorem. Company XYZ is expected to pay dividends and has projected stock prices for the next five years. The required rate of return is 8%.

Period	Expected Dividends	Expected Stock Price
1	$2.00	$25.00
2	$2.50	$28.00
3	$3.00	$32.00
4	$3.50	$36.00
5	$4.00	$40.00

Now, let’s calculate the Common Stock Value using the formula mentioned earlier.

$\text{Common Stock Value} = \sum_{t=1}^{5} \frac{D_t + P_t}{(1+0.08)^t}$

Excel Calculation

1. Create an Excel Table:

Period	Dividends	Stock Price	Present Value
1	$2.00	$25.00	=($B2+$C2)/(1+0.08)^A2
2	$2.50	$28.00	=($B3+$C3)/(1+0.08)^A3
3	$3.00	$32.00	=($B4+$C4)/(1+0.08)^A4
4	$3.50	$36.00	=($B5+$C5)/(1+0.08)^A5
5	$4.00	$40.00	=($B6+$C6)/(1+0.08)^A6
Total Common Stock Value			=SUM(D2:D6)

Input Data: Enter the expected dividends and stock prices into the respective columns.
Calculate Present Value: Use the formula mentioned to calculate the present value for each period.
Sum Present Values: Sum the present values to obtain the Common Stock Value.

Results

The calculated Common Stock Value for Company XYZ is the intrinsic value of its stock based on the provided dividends, stock prices, and the required rate of return.

$\text{Common Stock Value} = $92.75$

Other Approaches

While the Common Stock Theorem is a widely used approach, investors may also consider alternative valuation methods such as the Dividend Discount Model (DDM) or the Gordon Growth Model. These methods provide additional perspectives on stock valuation, and their application depends on the specific characteristics of the company being analyzed.