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

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.

"Fred Smith" wrote:

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

The most pressing reason I don't believe so is:




To calculate a growth rate, you need to know how much you put in, and how much
you received back.

No. I just want to know year-on-year growth, regardless of how much was put
in.

I don't care about the $50 some scrappy linen salesman put into the business
on a cold Tuesday afternoon in 1931... I care that from 200x to 200y, net
income growth was A percent; from 200y to 200z net income growth was B
percent; from 200z to 200n net income growth was N percent... So, the
compounded rate of income growth between 200x and 200n was N1 percent.

I'm not calculating an investment return - I'm calculating growth year to
year of an ongoing business concern.

It's not an investment.

There's no money "going in".

There's only income.

And it changes from year to year.

===

(I appreciate your help - I turn to these boards a lot... I'm just trying to
be absolutely clear about what I'm trying to figure out. I did it on paper
and a calculator in less time than it took me to post; I was simply hoping
there was a neat function in Excel that did it for me.)

On Dec 19, 4:42 am, RJB wrote:
"Fred Smith" wrote:
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.

No. I just want to know year-on-year growth, regardless of how much
was put in.

You are absolutely right.


I'm not calculating an investment return - I'm calculating growth year to
year of an ongoing business concern.
[....]
I was simply hoping there was a neat function in Excel that did it for me.

And Dave already provided the best answer for you: RATE(). Why are
you ignoring that answer?

PS....

On Dec 19, 9:17 am, I wrote:
On Dec 19, 4:42 am, RJB wrote:
And Dave already provided the best answer for you: RATE(). Why are
you ignoring that answer?

Well, perhaps because Dave had an off-by-error that shook your
confidence in his response.

And perhaps because you think that RATE() is as much a contortion of
the problem as XIRR() is. I don't. I think of it this way. Consider
your second example. I think you want to know the average rate, r%,
such that when you multiply 1000 by 1+r% for each year (3), you get
1080. That's the same as putting 1000 into a bank that pays r%
compounded annually such that you have 1080 after 3 years. You want
to know what r% is.

That is what RATE() computes. RATE(3,0,-1000,1080) is about 2.60%.
As a check: 1000*(1+2.60%)*(1+2.60%)*(1.2.60%) is about 1080.

But perhaps you prefer to look at the problem differently. You said
you computed this on paper with a calculator. How did you formulate
the problem?

I suspect you might have done the following. Income grew by 1040/1000
- 1 (4.00%) the 1st year, 1020/1040 - 1 (-1.92%) the 2nd year, and
1080/1020 - 1 (5.88%) the 3rd year. The average compounded rate is
((1+4.00%)*(1-1.92%)*(1+5.88%))^(1/3) - 1. That is about 2.60%.

In Excel, you could compute that in the same way with the following
array formula (commit with ctlr+shift+Enter and format as Percentage):

=product(A2:A4/A1:A3)^(1/3) - 1

assuming that the annual incomes are in A1:A4 (A1 = 1st year).

But note that mathematically, the product formula reduces to the
simple exponential formula that Dave provided (correction: the
exponent is 1/(#years - 1)); and that is the formula that RATE()
should use when the "pmt" argument is zero [1].

Moreover, there are (extreme) situations when the product formula
might fail due to limits of binary computer arithmetic, whereas the
exponential formulation (and presumably RATE) will work. I doubt that
you would hit those extremes using realistic business incomes over a
realistic number of years. But why not use the simpler formulation?

HTH.


-----
Endnotes:

[1] There are also situations where RATE() might fail, whereas an
exponential formulation would work. I hope that is only when the
"pmt" argument is non-zero. But I don' t know if RATE is optimized
for the case where the "pmt" argument is zero.

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.