nonlinear regression with constraints

[kanbozlee53]

Dear All,


I am trying to estimate parameters (b1, b2) of the model below

Y= timedummy* industrydummy*( exp({-b1}*lag))*(1-exp(-{b2}*lag)))


with a constraint so that sum of ( exp({-b1}*lag))*(1-exp(-{b2}*lag))) over lag (1,2,3,4, ....35) is unity in the model;

Any help any insights will be greatly appreciated
thanks in advance

Ibrahim

[adyteo]

you need a dedicated software package for that. you can try Palisade @ Risk Pro. They may have a trial version. it is an Excel Add-on.

[MrShorty]

It seems like you should be able to set this up so Solver can solve it.

1) Set up a block of cells to calculate your objective function. Assuming you want least squares, this would be sum((ymeas-ymodel)^2) or other objective function.
2) Set up a block of cells to calculate your constraint sum(f(lag)*g(lag))
3) Call Solver and tell it to "set target cell" with your objective function "to a minimum" "by changing" b1 and b2 "subject to the constraint" that constraint cell =1.

Assuming the problem is suited to a Newton-Raphson type algorithm and you give it reasonable starting values, it should converge on a solution.

[kanbozlee53]

thanks a lot Adyteo, Mrshorty for your replies, I really appreciate it
Mrshorty, It has been a while I have not been using Excel Solver, I use Stata but there is no way to do it there , thus I decided to look for a solution in Excel. I'll try to figure it and will let you know
thanks again

[MrShorty]

Yes, let us know.

I personally don't use stata, but I was a little surprised to see you say that stata cannot do non-linear regressions. It will be up to you to decide what stata really can and cannot do, but this page claims that stata can handle non-linear least squares: http://www.stata.com/features/overvi...ar-regression/

I really do not know which platform will be easier to program to do what you want, but I expect that either application can do it.

[kanbozlee53]

MrShorty, the part you cannot do in stata is not nonlinear regression , actually I have already estimated Y=timedummy*industrydummy*(exp(-b1*lag))*(1-exp(-b2*lag),
however I could not put constraint that summing (exp(-b1*lag))*(1-exp(-b2*lag) over lag (1,2,3....35) will be equal to unity. Already contacted to people in stata and asked, too. They said currently they don't have that feature

[MrShorty]

Ah -- I see. With a good spreadsheet and good initial guesses, I would expect Excel's Solver to be able to solve this, so let us know how it turns out.

[kanbozlee53]

MrShorty, thanks very much, it did estimate the parameters!! however I do not how can I get estimates for time/industry dummies, do I need to include them in objective function one by one , I mean objective function will be then;
Y= (timedummy1*bt1)* (industrydummy1*bi1)*( exp({-b1}*lag))*(1-exp(-{b2}*lag)))+ (timedummy2*bt2)* (industrydummy2*bi2)*( exp({-b1}*lag))*(1-exp(-{b2}*lag)))+..............
industry=1,2,3,...6
time=1,...30

do you think is there a easy way for it?
another thing is that not all of the constraints are satisfied, is it normal?

Thanks again

[MrShorty]

If I count correctly, that means you have 38 adjustable parameters in this regression? I certainly can't claim to be an expert in all fields, but it doesn't seem like there are very many situations where one would want 38 adjustable parameters. Part of me wants to say that there is no such thing as "easy" when we are talking about that many parameters.

If you want all of those parameters in the equation, then yes, they each have to figure into the objective function somehow. It should just be a matter of editing your ycalc functions to incorporate the desired changes to the function.

What constraints are you specifying, and which constraints are not satisfied? It is "normal" for Solver's algorithms to find a wrong solution or a less than optimal solution. Sometimes this is a question of precision (The specified constraint is exactly 1, but Solver's chosen values come to 0.997) and Solver reports that this does not satisfy the constraint. Other times it is because it is mathematically impossible to satisfy the target and the constraint. Other times it is due to a poor choice in "initial guesses" or "seed" values. Still other times it is because the spreadsheet/Solver model are poorly designed. Solver cannot usually determine what is at play when it reports that constraints are not satisfied, so it is up to us as programmers to understand our problem well enough to "debug" this kind of thing.

I might suggest that you upload a sample spreadsheet if you want more specific help with this. Without it, we can talk in generalities -- it is hard to talk about specifics.

[kanbozlee53]

is it okay that I send you the file in private email or message?

[MrShorty]

In general, I think it is best if you can post the file publicly. If the file contains data that cannot be publicly published, then I'm not sure I want any responsibility for it. If the data is confidential, I'm sure you can make up some "dummy" data that will illustrate the mathematical problem without using any confidential data.

Also, sent privately, only I can see it, and there are others on here who might have better insights into the specifics of your problem.

[kanbozlee53]

I could not upload .it seems like it does not accept attachments larger than 1mb

[kanbozlee53]

here it is, a smaller version

[MrShorty]

Can you explain exactly where you are having problems? There isn't a Solver model saved with the spreadsheet, so I cannot be sure what you are using for your OF and your constraint function and your parameters. I can see one cell (R5C56) labelled "SSE" which looks like it is the sum of the squared residuals, so I expect that will be your "target cell" in Solver. B11 through B26 look like they might be your "by changing cells". "function" (column 50) and "objective2" (column 51) appear to be equal. I cannot tell which column should sum up to 1 for the constraint.

[kanbozlee53]

sorry about that, let me explain,
1-objective function is OBJECTIVE2 in AY
2-as you said cell under SEE is sum of squared errors in column AZ
3-parameters are in BE4:BQ4
4-constraints requires that sum of Fk(L) (Function in AX) over L=1,2,3,...,35 to be equal to one
constraints are like this
exp(-b11*1)*(1-exp(-b21))+exp(-b11*2)*(1-exp(-b21))+........+exp(-b11*35)*(1-exp(-b21))=1
:
:
:
exp(-b16*1)*(1-exp(-b26))+exp(-b16*2)*(1-exp(-b26))+........+exp(-b16*35)*(1-exp(-b26))=1

I calculated those sums in columns BG-BL in cells from 19 to 54 (6 class and 35 lags)
sum if function there sums values in function(column AX) for every class over 35 lags

5- I uploaded newer version with solver values entered

6-my question is
how can I modify objective function so that I can have estimates for
class dummies (1,2,3,...6) in AM-AR
cited years 1963-1999 in E-L (grouped in 8 cohorts)
citing years 1975-1999 in M-AK

thanks a lot for all the help

[MrShorty]

There's a lot there, it gets kind of hard to follow what you are trying to do in this spreadsheet.

Without going through everything, adyteo might have been correct from the beginning - basic Solver may not be able to handle this problem. According to Frontline (the programmers of the Solver add-in), the standard Solver package can only handle 100 constraint cells (Excel help seems to claim a higher limit on constraints). http://www.solver.com/standard-excel...mits-continued If I counted correctly, your model call for 6x30+ (~200) constraint cells that add up to 1.

I'm not sure what you mean by "modifying the objective function to estimate" those additional variables. Do you mean you want to add them to the "by changing cells" so Solver also uses those in its attempts to minimize SSE? If so, you will almost certainly bump up against the maximum 200 adjustable cells limit in Solver.

At this point, I am wondering if you are up against the built in Solver's programming limits. There are add-ins out there (Frontline sells one, as you can see on their website) that use the same algorithms, but with more robust programming limits.

[kanbozlee53]

I downloaded frontline add in trial version, it understood the model and constraints . however solver could not find a feasible solution and created a problem that excel shut downs whenever I click to a cell

[MrShorty]

I have no experience with Frontline's other products, so I don't know why it would cause Excel to shut down.

As for it not finding a feasible solution, there are several possible reasons for this, as I explained in part in post 9. When I am developing this kind of spreadsheet, I try to make sure that the specific problem I am using during development has a solution (and that I know what that solution is). That allows me to test different initial guesses and different variations of the model to get an idea of when the algorithm fails and when it succeeds.