Calculating average annual change in real estate value
 

Hello Excel Tippers,

I have median sales prices for the years 2000-2005 for each census
tract in my City. The median is derived from any number of sales that
have occurred at any time during that particular year in that
particular census tract.

From one year to the next, some tract sale values have gone up. Some
years, some sales values have gone down. Some tracts have never gone
down in consecutive years.

Tract 2000 2001 2002 2003 2004 2005
101 120,000 110,000 130,000 180,000 210,000 215,000
102 110,000 150,000 145,000 180,000 190,000 190,000
103 210,000 250,000 240,000 280,000 340,000 360,000
104 130,000 160,000 190,000 210,000 230,000 240,000
105 350,000 400,000 500,000 575,000 600,000 650,000

What formula would provide me with this answer in Excel? Although I
described this as "average annual" in the title to this thread I'm not
sure that really describes what I'm after. I don't think Compound
Annual Growth Rate (am I wrong?) is the correct measure.


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile: http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=526223

Calculating average annual change in real estate value
 

When the media report "house prices have gone up 8% per year over the past five
years", they are using compound annual growth rate.

As far as I can tell, this is what you want. If you want the five-year average,
it's immaterial what the intervening values are. If five years ago, the price
was 210,000 and today it's 360,000, it doesn't matter what happened in between.

The Rate function will provide your answer.

--
Regards,
Fred


"James Hobart" wrote
in message news:James.Hobart.257031_1143234913.7228@excelforu m-nospam.com...

Hello Excel Tippers,

I have median sales prices for the years 2000-2005 for each census
tract in my City. The median is derived from any number of sales that
have occurred at any time during that particular year in that
particular census tract.

From one year to the next, some tract sale values have gone up. Some
years, some sales values have gone down. Some tracts have never gone
down in consecutive years.

Tract 2000 2001 2002 2003 2004 2005
101 120,000 110,000 130,000 180,000 210,000 215,000
102 110,000 150,000 145,000 180,000 190,000 190,000
103 210,000 250,000 240,000 280,000 340,000 360,000
104 130,000 160,000 190,000 210,000 230,000 240,000
105 350,000 400,000 500,000 575,000 600,000 650,000

What formula would provide me with this answer in Excel? Although I
described this as "average annual" in the title to this thread I'm not
sure that really describes what I'm after. I don't think Compound
Annual Growth Rate (am I wrong?) is the correct measure.


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile:
http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=526223

Calculating average annual change in real estate value
 

Ignore the intermediate data points. For each tract, subtract the values in
2000 from the values in 2005 and divide by 5.
--
Gary's Student


"James Hobart" wrote:


Hello Excel Tippers,

I have median sales prices for the years 2000-2005 for each census
tract in my City. The median is derived from any number of sales that
have occurred at any time during that particular year in that
particular census tract.

From one year to the next, some tract sale values have gone up. Some
years, some sales values have gone down. Some tracts have never gone
down in consecutive years.

Tract 2000 2001 2002 2003 2004 2005
101 120,000 110,000 130,000 180,000 210,000 215,000
102 110,000 150,000 145,000 180,000 190,000 190,000
103 210,000 250,000 240,000 280,000 340,000 360,000
104 130,000 160,000 190,000 210,000 230,000 240,000
105 350,000 400,000 500,000 575,000 600,000 650,000

What formula would provide me with this answer in Excel? Although I
described this as "average annual" in the title to this thread I'm not
sure that really describes what I'm after. I don't think Compound
Annual Growth Rate (am I wrong?) is the correct measure.


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile: http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=526223

Calculating average annual change in real estate value
 

Either approach is valid and yield the same ending value.
For tract 1 at an annual increase of about 19,000 per year:

2000 2001 2002 2003 2004 2005
120,000 139,000 158,000 177,000 196,000 215,000

and for 12.37% increase per year:

2000 2001 2002 2003 2004 2005
120,000 134,844 151,525 170,269 191,332 215,000

--
Gary's Student


"Fred Smith" wrote:

When the media report "house prices have gone up 8% per year over the past five
years", they are using compound annual growth rate.

As far as I can tell, this is what you want. If you want the five-year average,
it's immaterial what the intervening values are. If five years ago, the price
was 210,000 and today it's 360,000, it doesn't matter what happened in between.

The Rate function will provide your answer.

--
Regards,
Fred


"James Hobart" wrote
in message news:James.Hobart.257031_1143234913.7228@excelforu m-nospam.com...

Hello Excel Tippers,

I have median sales prices for the years 2000-2005 for each census
tract in my City. The median is derived from any number of sales that
have occurred at any time during that particular year in that
particular census tract.

From one year to the next, some tract sale values have gone up. Some
years, some sales values have gone down. Some tracts have never gone
down in consecutive years.

Tract 2000 2001 2002 2003 2004 2005
101 120,000 110,000 130,000 180,000 210,000 215,000
102 110,000 150,000 145,000 180,000 190,000 190,000
103 210,000 250,000 240,000 280,000 340,000 360,000
104 130,000 160,000 190,000 210,000 230,000 240,000
105 350,000 400,000 500,000 575,000 600,000 650,000

What formula would provide me with this answer in Excel? Although I
described this as "average annual" in the title to this thread I'm not
sure that really describes what I'm after. I don't think Compound
Annual Growth Rate (am I wrong?) is the correct measure.


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile:
http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=526223

Calculating average annual change in real estate value
 

OK. Need additional clarity.

It seems what I am looking for is the compound annual growth rate but
it doesn't seem to work with my example. I don't think I want the
5-year average.

First - these are median averages for an entire year. What would be
best for using the XIRR formula? July 1, 200x?

Second - the XIRR expects at least one positive cash flow and one
negative (according to the MS help). I certainly have tracts that have
gained in every year and I get an error message for those.


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile: http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=526223

Calculating average annual change in real estate value
 

You are making this far more complicated that it is. A five-year compounded
growth rate simply needs an opening and a closing value. Intermediate values are
of no consequence.

To calculate the growth rate for tract 101, the formula is:

=rate(5,0,-120000,215000)

You can use XIRR, but it's overkill. Again all you need is the starting and
ending value. Make one value negative, and the other positive, and use any dates
you want that are exactly a year apart. The intermediate values are immaterial
because they are not cash flow.

Think of it this way. If you invested $1000 five years ago, and it was worth
$1500 today, what was your average rate of return? You calculate this with the
Rate function as above. The fact that is was worth $1200 after year one, $1100
after year two, $1600 after year three, etc. is immaterial, and is only
confusing the issue for you.

--
Regards,
Fred


"James Hobart" wrote
in message news:James.Hobart.25bzpm_1143467700.7056@excelforu m-nospam.com...

OK. Need additional clarity.

It seems what I am looking for is the compound annual growth rate but
it doesn't seem to work with my example. I don't think I want the
5-year average.

First - these are median averages for an entire year. What would be
best for using the XIRR formula? July 1, 200x?

Second - the XIRR expects at least one positive cash flow and one
negative (according to the MS help). I certainly have tracts that have
gained in every year and I get an error message for those.


--
James Hobart
------------------------------------------------------------------------
James Hobart's Profile:
http://www.excelforum.com/member.php...o&userid=17540
View this thread: http://www.excelforum.com/showthread...hreadid=526223