How to calculate monthly stock return in Excel

FinanceInvestingInvesting for BeginnersHow to Find a Stock Return Using the Adjusted Closing PriceHow to Find a Stock Return Using the Adjusted Closing PriceBy: Madison Garcia | Re

1. Finance
2. Investing
3. Investing for Beginners
4. How to Find a Stock Return Using the Adjusted Closing Price

How to Find a Stock Return Using the Adjusted Closing PriceBy: Madison Garcia | Reviewed by: Ryan Cockerham, CISI Capital Markets and Corporate Finance | Updated January 28, 2019

Putting stock returns in a percentage format allow you to easily compare performance between stocks.

Duncan Smith/Photodisc/Getty Images

• 1. Finding the Price of a Stock from a Previous Year
• 2. How to Compute the 52-Week Average Selling Price of a Stock
• 3. How to Track Old Stock Prices

Tip

The adjusted closing price of a stock takes into account dividend payments, splits and other factor which directly influence overall return. Comparing the adjusted closing prices for a single stock over a specific duration of time will allow you to identify its return.

Obtain Important Information

Find an online or print resource that offers historical price tables for your stock. Many companies offer historical price data in the investor relations portion of their website, and finance websites also make data available to the public. Download the data for the period of time you're interested in, or enter it manually into a spreadsheet program. You can record close dates at daily, weekly or monthly intervals  whatever works best for your purposes.

Set Up the Data

Most sources will give you a variety of data regarding the stock for each closing date. The only data you really need is the column of dates and a corresponding column for adjusted closing prices. Set up the spreadsheet so that the date and corresponding price are in descending order. For instance, if you're trying to find the monthly stock return from January 2018 to September 2018, list February data below January data.

Find the Return

To calculate a monthly stock return, you'll need to compare the closing price to the month in question to the closing price from the previous month. The formula for percentage return begins by dividing the current month's price by the prior month's price. The number 1 is then subtracted from this result before multiplying the resulting figure by 100 to convert it from decimal to percentage format.

For example, if the January 2018 stock price was \$60 and the February price was \$67, the return is 11.67 percent [(67/60)-1] * 100. Create a new column labeled "stock return" and perform the calculation for each month.

Perform the Necessary Analysis

Once you've calculated monthly returns, you can continue to analyze and play around with the stock return data. You can find the average return over the time period by summing each stock return and dividing it by the number of months in the time period. You can also find the standard deviation of the monthly returns to see how erratically the stock increases in value. If you own stock in multiple companies, you can use correlation functions and equations to discover how the stocks in your portfolio are correlated.

References

• The Nest: How to Find a Stock Return Using the Adjusted Closing Price
• ADJUST | definition in the Cambridge English Dictionary

Resources

• Yahoo! Finance

Writer Bio

Based in San Diego, Calif., Madison Garcia is a writer specializing in business topics. Garcia received her Master of Science in accountancy from San Diego State University.