Free Microsoft Excel 2013 Quick Reference

Monthly compound growth rate?

Hi,

Can someone give me a formula that calculates the monthly compounding growth rate for a 12 month period where an annual growth rate target is subject to change:

E.G.

If we are targeting annual growth of 100%. Ie $1,000,000 becomes $2,000,000 in a year, the monthly compounding growth rate is 5.94631%.

How do I put this in a formula???????? in case the annual growth rate changes from say 100% to 50%.


A few months ago, I got the help of this forum to obtain the formula for compound growth rate. The formula I was given was something like this:

=sum(present earnings/starting earnings)^(1/#years)-1

That has worked in excel, but now I am attempting to move this formula from excel to a web program and I don't know what ^ does. The help section sez that ^ is *, but that doesn't work. Can anyone help me with ^? In excel the formula of
=sum(1.88/.29)^(1/9)-1 = 23.1 . How would I get the same answer in a regular math formula?

Thanks Rich Hill

Greetings:

Is it possible to calculate a compounded growth rate using a begining value, ending value, and number of intervals?

For example,

2005 Population = 24635
2010 Population = 30159

Inverals = 5

The annual rate (5 intervals) = 4.11%

Is there a function that can calucate the 4.11 % ?

Best regards,
Beau

Is there a function in Excell that can be used to calculate an investment"s
compound growth rate? If so what is it and how do you use it?

For instance if someone invested $10,000 in 1985 and that investment was
worth $18,000 today is there a function in Excell that can be used to
determine that investment's compound growth rate?

Hi all,
I have a sample spreadsheet of exactly what I am facing, but an excerpt.
Column A aims to find the compounded annual return of historical total assets, which is from coumn C onwards.

However, some data goes back to 1991, and some to 1993 and so on.
I'm trying to come up with a command that finds the compounded growth rate and simply drag it down to apply to all cells, taking into account of the different years.

Please advise.

Sincere thanks in advance.

Hi.

I'm thinking theres a simple formula to work out my growth rate (in my case negative growth) in the last 9 months (assumed straight line) so I can forecast for a year. Can anyone help? Example shown below.

Many thanks

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHIJK1Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9 Growth rate2 5,229,605 5,170,498 4,375,780 4,474,410 4,954,709 4,186,153 3,553,213 4,508,134 3,450,296 ?Sheet1
[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I am tring to calculate a city's Compound growth rate, but I keep
getting an error. the city has 233,149 as of 2004, and 242,820 in 2000.
My formula is =(D4/J4)^(1/(2004 -2000))-1
Thanks for any help.

I have a series of all positive numbers for which I would like to calculate
the compounded growth rate per period. However the XIRR function requires at
least one positive and one negative value. Is there an alternative that will
allow me to calculate the compounded growth rate using all positive values?
Thank you in advance for your cooperation.

Hi all,

I've created a spreadsheet to keep track of my modest share holdings and as a record of the past performance of closed out investments.

At present I am able to work out the exact % gain (or loss!) on an investment by taking the monetary change in value net of any fees and dividends, and expressing as a % of purchase value. Its a useful way to help work out where things have gone well, and where they haven't....

However, what I would really like to do is to work out the equivalent annual compound growth rate for any particular share, as this will enable me to make valid comparisons between investments over different time periods. So, for example, lets say I buy a share, hold it for 3 years and sell it for a total gain of 15%. You might suggest just dividing the % figure by the number of years to give an average gain of 5% per year.

However I think a truer reflection of performance is to work out compounded rates. To illustrate this further, lets take a standard interest bearing deposit account. If you had £100 in this account and earned 5% interest each year, you would end up not with £115, but with £115.76 after 3 years. The interest accumulated in all but the final year also earning interest in the subsequent years - the joy of compounding. While its easy to work out how funds accumulate over time with the rate of interest to hand, I don't have a method of performing the reverse calculation - extrapolating the interest rate from an end total and the time.

So for my aforementioned 15% over 3 years example, I can only guess the rate through trial and error, (its something just under 4.8% equivalent annual interest) - and that's just on paper. I wouldn't know where to begin when it comes to writing an excel formula to calculate this. The formula gets more complex still when you factor in time periods that are not whole years.

So, ideally, I would like to have a formula that calculates the time (in days, I guess?) an investment has been held from bought and sold dates (which I write in a format like so: 11-May-2009), and then uses this variable together with the total % gain (or loss) to work out the equivalent % rate of gain per year.

All of which will probably point to the fact that I'd earn more holding the money in a savings deposit account! If anyone can help out with this little headache, I'd most appreciate it. Thanks,

Andrew

Hi: I am trying to find an excel formula to use compounding backwards and find monthly and daily growth rates while having an annual growth rate. Example: Year 1 sales = 100. Year 2 Sales = 200. What is the daily growth rate to get from 100 to 200? From there I can create a table with Day | daily rate | Value at day [x] and adjust each month's sales forecast by number of days in the month.

Thanks in advance

below are the monthly data for Jul - Mar, data & are in real values and are not in percenatge difference. Problem is this i want to figure out the growth rate, either it could be postive growth or negative. i attempted to figure out the average growth rate in percenatge from jul - mar. I have taken the monthly percentage difference of the data and then subtracted it from 1, then i applied GEOmean formula. but can not be satsified by the answer. the answer i come through is -8%. Is this the right answer?. the formula i used to come to this answer is =GEOMEAN(K2:R2+1)-1. i took the percenatge difference then subtract1 from the answer then applied thet formula. Please anybody help me to solve this as i have importnt presentationn to make in which i have to show the growth rate % .

149.11
212.35
195.6
166.47
160.8
146.63
179.18
141.4
155.62

Morning,

Has anybody ever come up with a way to discover the a compound interest growth rate based on actual numbers... for example;

Jan05 - 75
Jan06 - 150
Jan07 - 290
Jan08 - 440

I have a long list of investments that I am working on for a colleague, and want to show the growth in interest rate. In addition, I have payment schedules from loans for which I want to do the same, although I imagine the formula will be the same.

Any suggestions?

Cheers,

Paul

Hello,

Is it possible to calculate the compound annual growth rate (CAGR) for a period with only the returns by percentage instead of the actual values? For example, I have the quarterly returns for a three-year period, dates and percentages [comma-separated]:Sep-04,Dec-04,Mar-05,Jun-05,Sep-05,Dec-05,Mar-06,Jun-06,Sep-06,Dec-06,Mar-07,Jun-07,Sep-07
1.00,9.90,-1.10,1.30,5.00,2.10,6.04,-1.40,3.00,6.10,2.40,4.25,2.62The way I used to calculate the CAGR was to create arbitrary intermediate values: Say I start with a value of $100 (I think any value will work) for Jun-04, the Sep-04 value would be $101.00 ($100 + (1*0.0100)), and Dec-04 would be $111.00 ($101.00 + (1+0.0990)), and so on until Sep-07 is $149.23:101.00,111.00,109.78,111.21,116.77,119.22,126.42,124.65,128.39,136.22,139.49,145.42,149.23Then I would use the RATE function: =RATE(3, ,-101.00, 149.23), where "3" represents the three-year period, the blank is the omitted payments argument, "-101.00" is the negated present value [Aside: I don't understand the negating it, but it is necessary for it to work], and "149.23" is the future value. This formula yields a result of 13.90 %.

I have looked at the XIRR function, but that does not appear to meet my my needs. Because I only know the return by percent, is there a way to calculate this same CAGR (13.90 %) without creating the "placeholder" values?

Thank you very much!
Ryan

PS
Is there a better way to display sample data since a file can't be attached? I found if a line of "cells" from the post is copied and pasted into Excel and then, in 2007, go to
Data > Data Tools > Text to Columns > Comma delimited
then things worked OK (but tabs (if I used tabs) only worked if the line was copied from the HTML source).

Hi,

Not sure how relevant this is... but I need help calculating a monthly
growth rate for the following:

Start Value in Month 1: 750,000
End Value in Month 12: 1,500,000

How do I calculate the monthly growth rate so that I start with 750,000 and
end up with 1,500,000 on the twelfth month?

Thanks in advance

DATE VALUE
1959-01-01 286.6
1959-02-01 287.7
1959-03-01 289.2
1959-04-01 290.1
1959-05-01 292.2
1959-06-01 294.1
1959-07-01 295.2
1959-08-01 296.4
1959-09-01 296.7
1959-10-01 296.5
1959-11-01 297.1
1959-12-01 297.8
1960-01-01 298.2
1960-02-01 298.5
1960-03-01 299.4
1960-04-01 300.1
1960-05-01 300.9
1960-06-01 302.3
1960-07-01 304.1
1960-08-01 306.9
1960-09-01 308.4
1960-10-01 309.5
1960-11-01 310.9
1960-12-01 312.4
1961-01-01 314.1

This is the monthly data. How can I calculate the growth rate from 1959
to 1960?

Thanks in advance

--
kotlon
------------------------------------------------------------------------
kotlon's Profile: http://www.excelforum.com/member.php...o&userid=35431
View this thread: http://www.excelforum.com/showthread...hreadid=552329

I understand how to use the custom calculations in the field settings in
pivot tables to derive a simple annual growth rate, using % Difference From,
(previous), and the year field .

Does anyone know a similar way to generate compound annual growth rates
(CAGRs) in a pivot table for each year in the series from the initial base
year?

I have tried doing a calculated field, but then the column total reflects a
sum of the CAGRs and not the CAGR for the total.

Can someone help define the formula to calculate the compound average growth
rate (CAGR) starting with an initial amount which grows or shrinks depending
upon the earnings for the year? Is the formula included in the formulae
included in Excel?

That data is in columns:
A: Year
B: Year's average interest rate growth or loss
C: Value of investment, e,g. starting at $1,000 and changing each year by
the rate in column B.
D: The CAGR effective to that year.

Thank you for your assistance.

Pat

DATE VALUE
1959-01-01 286.6
1959-02-01 287.7
1959-03-01 289.2
1959-04-01 290.1
1959-05-01 292.2
1959-06-01 294.1
1959-07-01 295.2
1959-08-01 296.4
1959-09-01 296.7
1959-10-01 296.5
1959-11-01 297.1
1959-12-01 297.8
1960-01-01 298.2
1960-02-01 298.5
1960-03-01 299.4
1960-04-01 300.1
1960-05-01 300.9
1960-06-01 302.3
1960-07-01 304.1
1960-08-01 306.9
1960-09-01 308.4
1960-10-01 309.5
1960-11-01 310.9
1960-12-01 312.4
1961-01-01 314.1

This is the monthly data. How can I calculate the growth rate from 1959 to 1960?

Thanks in advance

How do I cacluate the Compound Annual Growth Rate from 1 number to another
number over 5 years?

Not sure if I am posting in the correct place but here is the issue I am having:

I have US money supply data, arranged monthly from 1975-2008. I need to calculate the monthly growth rates. I would really appreciate some help as I have no clue how to do this.

Hi, Im trying to work out the formulae or fuction that will work out the monthly compound rate of a loan.

The loan details are £140,000 at 7.55% APR for 20 years.

Thanks for looking.

Hi,

Not sure how relevant this is... but I need help calculating a monthly
growth rate for the following:

Start Value in Month 1: 750,000
End Value in Month 12: 1,500,000

How do I calculate the monthly growth rate so that I start with 750,000 and
end up with 1,500,000 on the twelfth month?

Thanks in advance

I understand how to use the custom calculations in the field settings in pivot tables to derive a simple annual growth rate, using % Difference From, (previous), and the year field .

Does anyone know a similar way to generate compound annual growth rates (CAGRs) in a pivot table for each year in the series from the initial base year?

Hi there,

This is my first post so I am keeping my fingers crossed!

I am trying to show Y/Y growth rate and I can only manage to do M/M by doing

1. Summerize value field by "Sum"
2. Show value as "% Difference From"
3. Base Filed "Month"
4. Base Item "(Previous)"

When I change Base Field from Month to Year then I got N/A.

Please help!

Thanks!

This is my Data:

month (Multiple Items)

Values
Row Sum of Net value The Growth Rate
2007 14,378,194
2008 2,762,771 #DIV/0!
2009 3,595,793 #DIV/0!
2010 624,715 #DIV/0!
Grand Total 21361473.11

I am trying to calculate the growth rate for each year by doing the following:

Value field settings ----> summarized by (sum) ----> show value as ----> % difference from (Base field:year,Base Itemrevious)

But I keep getting the error shown in the table above DIV/0

I guess because the fist year GR equals zero.
I don't how to overcome this problem.
And I don't know if this is the right way to calculate the Growth Rate to start with.

Thanks.