Optimize by minimizing the value of an equation to get a parameter value for each observation

[Nazmul Hasan]

Dear Everyone,

Software Used: STATA 15.1

I am trying to get a parameter value (R) for each observation by minimizing the following equation. I could not figure out the exact STATA code I should write to get my desired value. I used the excel solver to estimate the value (R) for your reference. I also put the data below. Please help me with your STATA code to solve the problem. I highly appreciate your help

The equation to Solve (Here I want to get the value of R for each observation):

Price2_Y0=(B0_T1+((FROE1_T1_Med-R)/(1+R))*B0_T1
+((FROE2_T1_Med-R)/((1+R)^2))*B1_T1_Med
+((FROE3_T1_Med-R)/((1+R)^3))*B2_T1_Med
+((FROE4_T1_Med_I1-R)/((1+R)^4))*B3_T1_Med
+((FROE5_T1_Med_I1-R)/((1+R)^5))*B4_T1_Med_I1
+((FROE6_T1_Med_I1-R)/((1+R)^6))*B5_T1_Med_I1
+((FROE7_T1_Med_I1-R)/((1+R)^7))*B6_T1_Med_I1
+((FROE8_T1_Med_I1-R)/((1+R)^8))*B7_T1_Med_I1
+((FROE9_T1_Med_I1-R)/((1+R)^9))*B8_T1_Med_I1
+((FROE10_T1_Med_I1-R)/((1+R)^10))*B9_T1_Med_I1
+((FROE11_T1_Med_I1-R)/((1+R)^11))*B10_T1_Med_I1
+((FROE12_T1_Med_I1-R)/(R*(1+R)^11))*B11_T1_Med_I1)

Sample Data with R-Value calculated using Excel Solver:

obs	B0_T1	Price2_Y0	B1_T1_Med	B2_T1_Med	B3_T1_Med	B4_T1_Med_I1	B5_T1_Med_I1	B6_T1_Med_I1	B7_T1_Med_I1	B8_T1_Med_I1	B9_T1_Med_I1	B10_T1_Med_I1	B11_T1_Med_I1	FROE1_T1_Med	FROE2_T1_Med	FROE3_T1_Med	FROE4_T1_Med_I1	FROE5_T1_Med_I1	FROE6_T1_Med_I1	FROE7_T1_Med_I1	FROE8_T1_Med_I1	FROE9_T1_Med_I1	FROE10_T1_Med_I1	FROE11_T1_Med_I1	FROE12_T1_Med_I1	R	1	2	3	4	5	6	7	8	9	10	11	12	Sum	dif
1	12.04	17.5	12.96	14.11	15.46	17.09186	19.06786	21.46758	24.39325	27.97684	32.38978	37.85617	44.67083	0.070988	0.081502	0.087322	0.095476	0.10363	0.111784	0.119937	0.128091	0.136245	0.144399	0.152553	0.160707	11%	-0.4457	-0.3212	-0.25475	-0.16857	-0.08567	-0.00377	0.079354	0.166052	0.258872	0.360704	0.474932	5.399746	17.5	-3.5E-06
2	4.1943	10.61	-2.66464	-9.81995	-17.441	-25.5259	-31.7687	-34.3922	-32.9443	-28.2985	-22.0324	-15.6856	-10.2866	-0.60796	-0.1721	-0.10321	-0.07481	-0.04641	-0.01802	0.01038	0.038776	0.067172	0.095568	0.123965	0.152361	10%	-2.69333	0.594159	1.484183	2.062363	2.295877	2.08637	1.546039	0.9009	0.352807	-0.00055	-0.16366	-2.04941	10.61005	4.7E-05
3	53.42	168.11	63.08794	74.17822	86.74057	100.6832	116.0122	132.7046	150.7046	169.9214	190.2273	211.4572	233.4098	0.154071	0.150314	0.145607	0.139226	0.132844	0.126463	0.120082	0.113701	0.10732	0.100939	0.094558	0.088177	6%	4.80285	5.145318	5.419362	5.544526	5.595282	5.5635	5.442793	5.228879	4.919875	4.516513	4.022248	58.48874	168.1099	-0.00012
4	12.6785	39.26	12.364	11.99335	11.61146	11.22855	10.84555	10.46336	10.08285	9.704834	9.330102	8.959396	8.593415	0.045293	0.055031	0.058563	0.060722	0.062881	0.06504	0.067199	0.069358	0.071517	0.073675	0.075834	0.077993	2%	0.331172	0.433054	0.452443	0.453282	0.452393	0.449901	0.445931	0.440604	0.434044	0.42637	0.417699	21.84461	39.26001	5.8E-06
5	11.24	52.87	13.16	15.37	17.89	20.72743	23.90499	27.44408	31.36423	35.68253	40.41294	45.56567	51.14654	0.145897	0.143787	0.140861	0.136893	0.132924	0.128956	0.124988	0.12102	0.117052	0.113084	0.109115	0.105147	5%	0.994257	1.080689	1.160364	1.225119	1.284677	1.337677	1.382758	1.418579	1.44386	1.457413	1.45817	27.38643	52.87	-3.7E-06