software

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.

Tract200020012002200320042005
101120,000110,000130,000180,000210,000215,000
102110,000150,000145,000180,000190,000190,000
103210,000250,000240,000280,000340,000360,000
104130,000160,000190,000210,000230,000240,000
105350,000400,000500,000575,000600,000650,000

What formula would provide me with this answer in Excel? Although I
described this as quot;average annualquot; 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: www.excelforum.com/member.php...oamp;userid=17540
View this thread: www.excelforum.com/showthread...hreadid=526223When the media report quot;house prices have gone up 8% per year over the past five
yearsquot;, 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,
Fredquot;James Hobartquot; gt; wrote
in message news:James.Hobart.257031_1143234913.7228@excelforu m-nospam.com...
gt;
gt; Hello Excel Tippers,
gt;
gt; I have median sales prices for the years 2000-2005 for each census
gt; tract in my City. The median is derived from any number of sales that
gt; have occurred at any time during that particular year in that
gt; particular census tract.
gt;
gt; From one year to the next, some tract sale values have gone up. Some
gt; years, some sales values have gone down. Some tracts have never gone
gt; down in consecutive years.
gt;
gt; Tract 2000 2001 2002 2003 2004 2005
gt; 101 120,000 110,000 130,000 180,000 210,000 215,000
gt; 102 110,000 150,000 145,000 180,000 190,000 190,000
gt; 103 210,000 250,000 240,000 280,000 340,000 360,000
gt; 104 130,000 160,000 190,000 210,000 230,000 240,000
gt; 105 350,000 400,000 500,000 575,000 600,000 650,000
gt;
gt; What formula would provide me with this answer in Excel? Although I
gt; described this as quot;average annualquot; in the title to this thread I'm not
gt; sure that really describes what I'm after. I don't think Compound
gt; Annual Growth Rate (am I wrong?) is the correct measure.
gt;
gt;
gt; --
gt; James Hobart
gt; ------------------------------------------------------------------------
gt; James Hobart's Profile:
gt; www.excelforum.com/member.php...oamp;userid=17540
gt; View this thread: www.excelforum.com/showthread...hreadid=526223
gt;
Ignore the intermediate data points. For each tract, subtract the values in
2000 from the values in 2005 and divide by 5.
--
Gary's Studentquot;James Hobartquot; wrote:

gt;
gt; Hello Excel Tippers,
gt;
gt; I have median sales prices for the years 2000-2005 for each census
gt; tract in my City. The median is derived from any number of sales that
gt; have occurred at any time during that particular year in that
gt; particular census tract.
gt;
gt; From one year to the next, some tract sale values have gone up. Some
gt; years, some sales values have gone down. Some tracts have never gone
gt; down in consecutive years.
gt;
gt; Tract200020012002200320042005
gt; 101120,000110,000130,000180,000210,000215,000
gt; 102110,000150,000145,000180,000190,000190,000
gt; 103210,000250,000240,000280,000340,000360,000
gt; 104130,000160,000190,000210,000230,000240,000
gt; 105350,000400,000500,000575,000600,000650,000
gt;
gt; What formula would provide me with this answer in Excel? Although I
gt; described this as quot;average annualquot; in the title to this thread I'm not
gt; sure that really describes what I'm after. I don't think Compound
gt; Annual Growth Rate (am I wrong?) is the correct measure.
gt;
gt;
gt; --
gt; James Hobart
gt; ------------------------------------------------------------------------
gt; James Hobart's Profile: www.excelforum.com/member.php...oamp;userid=17540
gt; View this thread: www.excelforum.com/showthread...hreadid=526223
gt;
gt;

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

200020012002200320042005
120,000139,000158,000177,000196,000215,000

and for 12.37% increase per year:

200020012002200320042005
120,000134,844151,525170,269191,332215,000

--
Gary's Studentquot;Fred Smithquot; wrote:

gt; When the media report quot;house prices have gone up 8% per year over the past five
gt; yearsquot;, they are using compound annual growth rate.
gt;
gt; As far as I can tell, this is what you want. If you want the five-year average,
gt; it's immaterial what the intervening values are. If five years ago, the price
gt; was 210,000 and today it's 360,000, it doesn't matter what happened in between.
gt;
gt; The Rate function will provide your answer.
gt;
gt; --
gt; Regards,
gt; Fred
gt;
gt;
gt; quot;James Hobartquot; gt; wrote
gt; in message news:James.Hobart.257031_1143234913.7228@excelforu m-nospam.com...
gt; gt;
gt; gt; Hello Excel Tippers,
gt; gt;
gt; gt; I have median sales prices for the years 2000-2005 for each census
gt; gt; tract in my City. The median is derived from any number of sales that
gt; gt; have occurred at any time during that particular year in that
gt; gt; particular census tract.
gt; gt;
gt; gt; From one year to the next, some tract sale values have gone up. Some
gt; gt; years, some sales values have gone down. Some tracts have never gone
gt; gt; down in consecutive years.
gt; gt;
gt; gt; Tract 2000 2001 2002 2003 2004 2005
gt; gt; 101 120,000 110,000 130,000 180,000 210,000 215,000
gt; gt; 102 110,000 150,000 145,000 180,000 190,000 190,000
gt; gt; 103 210,000 250,000 240,000 280,000 340,000 360,000
gt; gt; 104 130,000 160,000 190,000 210,000 230,000 240,000
gt; gt; 105 350,000 400,000 500,000 575,000 600,000 650,000
gt; gt;
gt; gt; What formula would provide me with this answer in Excel? Although I
gt; gt; described this as quot;average annualquot; in the title to this thread I'm not
gt; gt; sure that really describes what I'm after. I don't think Compound
gt; gt; Annual Growth Rate (am I wrong?) is the correct measure.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; James Hobart
gt; gt; ------------------------------------------------------------------------
gt; gt; James Hobart's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=17540
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=526223
gt; gt;
gt;
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=17540
View this thread: www.excelforum.com/showthread...hreadid=526223You 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,
Fredquot;James Hobartquot; gt; wrote
in message news:James.Hobart.25bzpm_1143467700.7056@excelforu m-nospam.com...
gt;
gt; OK. Need additional clarity.
gt;
gt; It seems what I am looking for is the compound annual growth rate but
gt; it doesn't seem to work with my example. I don't think I want the
gt; 5-year average.
gt;
gt; First - these are median averages for an entire year. What would be
gt; best for using the XIRR formula? July 1, 200x?
gt;
gt; Second - the XIRR expects at least one positive cash flow and one
gt; negative (according to the MS help). I certainly have tracts that have
gt; gained in every year and I get an error message for those.
gt;
gt;
gt; --
gt; James Hobart
gt; ------------------------------------------------------------------------
gt; James Hobart's Profile:
gt; www.excelforum.com/member.php...oamp;userid=17540
gt; View this thread: www.excelforum.com/showthread...hreadid=526223
gt;