Goal seek/ Iteration help needed

[Ben Stork]

Dear All,

For my thesis, I need to solve the following equation with two unknowns (p) and (k).

In which p = FCF/(k - g) - L - N

and solving for k, gives; k = (g*N+g*L+FCF+g*p) / (NCI+L+P)

Normally it would be something I'd solve by the goal seek function in excel but I now need to execute it for 100.000+ individual observations in Stata. I haven't been able to figure out how to implement it by reading through the forum. Hopefully, somebody can help me out or point me in the right direction.

Kind regards,
Ben

[Clyde Schechter]

I don't understand your question. Do you have a data set with observations on variables g, N, L, FCF, and NCI, and you are trying to find values of k and p that fit the equation you show, once for each observation? That can't be done because with two unknowns, you need a pair of observations on the other variables to identify a unique solution. Without that there will, in general, be infinitely many solutions.

Or are those two different equations that you want to solve jointly for k and p? You say that the second one is gotten by solving the first one for k, but the algebra says otherwise. Solving the first one for k gives

k = g + FCF/(p+L+N)

Also the first equation involves a variable NCI that does not appear in the second one.


Can you explain in more detail? Also, show some example data, please, using the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Ben Stork]

Hi Clyde, thank you for your reply. I indeed try to find two unknowns. I notice that I accidentally left in "NCI" in the second equation like you pointed out. I am sorry for the confusion. The unknowns however do have an underlying relation for which solving should work.

What I am trying to achieve is part of a perpetual growth function in which FCFU grows to infinity at FCFU/(k-growth), creating FCFUtv. k, therein is what in finance is regarded as the WACC(weighted average cost of capital) and dependent on the weighting of p, L5 and NCI5 times their independent costs of capital (Re and Rd). While these weightings in turn, again depend on the value of FCFUtv (p) (given FCFUtv = p + L5 + NCI5).

As such I am trying to solve for the value of p, that creates the right weight in the calculation of k such that FCFUtv - p - L5 - NCI5 = 0

Like you advised, I used -dataex- to retrieve the columns it is regard and inserted the functions.

input float(FCFU FCFUtv k growth L5 NCI5 p Re Rd )

FCFU	FCFUtv	k	growth	L5	NCI5	p	Re	Rd	Weight_p	Weight_L5	Weight_NCI5
120.6	=FCFU/(k-g)	=((Weight_p*Re)+(Weight_L5*Rd)+(Weight_NCI5*Re)	0.02	5	3	=FCFUtv - L5 - NCI5	0.10	0.05	=(p/FCFUtv)	= (L5/FCFUtv)	= (NCI5/FCFUtv)
120.4		.	0.02	10	4	.	0.10	0.05
261.2		.	0.02	8	5	.	0.10	0.05
261.2		.	0.02	2	6	.	0.10	0.05
261.2		.	0.02	13	7	.	0.10	0.05
580.0		.	0.02	14	8	.	0.12	0.07
312		.	0.02	15	8	.	0.13	0.08

Hopefully this helps structuring the problem.

kind regards,
Ben

[Clyde Schechter]

Sorry, but now I'm even more confused. Not only do you have to solve of k and p, but it appears you also have to solve for three unknown weights. That's five unknowns and one (or two?) equations.