# 500 points if you create the Excel formula for this.

Let's do some simple rounding, to make this rather complex question easier to answer.

I track my company's monthly income in an Excel spreadsheet where Column A contains the months (dating back to January of '03), and Column B contains the amounts.  For example, A1=01/03, A2=02/03, B1=2000, B2=2100, A72=12/08, etc.
Let's say my company made \$5,000 in March (so B15=5000).
And let's say I have a goal for my company to make \$1,000,000 in the calendar year 2008, so SUM(B61..B72) is 1,000,000.
By what consistent percentage will I have to increase my company's income every month to be able to hit \$1,000,000 in 2008?

I want to be able to enter a formula into an Excel spreadsheet so that I can change the goal, and the target date to achieve that goal.

###### Who is Participating?

Commented:
If the income of last month (in B15) was X and your goal is Y (1,000,000) in N months (72 - 15), it means that you need to extract the percentage P from the following formula:
X * (1 + P)^N = Y

If you divide both sides by X, take the natural logarithms, and use the logarthm property that
ln(a^b) = b * log(a)
you have:
N * ln(1 + P) = ln(Y/X)

Now you need to move N to the right and take the exponential of both sides, because it is the inverse function of the natural logarithm:
exp(ln(1 +P)) = exp(ln(Y/X)/N)
which finally gives you:
P = exp(ln(Y/X)/N) - 1

In Excel this formula is written as follows:
=EXP(LN(Y/X)/N)-1
where Y and X are the cells where your goal and the March revenue are stored.  Obviously, you can also store your goal directly in the formula:
=EXP(LN(1000000/B15)/N) - 1

If you want to have a parametric N, you can use the function COUNTA(cell-range) which counts the non-empty cells in a range.  Therefore, COUNTA(A:A) would count the months.  Another useful function in this context might be ROW(reference) which gives you the row of a cell.  Therefore:
COUNT(A:A) - ROW(B15)
would calculate for you the number of months left (72 - 15)

There are perhaps financial functions which would be useful, but I am familiar with the Math functions...
0

Author Commented:
Thanks for the answer, and the explanation.  I appreciate the time you took in putting it together.

However, your formula gives me the percentage if I wanted to reach \$1,000,000 in the month of December 2008, not \$1,000,000 for the entire year.

While a million dollar MONTH would be *wonderful*, my example calls for an ANNUAL goal based on a consistent MONTHLY increase.

Is there a tweak?  Call me wacky, but I don't know that simply dividing or multiplying something (I don't know what) by 12 would throw the numbers off, even if only marginally.
0

Commented:
For the year
Y=X((1+P)^12-1)/P
0

Commented:
Sorry, you want the year  45 months from now;
Y=X((1+P)^57-(1+P)^45)/P
0

Commented:
You are right about my formula, 1M\$/month would be much harder!

You could simply divide 1,000,000 by 12 and use that as a target in my formula.  It would tell you what monthly increase you should sustain till end 2008 in order to get a monthly income that would correspond to a 1,000,000/year.
Obviously this would mean that in 2008 you would not yet earn a 1,000,000.  Roughly, if you set to earn 8,333\$/month for June 2008, you would get close to 1M\$ for the whole year (just a bit less).

ozo, your formula is equivalent to saying that the increase in monthly earning in 2008 (i.e. the difference between the earnings of Dec 2008 and Dec 2007) equals Y * P.  I would like to understand it.  Can you explain it please?  Is it an approximation?
0

Commented:
It is not an approximation.  It comes from the formula for geometric progressions:
Sum(N=1..M:X*(1+P)^N) = X * (1-(1+P)^N)/(1-(1+P))
0

Author Commented:
Here's what I've done so far:

I built out the spreadsheet I described so that I set a cell as "rate", then I multiply B1..B72 by rate, using SUM(61..72) as the total for calendar year 2008.  Then I use "trial and error" in the "rate" cell to arrive at the correct percentage, and I've figured out the correct percentage to 7 decimal places.

But that only works for one month.  I want a formula I can plug in each month, so I can set the bar for the following month at a glance.

PointyEars, dividing by 12 sets me up for a 2008 where I would have no "target growth" from Jan-08 thru Dec-08.

ozo, your suggestion sounds intriguing, but putting that into an Excel formula is over my head.  Can you help me with that end of it, and I'll plug it in and see if it works?
0

Author Commented:
By the way, folks, feel free to tell me that what I'm looking for is completely circular and can't be solved in a simple Excel statement, but I have a hunch that's not the case.
0

Commented:
ozo, silly of me to ask.

The formula for the sum of a geometric series is from N=0 to N=M-1, not from N=1 to N=M as you said.  But even if we had to slightly modify your formula, we still wouldn't have helped him: we should extract P from it and, I confess, I don't know how to do that.

Further, the fact that neilhedley calculated something to 7 significant digits suggests to me that [s]he wouldn't be satisfied with an approximate formula (say, expanding the powers to polynomial and only take the terms to P^2)...

I think I give up...  Sorry...
0

Commented:

We have this months figure, say 'x'. We want to achieve a year sometime in the future (say 'n' months) a total of a value 'G', your goal.

To keep it simple we will cut out compound growth. If we consistently increase by 'p' percent of THIS months figure every month from now on, the amount we will get 'n' months from now should be x + (n * ( x * p )). So the year starting with that month will total to 12 * the average for that year. We are assuming linear growth so the average for that year should hit on month 6 so we can get the years total from (x + ((n + 6) * ( x * p ))) * 12. We therefore want to choose p such that your goal is achieved.

(x + ((n + 6) * ( x * p ))) * 12 = G

my schoolboy maths suggests

p = (((G/12)-x)/(n+6))/x

If you want compound growth ask someone else.

0

Commented:
first I will put name to some amounts allready used in this discussion, and as it has been stated how to get them from the excel sheet, I suppose it is allready available to your formula:

Initial month you are earning X\$ (5000 in your example), then you pass m months (61 - 15 = 46 in your example) multiplying incomes by P (P is unknow, but P>1 luckly! having P like a factor >1 avoid using (P+1) everywhere), then we reach beginning of the year we want to sum incomes, in the following 12 months, you still increase incomes by P and sum of incommings must be Y ( = 1,000,000\$ on the example)

OK, now that variables are clear, lets follow this:

Initial monthly incomes are X\$, following month increase to X*P, next to X*P*P, and so on, so after waitting for m month, monthly incomes are (X*P^m) on january 2003.

So we start year 2008 earning (X*P^m) , and end up earning (X*P^(m+11)) at the last month (we will call it I as I-nitial amount of the 2008 year) of this year, so the sum is calculated as any other geometrical serie, calling I = (X*P^m) and being P the factor, so SUM(I, I*P, ... , I*P^11) equals I*(1-P^12)/(1-P).

Now we have the problem clearly stated:

I:=  (X*P^m) (having X = 5000)
I*(1-P^12)/(1-P) = Y (having Y=1,000,000)

Melting both xpressions we come to our finall equation (remember X and Y are constants):

X* (P^m) * (1-P^12) / (1-P) = Y

Now I try to  make it simplier (hard stuff):

(P^m) * (1-P^12) / (1-P) = Y/X (divide by X both sides)

(P^m) * (1-P^12) = (Y/X) * (1-P)

P^m - P^(m+12) = (Y/X) - (Y/X)P

P^m - P^(m+12) + (Y/X)P - (Y/X)  = 0

Finally we have our problem as a (m+12) grade polinomic to be solved (find the point where it becomes zero), and there is no generic way to calculate it directly (as m+12 is always > 5, the biggest number for witch there is a direct formula for polinomials).

So I think THERE IS NO direct way to solve the problem, unless this particular polinomic formula can be solved in some "intuitive way"... but I can't figure out how to solve this formula directly by now!

The most simple way to look at this formula is like this (changing all variables by the real values and name variable as X to look more familiar to all):

X^46 - X^58 + 200*X -200 = 0

And here is where race ends... I can't solve this at first glance... may be if you give that formula to mathematica or mapple and ask it to simplify, may be you get a solution, other way, you need to use solver or similar in excel.
0

Commented:
I have to agree with Sergio_Hdez.  I tried to approximate the formula by re-introducing (1 + p) with p < 1 and neglecting the powers of p greater than 2, but there are too many months between now and the end of 2008 and the exponential growth "runs away" from my approximation.

Therefore, I join Sergio_Hdez in saying that no solution is possible!
0

Commented:
Sergio Hdez, I arrived at your same final formula. I prefer to keep at as (P^m) * (1-P^12) / (1-P) - Y/X =0.
Then to solve this just use the numerical method of execl (goal seek).
Put a guessed valued of P in cell, e.g. in C1 enter 1,05
then enter (e.g. in C2) the formula but replacing P with refences to C1
finally do menu->tools->goal seek
set cell C2
to value 0
by changing cell C1
After this you get the proper growth rate in C1 and you can use it to calculate each month expected income.

0

Commented:
See my proposed solution that describes how to automate Goal Seek at:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20938891.html
0

Commented:
The graphic flexibility of Excel is the key here.  Let Excel do the work.
1. Start by projecting out the years in the first row, month by month, leaving a couple of columns free at the beginning.
2. In the first open row of the March 2004 column, place the formula:
= 1*A2
and extend the formula across the row for as many years as you want.
3. In A2 enter "1.0558", or any other estimate.
4. In each December column, create a cell that subtotals that year alone. ( If you want to make 1M, from 5000, then you want the year 2008 subtotal to equal 200).
5. Tweak A2. I also created a second row so I could look at differences between estimates. It took about four minutes to get 1000000.004 for 2008 (@1.0557902558/month).
6. This method allows you to insert values anywhere in the calendar. You just have to project out from any hard-value insertion point.
7. Obviously, if in E2 you used the formula
=5000*A2
you would be looking for a 1000000 subtotal in 2008.
0

Commented:
I would just split the points amongst all those that have given a direct contribution to answering the question (in order of first comment): PointyEars, ozo, Sergio_Hdez, lbertacco, nsstone, and perhaps ChadClancy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.