Albert Einstein is famously quoted for saying that "Compound interest is the 8th wonder of the world. He who understands it, earns it; he who doesn't, pays it". Thankfully none of us need to be Albert Einstein to understand the rather simple and yet powerful concept of Compound Interest. There are a lot of compound interest calculators out there. I particularly like the Compound Interest Calculator from Investor.gov. The main reason I like this calculator is that it allows you to include Monthly Contributions, which if you are a serious investor, should be part of your strategy. Let's take a look at how it works. First, you need to enter some general information such as starting amount, monthly contribution, length of time and estimated interest rate. For this example, I will use the numbers below:
Compound Interest Calculator with Contribution
Why use a Design-of-Experiment (DOE) Matrix
The word Design-of-Experiment, or DOE, has become very popular among engineers. However, often times this word is misused and misunderstood. The principles of DOE are based on the capabilities and limitations of the analysis tools that will be used to process the data and determine cause and effect. Sometimes proper DOE matrix are followed but adequate analysis is not performed. Other times, a proper DOE matrix is not followed but yet we try to use analysis methods that would have required a proper DOE matrix. Even more confusing is when we use the word DOE when neither a DOE matrix or analysis method was followed.
First, it is important to acknowledge that DOE results are intended to be analyzed using regression analysis and something called "response surface method" or RSM. These methods rely on using a proper DOE matrix to help with variable independence or orthogonality and statistical significance.
Let's take a look at the following example. Say you have 4 variables in a process (A, B, C and D) and you want to understand their impact in your process outcome. You conduct the following study where -1, 0, and 1 represent each variable min, middle, and max value:
You may be tempted to say that you have conducted a DOE. However, the table above has not followed any proper DOE matrix design. Even with the table above, it will be difficult to understand the effect of each variable (main effects) since some variables are strongly correlated. For example, let's take a look at the relationship between Parameter A and Parameter B:
Monte Carlo Analysis: Right Triangle
Here is a very simple problem. Assume you want to build a right triangle of known base (10) and height (3) dimension. However, the sigma tolerance of these dimensions are 1% of its value. Your goal is to find the variability that you will obtain for the dimension of the hypotenuse and the largest of the two complementary angles. It is straight forward to do some simple math to figure out the nominal value for the two variables of interest. This is shown in the figure below:
Note that the variables of interest are not linear with respect to the known variables. Therefore, it is not possible to simply add the variances of the two known dimensions. It is possible to use the principles of propagation of uncertainty to propagate the known variation through the non-linear equations. This exercise will be left to the reader. Here, I will show how you can quickly use my Excel Monte Carlo Workbook to solve this problem. First, download the free version of this workbook or if you wish, you can also purchase the non free version here. Set up the problem with 2 input variables of normal distribution with the given mean and sigma values. The inputs will look like this:
Finally, simply compute the average and standard deviation for the variables of interest. The results are the following:
As mentioned earlier, if you follow the principles of uncertainty propagation using partial derivatives, you will arrive at this same result (Rule No. 5 here). This is shown in the table above under the Analytical column. Using the Monte Carlo Excel you can also visualize the distribution of these two variables.
Monte Carlo: Roll 2 Dice
Assuming a fair die, each die has six different possible output values (1, 2, 3, 4, 5, and 6) and the probability of any of these values should be equal (1/6). The probability function that describes this behavior is the uniform discrete probability function with a lower bound of 1 and an upper bound of 6. The reason that this is a discrete probability is that in between values that are not integers (such as 1.5) are not an option.
Using my Monte Carlo Simulation Excel file to simulate the sum of two dice is very straight forward. First, click "0. Reset" in the Simulator sheet to make sure you are starting with a fresh and clean sheet. Indicate that the number of inputs is 2 by typing this value in cell F5. Remember, each input will represent one die. Now, click on "1. Define Inputs" and select the Uniform_Discrete probability function for both inputs with 1 as the lower bound (parameter 1) and 6 as the upper bound (parameter 2). Leave the other parameters empty. Your Simulator sheet should look like this:
Once the simulation is completed, there are a couple of things to do. First, in the "Y=" column, you will want to type the equation which is basically the sum of die 1 and die 2. This should look like this:
First, let's explore the output of each die. My Monte Carlo Simulation Excel file automatically outputs a histogram of your X1 variable if you have excel 2016 or higher. However, for discrete outcomes, I like making my own using the Excel Function COUNTIF. Doing this I obtained very similar outputs for both X1 and X2. Below I show the output for X1. Note that the number of occurrences for each number is about 1/6 of the total as expected.
In the same way, we can plot the histogram of our Y= X1 + X2. Note that in accordance with my previous post, the number 7 occurred approximately 16.6667% of the time. It is pretty interesting how the sum of two variables that have a uniform discrete probability function results in an output variable with a discrete triangular distribution function.
Now that we have looked into the sum of 2 dice, how about the sum of 3 dice, or 7 dice? Can you use my Monte Carlo Simulation Excel file to do this with more than 2 dice?