Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...


Let's make it stickier:


Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks

Compound annual growth rate (CAGR) = [(Ending value / beginning value)
^ (1 / # of years) ] - 1

Assuming your data are in cells A1:A4, then, =(A4/A1)^(1/4) - 1 is
your CAGR.

I get a CAGR of around 4% with your numbers.

Dave


On Dec 17, 12:23 pm, RJB wrote:
Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...

Let's make it stickier:

Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks

Dave, why would you not use '3' for the number of years instead of '4'. As in
the example Year 1 is the starting date and should be named Year 0. For the
second example from 1000 to 1080 I get a CAGR of 2,6%. And if you double
check by adding 2.6% each year you end up with 1080.
Year 1 1000
Year 2 1026
Year 3 1053
Year 4 1080

Year Income
================
1 1000
2 1040
3 1020
4 1080

TK

"Dave F" wrote:

Compound annual growth rate (CAGR) = [(Ending value / beginning value)
^ (1 / # of years) ] - 1

Assuming your data are in cells A1:A4, then, =(A4/A1)^(1/4) - 1 is
your CAGR.

I get a CAGR of around 4% with your numbers.

Dave


On Dec 17, 12:23 pm, RJB wrote:
Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...

Let's make it stickier:

Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks

On Jan 24, 4:15*pm, TK wrote:
Dave, why would you not use '3' for the number of years instead of '4'.

If you are going to respond to a month-old thread (or older), the
least you could do is read the entire thread. I already noted the off-
by-error in my last posting in this thread, posting Dec 19 ("PS....").

On Jan 24, 7:22*pm, joeu2004 wrote:
On Jan 24, 4:15*pm, TK wrote:

Dave, why would you not use '3' for the number of years instead of '4'.

If you are going to respond to a month-old thread (or older), the
least you could do is read the entire thread. *I already noted the off-
by-error in my last posting in this thread, posting Dec 19 ("PS....").

Yes, that's correct, you use 3 not 4.

Note you can also use the RATE function; i.e., =RATE(B15,,-C12,C15),
where B15 is the number of years, -C12 is the present value negated
(i.e., the first year's income) and C15 is the future value (the
ending value).

You can check to see that this is correct by using the algebra I gave
you in my first response.

Dave

On Dec 17, 12:23 pm, RJB wrote:
Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...

Let's make it stickier:

Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks

You can also use XIRR if you want. In your example, you put in $1000, and got
out $1157.63. So just enter -1157.63 and it will calculate for you.

--
Regards,
Fred


"RJB" wrote in message
...
Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...


Let's make it stickier:


Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks

No, that's not right. The numbers are all income from an ongoing enterprise.

"Fred Smith" wrote:

You can also use XIRR if you want. In your example, you put in $1000, and got
out $1157.63. So just enter -1157.63 and it will calculate for you.

--
Regards,
Fred


"RJB" wrote in message
...
Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...


Let's make it stickier:


Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks

What are you looking to calculate? If it is CAGR, then you have three
methods to calculate it: use the RATE function, use the XIRR function
or else use the algebraic expression [(Ending value / beginning
value)
^ (1 / # of years) ] - 1

Else, tell us what you want to calculate.

Dave

On Dec 18, 12:05 pm, RJB wrote:
No, that's not right. The numbers are all income from an ongoing enterprise.



"Fred Smith" wrote:
You can also use XIRR if you want. In your example, you put in $1000, and got
out $1157.63. So just enter -1157.63 and it will calculate for you.

--
Regards,
Fred

"RJB" wrote in message
...
Excel's formulas don't work for this, as there's no negative outflow at any
point, and all of my college textbooks are in the basement.

First iteration of my question:

Year Income
================
1 1000
2 1050
3 1102.50
4 1157.63

Now, we can see that income grew by five percent each year. We can see that
income is nearly 16% higher in year 4 than year 1. But how do I calculate the
compounded growth over four years? XIRR requires a negative to work...

Let's make it stickier:

Year Income
================
1 1000
2 1040
3 1020
4 1080

Now, income grew by eight percent over four years. Year-on-year was 4%,
-1.9%, and 5.9%, respectively. What was the OVERALL compounded growth? How do
I build a formula for THAT?

Thanks- Hide quoted text -

- Show quoted text -

I'm trying to calculate the compounded rate of growth. All functions require
an outlay of some kind; there is no outlay. Just continued growth. So it's
not a textbook CAGR, which is why I didn't ask for that.

It is textbook CAGR, you just don't want to believe so.

To calculate a growth rate, you need to know how much you put in, and how much
you received back. This is true for any financial calculation.

In order to calculate this growth rate, the assumption is that you cash in on
the last day of the investment. This is true of virtually all XIRR calculations.
You have to, figuratively, take the money back at the end of the period. This is
why it's called a 'return'.

Others are right that you can calculate this CAGR with an exponential formula,
but you will find it much easier to deal with financial functions if you think
in terms of "I put in $1000, got $1157.63 back, what's my return?"

--
Regards,
Fred


"RJB" wrote in message
...
I'm trying to calculate the compounded rate of growth. All functions require
an outlay of some kind; there is no outlay. Just continued growth. So it's
not a textbook CAGR, which is why I didn't ask for that.