Econofísica - 7 Modelando no Excel

Summary

In this video, the speaker explains how to simulate a stock's return model using Excel, focusing on a discrete model of stock returns. The process involves defining three parameters: mean, volatility (0.7), and step size (1). The speaker demonstrates how to use Excel functions like the inverse normal function and random values to simulate stock returns, ensuring the distribution has a mean of zero and a standard deviation of one. By applying this approach, users can visualize the simulation of returns, and the speaker encourages feedback for further Excel tutorials and simulations.

Takeaways

• 😀 The script focuses on simulating stock returns using Excel.
• 😀 Three key parameters are defined: mean, volatility, and step size.
• 😀 The mean is referred to as 'meio' in the script.
• 😀 Volatility is set at 0.7 in the example for the simulation.
• 😀 Step size is set at 1 to simplify the simulation process.
• 😀 Excel functions are used to apply these parameters for simulating stock returns.
• 😀 The NORMINV function is used to scale a random value to match the volatility.
• 😀 The RAND function generates random values that represent probabilities for stock returns.
• 😀 A random value between 0 and 1 is mapped onto a normal distribution curve for simulation.
• 😀 When the formula is dragged down in Excel, different stock return values are generated each time.
• 😀 The simulation can be visualized by plotting the results, showing stock return distributions based on the defined parameters.

Q & A

What is the main goal of the Excel simulation discussed in the script?

The main goal is to simulate the discrete model of stock returns using Excel, incorporating parameters like mean, volatility, and step size.

What are the three key parameters required for the stock return simulation?

The three key parameters for the simulation are the mean (referred to as 'meio'), volatility (set to 0.7), and the step size (denoted as '1').

How does the script explain the process of applying volatility in the Excel function?

Volatility is applied by multiplying the volatility value (0.7) with a random component, using the normal distribution function to model the randomness in the stock return.

What does the 'normal inverse' function in Excel do in this simulation?

The 'normal inverse' function in Excel generates a random value based on a standard normal distribution, which has a mean of 0 and a standard deviation of 1, and is scaled according to the specified volatility.

Why is the normal distribution function important in this simulation?

The normal distribution function is essential because it generates random values based on probability, which allows the simulation to model potential stock returns with a specific mean and volatility.

How does the Excel function simulate random returns?

By using the inverse normal function, Excel generates random values from a probability curve, and these values represent simulated stock returns that are consistent with the specified mean and volatility.

What happens when the function is dragged down across multiple cells in Excel?

When the function is dragged down, Excel recalculates the values for each cell, producing different random returns each time, reflecting the stochastic nature of the simulation.

What does the script mean by the value 'a' in the simulation?

The value 'a' is a result returned by the inverse normal function, representing a simulated return that can range from negative to positive infinity, but is normalized to have a mean of 0 and a standard deviation of 1.

How does the script ensure that the generated returns have the desired mean and volatility?

The script ensures the desired mean and volatility by using the normal distribution function with the specified parameters (mean = 0, volatility = 0.7), and applying them to the random values generated in Excel.

What is the benefit of simulating stock returns multiple times in Excel?

Simulating stock returns multiple times in Excel allows for a broader understanding of potential outcomes by visualizing a range of possible returns in a graph, helping to assess the risk and variability of the stock.