Solved

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

Posted on 2004-03-30
534 Views
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!
0
Question by:neilhedley
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 3
• 3
• +5
19 Comments

LVL 5

Accepted Solution

PointyEars earned 74 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:
=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 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.
0

LVL 84

Assisted Solution

ozo earned 71 total points
ID: 10718812
For the year
Y=X((1+P)^12-1)/P
0

LVL 84

Expert Comment

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

LVL 5

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?
0

LVL 84

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))
0

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?
0

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.
0

LVL 5

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...
0

LVL 16

Assisted Solution

PaulCaswell earned 71 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.

0

LVL 6

Assisted Solution

Sergio_Hdez earned 71 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.
0

LVL 5

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!
0

LVL 11

Assisted Solution

lbertacco earned 71 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.

0

LVL 5

Assisted Solution

ChadClancy earned 71 total points
ID: 10726347
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

LVL 2

Assisted Solution

nsstone earned 71 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
=5000*A2
you would be looking for a 1000000 subtotal in 2008.
0

LVL 5

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.
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Title # Comments Views Activity
Math question 3 124
Autosar OS Multicore Share Resources confusion ? 2 195
Understanding factorial 4 55
statistics basics sources 2 73
Complex Numbers are funny things.  Many people have a basic understanding of them, some a more advanced.  The confusion usually arises when that pesky i (or j for Electrical Engineers) appears and understanding the meaning of a square root of a negaâ€¦
We are taking giant steps in technological advances in the field of wireless telephony. At just 10 years since the advent of smartphones, it is crucial to examine the benefits and disadvantages that have been report to us.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
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â€¦
###### Suggested Courses
Course of the Month6 days, 16 hours left to enroll

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

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