Explore BrainMass

# Use NORMSINV(RAND()) to generate spreadsheet

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

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.