Estimating Parameters of Random Variables in Microsoft Excel

Estimates of parameters of random variables are values that approximate the true characteristics of the underlying probability distributions. For example, if we have a random variable X that follows a normal distribution, we might want to estimate its mean and variance, which are the parameters of the normal distribution. There are different methods to obtain these estimates, such as maximum likelihood estimation (MLE) and method of moments (MoM). These methods use data from a sample of observations of X to calculate the estimates. The idea is to choose the estimates that make the data most likely or consistent with the theoretical moments of the distribution.

Basic Theory:

In statistics, parameters are numerical characteristics that describe a population. For random variables, two common parameters are the mean (μ) and the standard deviation (σ). The sample mean ( $\bar{x}$ ) and the sample standard deviation (s) are used as estimates for these parameters.

The formulas for estimating parameters are as follows:

Mean ( $\bar{x}$ ) Estimation:
$\bar{x} = \frac{\sum_{i=1}^{n} x_i}{n}$
Standard Deviation (s) Estimation:
$s = \sqrt{\frac{\sum_{i=1}^{n} (x_i - \bar{x})^2}{n-1}}$

Procedures:

Data Collection:
Gather a set of data points (random variable observations) that represent the population or sample.
Compute the Mean:
Use the formula to calculate the mean ( $\bar{x}$ ).
Compute the Standard Deviation:
Apply the formula to find the standard deviation (s).
Excel Implementation:
Utilize Excel functions like AVERAGE for mean and STDEV.S for standard deviation to streamline the calculations.

Scenario:

Consider a scenario where you are analyzing the monthly returns of a stock for the past 12 months. The returns are as follows: 2.5%, -1.2%, 3.8%, -0.5%, 1.9%, -2.3%, 4.2%, 0.8%, -1.0%, 2.1%, -3.5%, 5.0%

Excel Implementation:

1. Create an Excel table with the data:

Month	Return (%)
Jan	2.5
Feb	-1.2
Dec	5.0

Use Excel formulas:
- Cell for Mean: =AVERAGE(B2:B13)
- Cell for Standard Deviation: =STDEV.S(B2:B13)

Results:

1. Mean ( $\bar{x}$ ) = 1.025%

2. Standard Deviation (s) = 2.731%

These results provide estimates of the average monthly return and the variability of the stock’s returns based on the given data.

Other Approaches:

Confidence Intervals:
Calculate confidence intervals to express the uncertainty around the point estimates. Excel functions like CONFIDENCE can be useful.
Histograms:
Visualize the distribution of the data using a histogram (Excel’s HISTOGRAM tool) to gain insights into its shape.
Regression Analysis:
For more complex relationships, use Excel’s regression analysis tools to estimate parameters and understand the relationships between variables.