Looping a macro until the formula within the macro results in an acceptable output

[TheWanderer]

Hi All,

I would appreciate some help with the below. So I created a code where all it does is that it takes the base value (a constant) and multiplies it by the multiplier column at each tenor point and that give me a price ("Formula Price"). That simple calc is within a code. After the calcs are done I compare the results with The Market Price column. The difference is the "Error" between the Formula and Market Price.
What I now need to do is to add a code into my existing one, where it will be smart enough to know that since there is an error larger than +-0.5 it has to move the multipliers, for each point, so that it minimises the difference up until it is within that threshold of +-0.5. It has to happened per each point in the tenor. So first it has to minimise the error for 1m then 3m etc. Obviously I am happy for you to change my original code which sucks anyway for the basic calc I have done (which multiplies the 1575 * factor input).

I hope that makes sense. Do not ask me to use solver or any other non vba solution please as the real life formula is more complicated and non linear than that so I just want to know how I can loop the existing simple formula to run until it minimises errors.

So for example the first point has a difference of -39.755 so maybe if someone can help with a code where it bumps or reduces (depending on the signage of the error) the multiplier by a small amount (steps) until it minimises the error? thanks a lot

Tenor	Constant Input to formula	Input to Formula (to iterate until difference between Formula Price - Market price is with acceptable value	Formula Price	Market Price	Error
1m	1575	1.025	1614.375	1575	-39.375
3m	1575	1.05	1653.75	1640	-13.75
5x2	1575	1.075	1693.125	1700	6.875
5x5	1575	1.1	1732.5	1750	17.5
10x5	1575	1.125	1771.875	1790	18.125
10x10	1575	1.15	1811.25	1800	-11.25
20x10	1575	1.175	1850.625	1840	-10.625

[SamT]

I hope that makes sense

Sorry to dash your hopes, but no, it doesn't.

What formula? What code? What is 5x2, 5x10, et al? Which is the Multiplier column? Which is the multiplicand column?

However,
ExactMultiplier = Round(Multiplier + (Error / Multiplier))