I am trying to create an Excel formula to calculate the value of a stock on the stock market, from the 52 weeks lo to the present selling price. From the lo to now + add the dividend that has been paid todate and calculate the time held to come up with a cost of holding the stock and its hopeful increase. Any help gratefully appreciated.

What is the excel formula that calculates this?

http://goo.gl/ey7G5B

Astock = $100 on Jan 1


Astock = $120 on Sept 1
Astock pays a monthly dividend of $3/mo.
Jan 1 - Sept 1 = 8 mo x $3 = .$24 Dividend

Expected Return = (Dividends Paid + Capital Gain) / Price of Stock

Price of Stock A is currently $100.00 per share or (P0).
Dividends are expected to be $3.00 per share (Div).
The price of Stock A is expected to be $125.00 per share in one year's time (P1).
Therefore our capital gain is expected to be $125.00 - $100.00 or $25.00 per share.

Expected Return, or R = ($3.00 + $25.00) / $100.00 = 8.0%???
We can now use this expected return to calculate the price of a stock in the same risk class as Stock A using the following formula:
Stock Price = (Dividends Paid (Div) + Expected Price (P1)) / (1 + Expected Return (R))

Proving this calculation with our example information above, we have:
Stock Price = ($3.00 + $105) / (1 + 0.08) = $108.00 / 1.08 = $100

Attached Files

ROI.xls (13.5 KB, 11 views)