Go Premium for a chance to win a PS4. Enter to Win


500 points if you create the Excel formula for this.

Posted on 2004-03-30
Medium Priority
Last Modified: 2012-06-27
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.

I look forward to your answers!
Question by:neilhedley
  • 5
  • 3
  • 3
  • +5

Accepted Solution

PointyEars earned 296 total points
ID: 10717266
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:
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...

Author Comment

ID: 10717423
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.
LVL 85

Assisted Solution

ozo earned 284 total points
ID: 10718812
For the year
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 85

Expert Comment

ID: 10718858
Sorry, you want the year  45 months from now;

Expert Comment

ID: 10719320
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?
LVL 85

Expert Comment

ID: 10719422
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))

Author Comment

ID: 10719813
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?

Author Comment

ID: 10719825
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.

Expert Comment

ID: 10721720
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...
LVL 16

Assisted Solution

PaulCaswell earned 284 total points
ID: 10721770
How about this,

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.


Assisted Solution

Sergio_Hdez earned 284 total points
ID: 10722091
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.

Expert Comment

ID: 10724821
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!
LVL 11

Assisted Solution

lbertacco earned 284 total points
ID: 10725571
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.


Assisted Solution

ChadClancy earned 284 total points
ID: 10726347
See my proposed solution that describes how to automate Goal Seek at:


Assisted Solution

nsstone earned 284 total points
ID: 10797288
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
you would be looking for a 1000000 subtotal in 2008.

Expert Comment

ID: 10976258
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.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
This article covers the basics of data encryption, what it is, how it works, and why it's important. If you've ever wondered what goes on when you "encrypt" data, you can look here to build a good foundation for your personal learning.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question