Financial modelling/monte carlo - predicting share price movement with dividend

[abender777]

I am trying to model a future share price, based on:

- Dividend per year
- yearly volatility (standard deviation)
- price
-exercise price
-risk free rate
-Time period

I currently have the following equation to predict the daily movement of the share:

=J8*(1+NORMINV(RAND(),0,$J$10))

Where J8 = Share Price of last day
J10 = daily volatility

I simply have it 1008 in a row, then am using a data table to get the final price 1000 times (and taking average).

The issue is, I need to incorporate a yearly 5% dividend yield into this equation.

Anyone have any ideas on what is the best way to do this?

This is to model an option price where the option pays 0 if the share does not go up on average 10% per year (including dividends).

 

[abender777]

Anyone have an idea?

 

[charlie727]

I have never seen that equation to estimate the daily movement of share price. If you want to keep your equation, to add 5% dividend yield

Dividend Yield = Annual dividends per share / Price per share

Price per Share = Annual Dividends per share/ Dividend yield - (Gives you an updated Share price incorporating Dividend Yield)
Average out the two share prices and you'll have an updated share price to use in your equation.

However I prefer to use the equation:


(Stock price) x (Annualized Implied Volatility) x (Square Root of [days to expiration / 365]) = 1 standard deviation.
Which will more accurately predict the price movement for a specific time period (Say a month) and will give you the implied price movement

Remember the value means it can go up as well as down