# MS Excel Investing Formula for calculating stock return HELP pls

#### doug6388

##### New Member
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

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

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.

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

Replies
2
Views
893
Replies
4
Views
471
Replies
0
Views
379
Replies
1
Views
753
Replies
1
Views
416

1,203,455
Messages
6,055,541
Members
444,794
Latest member
HSAL

### 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.

### Which adblocker are you using?

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

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