compound annual growth rate

P

Paul B

Well-known Member

Joined

Feb 15, 2002

Messages

577

• Aug 18, 2002

• #2

This will give you Compound Interest, is that what you need?
= Principle*(1 + Interest Rate/12)^(number months)

If you put your starting amount in B2, your Interest rate in C2, and the months in D2.you could put this in E2
=B2*(1+(C2/12))^D2
This message was edited by Paul B on 2002-08-18 10:53

 

Upvote 0

R

rjaa

New Member

Joined

Aug 17, 2002

Messages

2

• Aug 18, 2002

• #3

Paul

Thanks, but interest rate is the unknown.

I need to calculate the rate that yields a final value of Vn after n periods, given an initial value of Vo

 

Upvote 0

R

Ricardo

Board Regular

Joined

Jun 26, 2002

Messages

149

• Aug 18, 2002

• #4

Try

Power(Vn/Vo;1/n)-1

where n are the number of periods.
You could use as well the ^ but i have problems with that.
tell me if OK
bye

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Aug 18, 2002

• #5

Howdy, sounds like you want to use:

=IRR()

Against your timeline of cashflows. With multiple changes in positive to negative there are the same number of correct mathematical soltuions, as discussed:

http://www.mrexcel.com/board/viewtopic.php?topic=9367&forum=2

Edit: Are you plugging rates or cfs?
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-08-18 11:50

 

Upvote 0

R

Ricardo

Board Regular

Joined

Jun 26, 2002

Messages

149

• Aug 18, 2002

• #6

Nate,
I dont think IRR works when you have only positive numbers.

bye

 

Upvote 0

P

P. Dante

New Member

Joined

Mar 14, 2002

Messages

7

• Aug 18, 2002

• #7

On 2002-08-18 12:31, Ricardo wrote:
Nate,
I dont think IRR works when you have only positive numbers.

bye

Click to expand...

The negative number is the original principal.

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Aug 18, 2002

• #8

I dont think IRR works when you have only positive numbers.

bye

Click to expand...

Yeah this is right, I had a little trouble determining what we were attempting to accomplish here. The IRR should return your annual growth rate on dollars invested in a project. If you just want to look a stream of numbers and determine the compounding growth, perhaps the following example will help. If you click on cell g17, it'll show the formula that I'm familiar with:

Book2
	A	B	C	D	E	F	G	H
3	Example
4
5	Year	Revenues				NetRevenue	AnnualGrowth
6	0	$200			1990	$200
7	1	$220			1991	$220	10.0%
8	2	$242			1992	$242	10.0%
9	3	$266			1993	$266	10.0%
10	4	$293			1994	$293	10.0%
11	5	$322			1995	$322	10.0%
12	6	$354			1996	$354	10.0%
13	7	$390			1997	$390	10.0%
14	8	$429			1998	$429	10.0%
15	9	$472			1999	$472	10.0%
16
17	Growth	10.0%			No.ofperiods:	9	10.0%	CAGRcalculation
18
MrExcel Example

 

Upvote 0

S

shergenr

New Member

Joined

Aug 15, 2002

Messages

6

• Aug 19, 2002

• #9

CAGR and IRR are not the same. You can do a search on the internet and get an explanation of CAGR. The basic formula is as follows:

Say C1 is total sales for period 1, E1 is total sales for period 3. To find Cagr for 2 year period see below:

(E1/C1)^(1/2)-1=CAGR

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Aug 19, 2002

• #10

On 2002-08-19 08:22, shergenr wrote:
CAGR and IRR are not the same. You can do a search on the internet and get an explanation of CAGR. The basic formula is as follows:

Say C1 is total sales for period 1, E1 is total sales for period 3. To find Cagr for 2 year period see below:

(E1/C1)^(1/2)-1=CAGR

Click to expand...

This is correct, the formula you've used is very similar to the one in g17 above.

Here's a udf that may make this a little easier:<pre>
Public Function CAGR(ByVal myRng As Range) As Double
Dim Fst As String, Lst As String, pos As Single, TmpVal As Double
Application.Volatile
pos = InStr(myRng.Address(False, False), ":")
Lst = Mid(myRng.Address(False, False), pos + 1, _
Len(myRng.Address(False, False)))
Fst = Left(myRng.Address(False, False), pos - 1)
CAGR = ((Range(Lst) / Range(Fst)) ^ (1 / (myRng.Count - 1))) - 1
End Function</pre>

Now just use the following

=cagr(h6:h15)

In the example I provided. Format the cell appropriately.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-08-19 08:39

 

Upvote 0