Finding unknown variable (n) - time period - through excel.

[gjergj]

Hi everyone,

Was trying to figure out the below equation on how to turn it in order to find the missing variable but seems mission impossible for me. I think it should be somehow turned to a logarithmic one but i am not sure.

Either way, I was hoping excel could help into solving this issue. I have seen that many use it for equations too but never get the chance to use that and the videos in youtube are a bit for easy things.

I want to find (n) value which is the time to reach breakeven point in the above equation. Any ideas or suggestions? thanks a lot

 

[gjergj]

Hi everyone,

Was trying to figure out the below equation on how to turn it in order to find the missing variable but seems mission impossible for me. I think it should be somehow turned to a logarithmic one but i am not sure.

Either way, I was hoping excel could help into solving this issue. I have seen that many use it for equations too but never get the chance to use that and the videos in youtube are a bit for easy things.

View attachment 22056


I want to find n value which is the time to reach breakeven point in the above equation. Any ideas or suggestions? thanks a lot

 

[joeu2004]

I suspect that there is an algebraic solution. But the first "obvious" thing to do is to use Solver.

Book1
	A	B	C
1	12.0841346228067	n
2	359,772.702509101	RHS	A2: =22395*(1.05^A1-1)/0.05
3	359,772.702509101	LHS	A3: =46000 + (14640+6060)*(1.04^A1-1)/0.04
4	0.00E+00	RHS-LHS	A4: =A2-A3
Sheet1

Solver set-up:
Set objective: A4
To value: 0
By changing: A1

I took the liberty of doing some algebraic simplification.

And of course, A1 -- n -- is empty to begin with.

In the approach above, I actually did 2 iterations of Solver in order to get A4 down to zero exactly(!). Alternatively, I could have changed some precision options.

Alternatively, I could have represented the entire equation in one formula by entering the following into A4:

=46000 + (14640+6060)*(1.04^A1-1)/0.04 - 22395*(1.05^A1-1)/0.05

 

[joeu2004]

To late to edit....

Alternatively, I could have represented the entire equation in one formula by entering the following into A4:
=46000 + (14640+6060)*(1.04^A1-1)/0.04 - 22395*(1.05^A1-1)/0.05

Or more simply:

=46000 + FV(4%, A1, -14640-6060) - FV(5%, A1, -22395)

I suspect that is what you started with, and the more complicated expressions evolved as a way (hopefully) to find an algebraic solution for n.

 

[gjergj]

I think I started from a more complicated one or at a least so it looked like to me :D

So my goal was to find the breakeven point in time if the revenues and costs are each being increased by a diff % amount. The goal was to have three diff variables, so basically one variable for the revenue increase, one for the labor costs & another one for non labor costs. so would make sense though that the increase in revenues should not be lower than the increase in costs.

Revenue 1 - is the revenues in the first year
Investment - initial investment (does not change)
Labor cost1 - is the cost in year 1
Non labor cost 1 - is the cost in year 1
a - increase in revenues
b - increase in costs
c - increase in costs (b not equal to c)

The base formula used to get in the above one was this one:

.


I will try your solution in excel but the result was matching so it was cool thanks a lot

One question - does solver work if Instead of values I put cell references? So based on diff tests/or simulations it gives diff data?


Thanks man!

 

[gjergj]

To late to edit....


Or more simply:

=46000 + FV(4%, A1, -14640-6060) - FV(5%, A1, -22395)

I suspect that is what you started with, and the more complicated expressions evolved as a way (hopefully) to find an algebraic solution for n.

I think I started from a more complicated one or at a least so it looked like to me :D

So my goal was to find the breakeven point in time if the revenues and costs are each being increased by a diff % amount. The goal was to have three diff variables, so basically one variable for the revenue increase, one for the labor costs & another one for non labor costs. so would make sense though that the increase in revenues should not be lower than the increase in costs.

Revenue 1 - is the revenues in the first year
Investment - initial investment (does not change)
Labor cost1 - is the cost in year 1
Non labor cost 1 - is the cost in year 1
a - increase in revenues
b - increase in costs
c - increase in costs (b not equal to c)

The base formula used to get in the above one was this one:

.


I will try your solution in excel but the result was matching so it was cool thanks a lot

One question - does solver work if Instead of values I put cell references? So based on diff tests/or simulations it gives diff data?


Thanks man!

 

[joeu2004]

I suspect that there is an algebraic solution.

I don't think so.

We can reduce the equation to the following, collecting terms:

22395*0.04*1.05^n - (14640+6060)*0.05*1.04^n = 46000*0.05*0.04 - (14640+6060)*0.05 + 22395*0.04 = -47.2

That is of the form: a*(x^n) - b*(y^n) = c .

I don't believe that is solvable algebraically. I tried using wolframalpha.com to find a solution to the general equation, to no avail.

 

[joeu2004]

One question - does solver work if Instead of values I put cell references? So based on diff tests/or simulations it gives diff data?

No need to duplicate postings. Our responses simply crossed on the "ether". Try the following.

annuity breakeven.xlsx
	A	B
1	$22,395.00	Revenue
2	$46,000.00	Investment
3	$14,640.00	Labor costs
4	$6,060.00	Non-labor costs
5	5.00%	%revenue growth (a)
6	4.00%	%labor costs increase (b)
7	4.00%	%non-labor costs increase (c)
8	12.0841346228067	n
9	-2.62E-10	breakeven
Sheet2

The formula is A9 is:

=FV(A5,A8,-A1) - A2 - FV(A6,A8,-A3) - FV(A7,A8,-A4)

The Solver set-up is:
Set objective: A9
To value: 0
By changing: A8

Again, A8 ( n ) starts is empty to begin with.

And again, I got the nearly-zero value in A9 (-2.62E-10) by executing Solver twice. I can even get exactly zero (0.00E+00) by setting Solver options. But that is just a coincidence.

If you are unfamiliar with Scientific notation, -2.62E-10 is -2.62 times 10 to the -10 power. In this case, it is about -0.000000000262 .

 

[gjergj]

I don't think so.

We can reduce the equation to the following, collecting terms:

22395*0.04*1.05^n - (14640+6060)*0.05*1.04^n = 46000*0.05*0.04 - (14640+6060)*0.05 + 22395*0.04 = -47.2

That is of the form: a*(x^n) - b*(y^n) = c .

I don't believe that is solvable algebraically. I tried using wolframalpha.com to find a solution to the general equation, to no avail.

No need to duplicate postings. Our responses simply crossed on the "ether". Try the following.

annuity breakeven.xlsx
	A	B
1	$22,395.00	Revenue
2	$46,000.00	Investment
3	$14,640.00	Labor costs
4	$6,060.00	Non-labor costs
5	5.00%	%revenue growth (a)
6	4.00%	%labor costs increase (b)
7	4.00%	%non-labor costs increase (c)
8	12.0841346228067	n
9	-2.62E-10	breakeven
Sheet2

The formula is A9 is:

=FV(A5,A8,-A1) - A2 - FV(A6,A8,-A3) - FV(A7,A8,-A4)

The Solver set-up is:
Set objective: A9
To value: 0
By changing: A8

Again, A8 ( n ) starts is empty to begin with.

And again, I got the nearly-zero value in A9 (-2.62E-10) by executing Solver twice. I can even get exactly zero (0.00E+00) by setting Solver options. But that is just a coincidence.

If you are unfamiliar with Scientific notation, -2.62E-10 is -2.62 times 10 to the -10 power. In this case, it is about -0.000000000262 .

Okay thanks a lot. I will try this one

Yeah I spent like searching a lot to find a solution to that equation but was unable too. Still using excel for this and the way you suggested seems way perfect for my case. I do really appreciate your help here.

 

[Dave Patton]

You could probably use Goal Seek instead of Solver.