I am trying to use the solver in Excel to create a linear program to minimize mutual fund expenses. My decision variables are the amounts invested in each fund. If anything is invested, it must meet the fund minimum. How can I program this?
Amount invested in Fund 1 >= if Amount Invested in Fund 1 > 0, then Amount Invested in Fund 1 >= 3000 else Amount Invested = 0
Any help is greatly appreciated. Thanks.
These types of constraints are enforced by a variation of the Big M Method
Suppose that x is the decision variable corresponding to the amount to invest in Fund 1. Pick an upper bound M on the possible values of x. For example, if you only have $1,000,000 to invest, let M = 1,000,000 You don't need to have the least upper bound on x. M = 2,000,000 would work as well (though you will generally get faster convergence and less round-off error if M isn't unreasonably large).
Introduce a new variable, y, which is constrained to be 0 or 1 (i.e. a binary decision variable). Add the following two constraints to your model:
x >= 3000*y
x <= M*y
If x>0 then the second added constraint forces y away from 0, hence it forces y = 1 since y is binary. But if y = 1, this second constraint reduces to x <= M, which is automatically true by choice of M, thus it doesn't add any genuine constraint on x. But -- since y = 1 in this case, the first constraint becomes x >= 3000. Thus the two constraints together force x >= 3000 as soon as x > 0. Importantly -- it does so without use of the nonlinear if function. It does make it an MILP (Mixed Integer Linear Programming) problem -- but Excel's solver can handle those with no problem (as long as the number of binary variables doesn't become too large).