1. Histogram of stock returns
In Chapter 2, you looked at the historical returns to assess the past performance of stock ABC. In this new chapter, you'll see the returns from a different perspective by focusing on the empirical distribution.
2. Empirical distribution
The empirical distribution can be used to describe the sample of historical returns. It reports the number of times (also called frequency) past returns were observed within specific internals. For instance, it can report the number of times past returns were greater than or equal to 2% but smaller than 3%.
The best way to visualize an empirical distribution is with a histogram.
3. Histogram of historical returns
A histogram is a bar chart that has on the horizontal axis the set of intervals or possible values taken by the returns, and on the vertical axis, the absolute or relative frequency at which they occurred.
The plot thus provides a description about the "behavior" of past returns.
4. Histogram of historical returns: bins
To construct a histogram for past returns, you first need to split the range of past values into intervals, called bins.
The bins don't overlap and span the range of possible outcomes. For example, one bin can include all returns greater than or equal to 2% but smaller than 3%. The next bin can include returns greater than or equal to 3% but smaller than 4%, and so on.
5. Histogram of historical returns: bins
To construct the bins, and their respective lower and upper bounds, you can use a simple procedure.
First, compute the range of returns as the maximum minus the minimum historical values. In this case, 26.23%.
6. Histogram of historical returns: bins
Second, divide the range by the number of desired bins minus one; this gives the optimal width of each bin. With 30 bins, we get a width of 0.90%.
7. Histogram of historical returns: bins
You then start to construct each bin by the lower bound. For the first bin, it is the minimum value of -15.95%.
8. Histogram of historical returns: bins
To find the upper bound of the bin, simply add the width of 0.90% to the lower bound.
9. Histogram of historical returns: bins
The lower bound of the next bin is then simply the upper bound of the previous bin.
10. Histogram of historical returns: bins
You proceed this way by adding the width iteratively as for the first bin. You get regular bins spanning the whole range of past returns.
11. Histogram of historical returns: frequency
Once the mesh of bins is constructed, the next step is to count how many historical returns belong to each bin. Thus you compute the frequency of past returns for each bin.
12. Histogram of historical returns: frequency
To proceed, you count the number of past returns that are larger or equal than the lower bound and smaller than the upper bound. You do that for each bin. This is done with the function COUNTIFS() with two conditions, one for the lower bound and one for the upper bound.
13. Histogram of historical returns: chart
With the bins and the frequencies, you can then build the chart. To do so, you need to select the range of frequencies.
14. Histogram of historical returns: chart
Then go to "Insert", "Chart", and on the "Chart editor", under "Chart type", select "Column chart".
15. Histogram of historical returns: chart
In the chart editor, in addition to several customization functions, you can include the intervals on the horizontal axis.
16. Relative frequency
Instead of the frequency, you can also report the relative frequency. This is simply the frequency divided by the total number of observations. The relative frequency can be interpreted as a probability and compared with a theoretical model. We will see this in the next lesson.
17. Relative frequency
Considering the frequency or the relative frequency does not change the shape of the histogram. It modifies the height of the bars. The frequency is an absolute number. The relative frequency is a percentage.
18. It's time to practice!
It's your turn to build the histogram of returns. Time to practice!