Financial analysts often use the following model to characterize changes in stock prices:
Pt = Po*e^(u-0.5s^2)t+s*Z*t^0.5
where Po = current stock price
Pt - price at time t
u - mean (logarithmic) change of the stock price per unit time
s = (logarithmic) standard deviation of price change
Z = standard normal random variable
This model assumes that the logarithm of a stock's price is a normally distributed random variable. Using historical data, one can estimate the values for u and s. Suppose that the average daily change for a stock is $0.003227, and the standard deviation is 0.026154. Develop a spreadsheet to stimulate the price of the stock over the next 30 days, if the current price is $53. Use the Excel function NORMSINV(RAND()) to generate values for Z. Construct a chart showing the movement in the stock price.
See attached file.
The solution provides detailed explanation how to Use NORMSINV(RAND()) to generate spreadsheet