TheWanderer
01-24-2016, 11:27 AM
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
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