Please help me to start the subroutine

That looks a variant of some sort of statistical problem, regression variant perhaps.

I think you should be more specific about just what you are aiming for.

Such as, what do you mean by “model qx=Bc^x”? Model it to do what? Doesn’t that equation model it already?

As your problem stands, there’s no need to use VBA to maximize the summation expression you give. All you need to do is prespecify c at some suitable value, then make B as large as you like (unbounded) whereby you can thereby make the summation as large as you like.

Some more indication as to the origins, particularly statistical origins, of the problem would help a lot towards devising a solution, or outlining a suitable VBA approach, that would keep you happy.

Thank you very much for the quick reply.
Basically, I have the mortality data of 2000-2004, and I would like to apply graduation by using mathematical formulae.
One of the formula is qx=Bc^x, where B and c are unknown.
Which means, I would like to find the parameters, B and c such that it will fit the model the best.
I start off with maximum likelihood approach, but this is not going anywhere, is it s too complicated.
The textbook recommends to use a subroutine method, but I have no idea what it is.

OK.

It seems you want to start with qx=Bc^x where B and c are to be determined from a statistical procedure and x is and age (time varying) subscript. I think that’s like what’s been called a Spillman-Mitscherlich function

Using Excel terminology, I’d write it as q(x)=B * c^x.

Maximum likelihood isn’t that hard for this case, but I think a more standard approach would be to transform the equation into an easier to handle linear form and then use linear regression, which Excel is well equipped to do.

So if you take logarithms (natural, base 10, or any base you like) of both sides, your equation becomes

log(q)=log(B) + x*log(c)

Using Excel, you can list your known values for q down (say) columnA, list your known values for x down columnB and then use an Excel regression to obtain values for log(B) and log(c). These can then be transformed back to get B and c, i.e. the values you want.

For Excel regression, you can use the one in the add-on statistical analysis pak (if you have this) or use Excel’s LinEst function, either via VBA or directly from the Excel worksheet. If you’re not familiar with LinEst you can find out plenty about it from the Excel Help.

Putting together you own regression using the standard Excel tools isn’t that hard either.

If your ideas on logical purity bother you sufficiently that you feel the urge to get B and c directly rather than retransforming them from logarithnms, then you could use alternative statistical techniques such as Maximum Likelihood or Empirical Characteristic Function. But, as you point out, unless familiar with these you may find them more complicated.