MS Excel Investing Formula for calculating stock return HELP pls

[doug6388]

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?


Calculating Stock Prices


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

 

[JoeMo]

The XIRR function can be used to calculate returns. Your return for the example you cited will depend on whether or not you reinvest the dividends to purchase more shares. XIRR returns an annualized value. Here's a link that provides more information on XIRR: XIRR

 

[doug6388]

The XIRR function can be used to calculate returns. Your return for the example you cited will depend on whether or not you reinvest the dividends to purchase more shares. XIRR returns an annualized value. Here's a link that provides more information on XIRR: XIRR

Many thanks, Joe, but could you translate that into a multi-cell excel spreadsheet that I could copy and paste and plug in and use, as I have no knowledge of the XIRR function. Regards, Doug

 

[JoeMo]

Many thanks, Joe, but could you translate that into a multi-cell excel spreadsheet that I could copy and paste and plug in and use, as I have no knowledge of the XIRR function. Regards, Doug

Basically, you need to put the dates of all cash flows, such as investments (buys), non-reinvested dividends and redemptions (sales) into one column and the amount of each cash flow in a companion column. See Excel Help and the link I provided for how to use XIRR with that information.

 

[doug6388]

I posted the same question to MS office forum and was able to upload my Excel spreadsheet for any to review
Microsoft Office Forums