How do I use Excel to find an average return?

Question

I have the closing price of a stock for 60 months. How do I use Excel to find the average month-to-month return? For example, if I had the following three month closing prices:

Month 1: $10.00
Month 2: $9.00
Month 3: $11.00

The monthly returns would be:

Month 1: NA
Month 2: -10%
Month 3: +22.2%

The average monthly return would be:

(-10+22.2)/2 = 6.1%

Annualized, it would be:

6.1% X 12 months = 73.2%

How do I do this quickly in Excel with many stocks and 60 months worth of closing price data?

Answer 1

1. date
2. closing price
3. HPR
formula: =(ending price - beginning price)/ beginning price
4. Average HPR
formula: =average(C1:C77) <=put the range of cells from the column in which you calculated the HPRs.

Answer 2

You can do this without creating a new column using an array function.
If column C has your monthly closing prices (for stocks/funds make sure this is the adjusted closing price), let's say from rows 2 to 61, then you can get the average monthly return like so:

1. Enter this as the cell formula: "=AVERAGE(C3:C61/C2:C60-1)"
2. Instead of hitting Enter/Return, hit Shift+Ctrl+Enter.

Note that the ranges given are intentional, the numerator should start at the 2nd month of data you have) and end at the last month, the denominator should start at the 1st month and end at the second-to-last month.

Step 2 makes it an array formula. If you don't use Shift+Ctrl+Enter correctly (say you just hit Enter directly), you'll get an error in that cell. You should be able to re-focus it and enter Shift+Ctrl+Enter and get it interpreted correctly.

Answer 3

Hpr Formula

Answer 4

i'm Laurie and that i've got lost 60 kilos. I went from a length 14 to a length 6.for the reason that i've got lost the load i've got faith great. I also have a number of of power. i'm keen to do distinctive issues. I purely love going out, and fiddling with the youngsters, and having relaxing. this is replaced each and every thing. My complete life has replaced.

Answer 5

Use Yahoo Finance-It has a built in program.

Answer 6

you type in
=average(highlight all the numbers needed) and press enter
Well that is how i do it

Answer 7

try www.stock-exc.com