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 <R...@discussions.microsoft.com> 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 <R...@discussions.microsoft.com> 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" <(E-Mail Removed)> wrote in message
news:0C7825C9-B678-482D-99DF-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:0C7825C9-B678-482D-99DF-(E-Mail Removed)...
> > 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 <R...@discussions.microsoft.com> 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" <R...@discussions.microsoft.com> wrote in message
> >news:0C7825C9-B678-482D-99DF-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
news:6812CDE2-B3B8-426D-BB0A-(E-Mail Removed)...
> 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 <R...@discussions.microsoft.com> 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?