MS Excel Investing Formula for calculating stock return HELP pls

doug6388

New Member
Joined
Sep 5, 2014
Messages
3
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,587
Members
452,860
Latest member
jroberts02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top