Calculating average annual change in real estate value

[James Hobart]

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.

[Fred Smith]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Gary''s Student]

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

[Gary''s Student]

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" <[email protected]> wrote
> in message news:[email protected]...
> >
> > 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
> >
>
>
>

[James Hobart]

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.

Third - the values I have are toal sales value, not the change from one period to the next. Does XIRR work with total value or the change?

[Fred Smith]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>