...$A$2 is the Target Cell. (It is also the By Changing Cell.)
This should give you an error.
[....] These should be two different cells.
Fine. Either way, it appears that Solver has a defect or design flaw,
namely: (1) it fails to flag "misusage" when the Target and By
Changing cells are the same; and/or (2) it fails to notify the user
when the result is not a solution (i.e. it violates one or more
constraints).
This can't be correct because your constraint is that A1 >= B1 (ie 371)
A1 "can" be larger.
I don't know why you think that is not a solution. If A2 is less than
123573, I believe A1>=B1 remains true. If A2 is more than 123573, I
I may be wrong, but it appears you are trying to set Target Cell A1 "to a
value" of 371 by changing A2.
Well, I am trying to find the largest A2 that makes A1 __as_close_
to__ 371 (but no less) as possible. But yes, the ideal solution would
set A1 to 371 (i.e. same as B1), if that is possible. You might be
correct that that is always possible in this example. But I am not
sure it is always possible in the real-world problem from which the
example was distilled.
I see no point in parsing the details...
Because I can visualize the solution you provided better with..
Solve[0.03*(135940 - A2) = 371]
A2 -> 123573.33
Instead of:
[... my complex formulation ...]
As I have said repeatedly, the example is a __distillation__
(simplification) of a much more complex model, which I deemed too
complex to post here and an imposition to ask experts to duplicate and
experiment with. I retained the complexity so as not to change the
problem that I encountered with Solver.
My primary interest was in pointing out alleged Solver misbehavior,
not in trying to solve my problem differently.
But since you express some interest in the real problem (model) and
you believe that I have constructed the Solver solution incorrectly, I
would be happy if you offered some ideas to improve or correct the
model, as a way of educating me on the use of Solver. Normally, I
would start a new thread. But I seem to have your attention here ;-).
I want to find the largest amount of taxable income at which it is
advantageous to invest x dollars in taxable bonds instead of tax-free
bonds.
The solution is straight-forward if we assume that the marginal tax
rate (combined state and federal) is the same with and without the
taxable interest. That requires only simple algebra; I do not need
Solver for that.
However, I believe the solution is non-trivial when: (a) we cross one
or more tax brackets, depending on the amount of interest (which
depends on the amount invested -- "x"); and/or (b) we cross state and
federal tax brackets at different "times" -- that is, the size of the
state and federal tax brackets differ significantly. (In particular,
compare the California and federal tax rate schedules.)
So I constructed the following model. I hope I can explain this well
enough without actually providing the spreadsheet. If my explanation
is insufficient for discussion and you are interested in working with
the spreadsheet, contact me by email.
A1: amount invested (input data)
A2: interest earned on taxable bond (=A1*APY1)
B2: interest earned on non-taxable bond (=A1*APY2)
A3: after-tax taxable interest earned
B3: after-tax non-taxable interest earned (=B2)
A4: taxable income before adding taxable interest earned
The formula for after-tax taxable interest (A3) is complicated. It is
taxable interest (A2) minus the difference between: (a) total tax
after adding taxable interest (A2) to other taxable income (A4); and
(b) total tax on other taxable income alone (A4). Total tax can be
computed in a variety of ways. I chose to enter 2006 federal and
state marginal tax rate schedules for MFJ, then construct appropriate
expressions to perform the evaluation.
I believe the optimatization problem becomes: find the largest
taxable income (A4) at which adding taxable interest (A2) results in a
larger or equal after-tax earned interest (A3>=B3). I believe the
solution space is continous.
Since I am trying to find the largest taxable income (A4), it seems to
me that A4 is the Target Cell for Solver. But A4 is not a formula; it
is one number that is input to the formula in A3.
If I were do solve this by brute force as a "what if" model, I would
repeatedly change A4 in a methodical manner (binary search) until I
found the largest A4 that satisfied the constraint A3>=B3 (and
preferrably A3=B3). For that reason, I ass-u-me-d that A4 is also the
By Changing cell for Solver.
Of course, A3>=B3 was entered as a Constraint. And all cell
references entered into Solver were absolute (e.g. $A$4), not the
relative references that I wrote here for convenience.
If that set-up is incorrect for Solver, I would appreciate it if you
would explain what I should do instead to construct a tractable
problem for Solver.
When I put a number (say 10000) into A1 (investment) for the first
time, A4 was blank, and Solver found an appropriate solution.
When I subsequently put a different number (say 100000) into A1, A4
was no longer blank. As I recall, Solver did not find an appropriate
solution -- that is, one that obeys the constraint (A3>=B3). It was
only after I deleted the contents of A4 again that Solver found an
appropriate solution.
Note: I am writing that historical description from memory. The
order might have been reversed, and I might used different numbers for
A1 when I encountered the problem.