Plot the standard Gaussian model

The “standard” Gaussian model is a Gaussian model with a mean of zero and a standard deviation of one.

In this exercise, given a set of bins spanning a range of possible values, your goal is to compute and display the theoretical probabilities of each bin.

To do so, use the function NORMDIST(). It has four arguments:

x: The value for which you want to compute the density (or the cumulative probability).
m: The location (mean) of the Gaussian model.
s: The dispersion (standard deviation) of the Gaussian model.
cumulative: TRUE if you want the cumulative probability, FALSE if you want the density.

Remember that the probability is the surface below the density curve, and can be computed by taking the difference of the cumulative probability at the boundaries of the bin.

Diese Übung ist Teil des Kurses

Financial Analytics in Google Sheets

Anleitung zur Übung

In C3, use NORMDIST(UB, 0, 1, TRUE) minus NORMDIST(LB, 0, 1, TRUE)to compute the theoretical probability of the first bin. UB is the upper bound of the bin and LB is the lower bound of the bin.
In C4:C35, complete the series by using the autofill feature.
Display the probabilities using a column chart and place the series of bins (lower bound) on the horizontal axis. Include cells with titles (A2 and C2) in your chart.
Change the title to Gaussian model.

Interaktive Übung

Setze die Theorie in einer unserer interaktiven Übungen in die Praxis um

Diese Übung ist Teil des Kurses

Financial Analytics in Google Sheets

Geringe SchwierigkeitSchwierigkeitsgrad

4.8+

Kurs kostenlos starten

In the first chapter, you’ll be introduced to the problem: you have a time series of monthly (historical) prices for the hypothetical stock ABC from which you have to extract some meaningful information. You’ll be given some definitions (what is a stock? what are dividends?), and at the end of the chapter, you’ll be able to graphically represent the evolution of a stock price over a specific period.

Exercise 1: Introduction and first metrics Exercise 2: What is a stock?Exercise 3: Count prices and dividends Exercise 4: Find minimum and maximum prices Exercise 5: Find minimum and maximum dividends Exercise 6: Identifying dates with unusual prices Exercise 7: Find price at a given date Exercise 8: Find dates with minimum and maximum prices Exercise 9: Visualizing the price evolution Exercise 10: Plot a line chart of historical prices Exercise 11: Customize the chart Exercise 12: Highlight minimum and maximum prices

In this chapter, the core of the analysis will switch from historical prices to historical returns. You’ll learn (and compute) the main performance indicators of past returns, both in terms of reward and risk. Finally, you’ll be introduced to risk-adjusted performance measures: indicators that take into account both reward and risk.

Exercise 1: From prices to returns Exercise 2: What are dollar and percentage returns?Exercise 3: Compute historical returns Exercise 4: Count positive and negative returns Exercise 5: Reward metrics Exercise 6: Compute the average return Exercise 7: Compute the effective rate of return using PRODUCT() and COUNT()Exercise 8: Compute the effective rate of return using ARRAYFORMULA()Exercise 9: Risk metrics Exercise 10: Compute the volatility Exercise 11: Compute the volatility with STDEV()Exercise 12: Compute the historical value-at-risk Exercise 13: Risk-adjusted metrics Exercise 14: Compute the Sharpe ratio Exercise 15: Compute the semideviation Exercise 16: Compute the Sortino ratio

In this chapter, you'll look at the full distribution of historical returns. First, you’ll learn how to build a histogram to describe the distribution of historical returns. Second, you’ll be introduced to the Gaussian distribution, a commonly used model for stock returns. You'll visually inspect if the Gaussian model is reasonable for the ABC stock returns. Finally, you'll understand potential flaws with the Gaussian model.

Exercise 1: Histogram of stock returns Exercise 2: Define bins Exercise 3: Find the frequency of each bin Exercise 4: Convert frequencies into relative frequencies Exercise 5: Build the histogram Exercise 6: The Gaussian model Exercise 7: Plot the standard Gaussian model

Aktuelle Übung

Exercise 8: Change the location of the Gaussian model Exercise 9: Change the dispersion of the Gaussian model Exercise 10: Calibrating the Gaussian model Exercise 11: Calibrate the Gaussian model on historical returns Exercise 12: Overlay the Gaussian model to the empirical histogram Exercise 13: Compute the 5% value-at-risk from the Gaussian model Exercise 14: Limitations of the Gaussian model Exercise 15: What are the limitations of the Gaussian model?Exercise 16: Compute skewness and kurtosis of the historical returns

In this final chapter, you’ll benchmark ABC stock against a market index and verify whether ABC outperformed the benchmark or not. The comparison process will be done through several steps/metrics. First, you’ll analyze the cumulative wealth. Next, you’ll extend the comparison using different indicators such as Sharpe Ratio and Drawdown. Finally, you’ll examine the linear relation between ABC stock and the benchmark through the correlation coefficient. At the end of the chapter, you’ll be introduced to more powerful and advanced spreadsheet features that introduce interactivity in your analysis.

Exercise 1: Benchmarking Exercise 2: How to choose a good benchmark?Exercise 3: Compare the final wealth Exercise 4: Plot the cumulative wealth Exercise 5: Performance metrics comparison Exercise 6: Compare the Sharpe ratios Exercise 7: Compare the drawdowns Exercise 8: Compare the maximum drawdowns Exercise 9: Correlation analysis Exercise 10: Compute the correlation coefficient Exercise 11: Compute the rolling-window correlation Exercise 12: Creating the dashboard