Solver works half the time for the same problem

[EthanP]

I created a macro that uses solver to help setup the first value in column F. In solver I set the objective cell to the value in column C to a max value by changing the value in column F. The constraints are column C must equal column B. The values in column F thru Q represent income over 12 months and are increasing by 3% over those 12 months. The values in column C are the sum of column F thru Q. I have discovered a few things when trying to get this to work... first if I have the macro (Macro12) take the values in column B and divide by 12.2months in column F it helps get solver closer to an answer. Second, for this problem it seems as though solver does not like values under $1,500. Third, solver uses values/answers that are E-13 ( E to the power of -13 or 13 digits on the right side of the decimal) when I manually enter (or manually use random numbers) values I can figure out an answer that is only 3 to 4 digits on the right side of the decimal. I my mind solver is trying to hard and it does not take a 13 digit or longer value to solve this problem. Also, if this can be done using formulas that would be better than solver and a macro.

Experiment Valuator REV B.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		NOTE: you have to guess first and get the first number close to the value in column B in order for solver to figure it out. If the numbers are to far apart solver does not like the answer. In order to do this, in column F (=B5/12) this gets close so solver can find the answer		Month	Acquisition	1	2	3	4	5	6	7	8	9	10	11	12
2				Year	Acquisition	1	1	1	1	1	1	1	1	1	1	1	1
3	Check			Month Ending	1/5/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023	7/31/2023	8/31/2023	9/30/2023	10/31/2023	11/30/2023	12/31/2023	1/31/2024
4				Income	Run Macro to set the first value. Formulas> Calculation Options> Auto Mode After running the macro
5	GOOD	$369,840.00	$369,840.00	GPRI		$30,404.17	$30,479.16	$30,554.33	$30,629.68	$30,705.22	$30,780.95	$30,856.87	$30,932.97	$31,009.26	$31,085.73	$31,162.40	$31,239.25
6				Economic Occupancy %
7	GOOD	$277,380.00	$277,380.00	NET Rental Income		$22,803.13	$22,859.37	$22,915.75	$22,972.26	$23,028.92	$23,085.71	$23,142.65	$23,199.73	$23,256.94	$23,314.30	$23,371.80	$23,429.44
8	ERROR	$1,000.00	$997.06	Truck rental Commissions		$81.97	$82.17	$82.37	$82.58	$82.78	$82.98	$83.19	$83.39	$83.60	$83.80	$84.01	$84.22
9	ERROR	$900.00	$897.35	Locks, Moving Supplies		$73.77	$73.95	$74.13	$74.32	$74.50	$74.68	$74.87	$75.05	$75.24	$75.42	$75.61	$75.80
10	ERROR	$800.00	$797.65	Tenant Insurance		$65.57	$65.74	$65.90	$66.06	$66.22	$66.39	$66.55	$66.71	$66.88	$67.04	$67.21	$67.37
11	ERROR	$700.00	$697.94	Vending/Misc		$57.38	$57.52	$57.66	$57.80	$57.95	$58.09	$58.23	$58.37	$58.52	$58.66	$58.81	$58.95
12	GOOD	$600.00	$600.00	Cell Phone Tower		$49.33	$49.45	$49.57	$49.69	$49.81	$49.94	$50.06	$50.18	$50.31	$50.43	$50.56	$50.68
13	ERROR	$500.00	$498.53	Bill Board		$40.98	$41.08	$41.19	$41.29	$41.39	$41.49	$41.59	$41.70	$41.80	$41.90	$42.01	$42.11
14	ERROR	$400.00	$398.82	Retail Income		$32.79	$32.87	$32.95	$33.03	$33.11	$33.19	$33.28	$33.36	$33.44	$33.52	$33.60	$33.69
15	ERROR	$300.00	$299.12	Admin Fee		$24.59	$24.65	$24.71	$24.77	$24.83	$24.89	$24.96	$25.02	$25.08	$25.14	$25.20	$25.27
16	GOOD	$200.00	$200.00	Late Fee		$16.44	$16.48	$16.52	$16.56	$16.60	$16.65	$16.69	$16.73	$16.77	$16.81	$16.85	$16.89
17	ERROR	$100.00	$99.71	Other Income		$8.20	$8.22	$8.24	$8.26	$8.28	$8.30	$8.32	$8.34	$8.36	$8.38	$8.40	$8.42
18	ERROR	$50.00	$49.85	Other Income		$4.10	$4.11	$4.12	$4.13	$4.14	$4.15	$4.16	$4.17	$4.18	$4.19	$4.20	$4.21
19	ERROR	$25.00	$24.93	Other Income		$2.05	$2.05	$2.06	$2.06	$2.07	$2.07	$2.08	$2.08	$2.09	$2.10	$2.10	$2.11
20	ERROR	$10.00	$9.97	Other Income		$0.82	$0.82	$0.82	$0.83	$0.83	$0.83	$0.83	$0.83	$0.84	$0.84	$0.84	$0.84
21				Total (Effective Gross Income)		$23,261.11	$23,318.48	$23,375.99	$23,433.64	$23,491.43	$23,549.37	$23,607.45	$23,665.67	$23,724.04	$23,782.55	$23,841.20	$23,900.00
Monthly Cash Flow

Cell Formulas
Range	Formula
G1:Q1	G1	=F1+1
F2:Q2	F2	=ROUNDUP(F1/12,0)
E3	E3	='Summary Sheet'!B7
F3:Q3	F3	=EOMONTH(E3,1)
A5,A7:A20	A5	=IF(C5=B5,"GOOD","ERROR")
B5,B7:B20	B5	='Projections '!C6
C5,C7:C20	C5	=ROUND(SUM(F5:Q5),2)
G7:Q20,G5:Q5	G5	=F5*(1+HLOOKUP(ROUNDUP(F$1/12,0),'Projections '!$C$1:$AF$2,2,0))^(1/12)
F21:Q21	F21	=SUM(F7:F20)

Named Ranges
Name	Refers To	Cells
'Monthly Cash Flow'!solver_adj	='Monthly Cash Flow'!$F$16	F21, C16, G16
'Monthly Cash Flow'!solver_lhs1	='Monthly Cash Flow'!$C$16	A16
'Monthly Cash Flow'!solver_lhs10	='Monthly Cash Flow'!$C$8	A8
'Monthly Cash Flow'!solver_lhs2	='Monthly Cash Flow'!$C$5	A5
'Monthly Cash Flow'!solver_lhs3	='Monthly Cash Flow'!$C$7	A7
'Monthly Cash Flow'!solver_lhs4	='Monthly Cash Flow'!$C$5	A5
'Monthly Cash Flow'!solver_lhs5	='Monthly Cash Flow'!$C$7	A7
'Monthly Cash Flow'!solver_lhs6	='Monthly Cash Flow'!$C$8	A8
'Monthly Cash Flow'!solver_lhs7	='Monthly Cash Flow'!$C$5	A5
'Monthly Cash Flow'!solver_lhs8	='Monthly Cash Flow'!$C$7	A7
'Monthly Cash Flow'!solver_lhs9	='Monthly Cash Flow'!$C$8	A8
'Monthly Cash Flow'!solver_opt	='Monthly Cash Flow'!$C$16	A16
'Monthly Cash Flow'!solver_rhs1	='Monthly Cash Flow'!$B$16	A16
'Monthly Cash Flow'!solver_rhs10	='Monthly Cash Flow'!$B$8	A8
'Monthly Cash Flow'!solver_rhs2	='Monthly Cash Flow'!$B$5	A5
'Monthly Cash Flow'!solver_rhs3	='Monthly Cash Flow'!$B$7	A7
'Monthly Cash Flow'!solver_rhs4	='Monthly Cash Flow'!$B$5	A5
'Monthly Cash Flow'!solver_rhs5	='Monthly Cash Flow'!$B$7	A7
'Monthly Cash Flow'!solver_rhs6	='Monthly Cash Flow'!$B$8	A8
'Monthly Cash Flow'!solver_rhs7	='Monthly Cash Flow'!$B$5	A5
'Monthly Cash Flow'!solver_rhs8	='Monthly Cash Flow'!$B$7	A7
'Monthly Cash Flow'!solver_rhs9	='Monthly Cash Flow'!$B$8	A8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5,A23:A57,A7:A20	Cell Value	contains "ERROR"	text	NO
A5,A23:A56,A7:A20	Cell Value	contains "GOOD"	text	NO

 

[KRice]

Welcome to the MrExcel Board! You don't need Solver for this, as you can compute the starting amounts analytically. I eliminated your HLOOKUP formulas (they reference a worksheet that was not provided so all of the formulas displayed errors). Instead, you mentioned an annualized rate of 3%, so I added some helper cells to the upper right of your table. The annual rate, the monthly rate factor, the number of intervals during the year, and one other term are shown there. The one other term represents the sum of a finite geometric series, which is a well-defined expression representing the summation of terms that occur in months 2-12. Once that is calculated, it is used to compute the starting amount (F5 and down) for each of your line items.

MrExcel_20220910.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Start Date	1/5/2023		Month		1	2	3	4	5	6	7	8	9	10	11	12		Rate of Annual Increase	3%
2				Year		1	1	1	1	1	1	1	1	1	1	1	1		Monthly factor	1.00246627
3				Month Ending	1/5/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023	7/31/2023	8/31/2023	9/30/2023	10/31/2023	11/30/2023	12/31/2023	1/31/2024		Number intervals	11
4	Check	Inputs: Yearly Total	Sum of monthly amts	Income		Calculate starting amt													Sum of Finite Geometric Series	11.1641194
5	GOOD	$ 369,840.00	$ 369,840.00	GPRI		$30,404.17	$30,479.16	$30,554.33	$30,629.68	$30,705.22	$30,780.95	$30,856.87	$30,932.97	$31,009.26	$31,085.73	$31,162.40	$31,239.25
6				Economic Occupancy %		$ -
7	GOOD	$ 277,380.00	$ 277,380.00	NET Rental Income		$22,803.13	$22,859.37	$22,915.75	$22,972.26	$23,028.92	$23,085.71	$23,142.65	$23,199.73	$23,256.94	$23,314.30	$23,371.80	$23,429.44
8	GOOD	$ 1,000.00	$ 1,000.00	Truck rental Commissions		$ 82.21	$ 82.41	$ 82.61	$ 82.82	$ 83.02	$ 83.23	$ 83.43	$ 83.64	$ 83.85	$ 84.05	$ 84.26	$ 84.47
9	GOOD	$ 900.00	$ 900.00	Locks, Moving Supplies		$ 73.99	$ 74.17	$ 74.35	$ 74.54	$ 74.72	$ 74.90	$ 75.09	$ 75.27	$ 75.46	$ 75.65	$ 75.83	$ 76.02
10	GOOD	$ 800.00	$ 800.00	Tenant Insurance		$ 65.77	$ 65.93	$ 66.09	$ 66.25	$ 66.42	$ 66.58	$ 66.75	$ 66.91	$ 67.08	$ 67.24	$ 67.41	$ 67.57
11	GOOD	$ 700.00	$ 700.00	Vending/Misc		$ 57.55	$ 57.69	$ 57.83	$ 57.97	$ 58.12	$ 58.26	$ 58.40	$ 58.55	$ 58.69	$ 58.84	$ 58.98	$ 59.13
12	GOOD	$ 600.00	$ 600.00	Cell Phone Tower		$ 49.33	$ 49.45	$ 49.57	$ 49.69	$ 49.81	$ 49.94	$ 50.06	$ 50.18	$ 50.31	$ 50.43	$ 50.56	$ 50.68
13	GOOD	$ 500.00	$ 500.00	Bill Board		$ 41.10	$ 41.21	$ 41.31	$ 41.41	$ 41.51	$ 41.61	$ 41.72	$ 41.82	$ 41.92	$ 42.03	$ 42.13	$ 42.23
14	GOOD	$ 400.00	$ 400.00	Retail Income		$ 32.88	$ 32.96	$ 33.05	$ 33.13	$ 33.21	$ 33.29	$ 33.37	$ 33.46	$ 33.54	$ 33.62	$ 33.70	$ 33.79
15	GOOD	$ 300.00	$ 300.00	Admin Fee		$ 24.66	$ 24.72	$ 24.78	$ 24.85	$ 24.91	$ 24.97	$ 25.03	$ 25.09	$ 25.15	$ 25.22	$ 25.28	$ 25.34
16	GOOD	$ 200.00	$ 200.00	Late Fee		$ 16.44	$ 16.48	$ 16.52	$ 16.56	$ 16.60	$ 16.65	$ 16.69	$ 16.73	$ 16.77	$ 16.81	$ 16.85	$ 16.89
17	GOOD	$ 100.00	$ 100.00	Other Income		$ 8.22	$ 8.24	$ 8.26	$ 8.28	$ 8.30	$ 8.32	$ 8.34	$ 8.36	$ 8.38	$ 8.41	$ 8.43	$ 8.45
18	GOOD	$ 50.00	$ 50.00	Other Income		$ 4.11	$ 4.12	$ 4.13	$ 4.14	$ 4.15	$ 4.16	$ 4.17	$ 4.18	$ 4.19	$ 4.20	$ 4.21	$ 4.22
19	GOOD	$ 25.00	$ 25.00	Other Income		$ 2.06	$ 2.06	$ 2.07	$ 2.07	$ 2.08	$ 2.08	$ 2.09	$ 2.09	$ 2.10	$ 2.10	$ 2.11	$ 2.11
20	GOOD	$ 10.00	$ 10.00	Other Income		$ 0.82	$ 0.82	$ 0.83	$ 0.83	$ 0.83	$ 0.83	$ 0.83	$ 0.84	$ 0.84	$ 0.84	$ 0.84	$ 0.84
21				Total (Effective Gross Income)		$23,262.27	$23,319.64	$23,377.15	$23,434.81	$23,492.60	$23,550.54	$23,608.62	$23,666.85	$23,725.22	$23,783.73	$23,842.39	$23,901.19
Sheet2

Cell Formulas
Range	Formula
F1:Q1	F1	=COLUMNS($F:F)
F2:Q2	F2	=ROUNDUP(F1/12,0)
E3	E3	=B1
F3:Q3	F3	=EOMONTH($E3,F1)
T2	T2	=(1+$T$1)^(1/12)
T3	T3	=COUNT(F3:Q3)-1
T4	T4	=T2*(1-T2^T3)/(1-T2)
A5,A7:A20	A5	=IF(C5=B5,"GOOD","ERROR")
C5,C7:C20	C5	=ROUND(SUM(F5:Q5),2)
G5:Q5,G7:Q20	G5	=F5*(1+$T$1)^(1/12)
F5:F20	F5	=B5/(1+$T$4)
F21:Q21	F21	=SUM(F7:F20)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:A20	Cell Value	="Error"	text	NO
A5:A20	Cell Value	="Good"	text	NO

 

[EthanP]

THANK YOU VERY MUCH!!!! This works great and solved my issue! I greatly appreciate it!! You saved me lots of time!

 

[KRice]

You're welcome...I'm happy to help. By the way, if you look at your G5 formula:

=F5*(1+$T$1)^(1/12)

and replace it with this:

=$F5*(1+$T$1)^((G$1-1)/12)

...you can build your entire monthly growth table by referring only to the starting amount (in column F) and the number of monthly intervals that have passed (found in row 1). Then you are not reliant upon explicitly calculating the value for the preceding month. Just drag this revised formula throughout the monthly growth table.

 

[EthanP]

Okay, awesome!! Thank you!