3

I want to calculate "time to expiration" for call options by using the following loop. Note, optionbidprice is a number printed on the spreadsheet and is correctly read. Option price has to be recalculated for each new T, and thus also function0. Also functionderived is recalculated each iteration.

optionPrice = BSMOptPrice(opT, S, sigma, K, rf, q, T)

functionDerived = (-1) * ((S * Nd1accent * sigma * Exp(-q * T)) / (2 * Sqr(T))) + (q * S * ND1 * Exp(-q * T)) - (rf * K * Exp(-rf * T) * ND2)

Do Until function0 = 0.1       
    function0 = optionBidPrice - optionPrice        
    Tnext = T - (function0 / functionDerived)        
    T = Tnext        
Loop        

calculateOptionExpiration = Tnext

I am guessing that the 0.1 number is too unrealistic and that the loop can't find a good T value for this.

What do you think?

brettdj
  • 54,857
  • 16
  • 114
  • 177
Jolien .A
  • 173
  • 3
  • 18

2 Answers2

9

The value 0.1 can never be represented exactly using floating point numbers. You will need to test that the value is instead within a small range around 0.1. Note this is is true for any floating point equality comparison.

For an in-depth explanation, see Why 0.1 Does Not Exist In Floating-Point

Community
  • 1
  • 1
Eric J.
  • 147,927
  • 63
  • 340
  • 553
  • 1
    I agree with the sentiment, but I don't think that's the real problem. If I type `?.1=.1` in the Immediate Window, I get True and those are both floating point. It's just that the misrepresentation of the decimal happens to be equal. The problem is that any non-rounded calculation (floating point or otherwise) is very unlikely to exactly equal a rounded number. – Dick Kusleika Oct 20 '15 at 19:31
  • 1
    If you type ?.1 = .1, both the left-hand and right-hand operand are converted to a binary representation that is very close to, but not the same as, .1. However, they are both converted *to the same binary representation* so will be equal. On the other hand, your iterative formula is unlikely to ever produce left-hand and right-hand operands for the equality comparison that have exactly the same binary representation. – Eric J. Oct 20 '15 at 19:35
  • 1
    To @Eric J.'s point, try `? 0.1 = 0.3 - 0.2`. This returns False even though we know better. – xidgel Oct 20 '15 at 19:43
1

While what @EricJ. answered is valid, there are other ways to test for the 0.1 value directly... without the need to test for a small range around it.

One of these is to convert to the Decimal variant subtype, which does not suffer from the imprecision of the Double, Single, or Date types.

Consider this procedure. It will never finish because of the imprecision of the Double:

Sub Bad()
    Dim k As Double

    k = 0

    Do Until k = 0.1
        k = k + 0.001
        DoEvents
    Loop
    MsgBox k

End Sub

Now consider this, which finishes instantly:

Sub Good()
    Dim k As Variant ' <-- look here

    k = CDec(0)      ' <-- look here

    Do Until k = 0.1
        k = k + 0.001
        DoEvents
    Loop
    MsgBox k

End Sub

Answer for @Jolien.A question:

Define functionDerived as a variant and use CDec() on the expression to the right of the equal sign.

And this is an even better approach, though it would not work in OP's case:

Sub Better()
    Dim k As Double

    k = 0

    Do Until k = 0.1@ ' <-- look here
        k = k + 0.001
        DoEvents
    Loop
    MsgBox k

End Sub

This one is even quicker because the iterator (k) is kept as a Double. Notice the At-Sign (@) at the end of Do Until k = 0.1@ The At-Sign is the Decimal Type Character and using it as a suffix for a literal numeric value instructs the VBA compiler that the number is a Decimal, and not a Double (which is the default for fractional, literal numbers.

.

ADDITIONAL DETAILS

From MSDN: https://msdn.microsoft.com/en-us/library/xtba3z33.aspx

The Decimal Data Type:

  • Holds signed 128-bit (16-byte) values

  • It supports up to 29 significant digits

  • It is particularly suitable for calculations, such as financial, that require a large number of digits but cannot tolerate rounding errors.

  • largest value is +/-7.9228162514264337593543950335, and the smallest nonzero value is +/-0.0000000000000000000000000001 (+/-1E-28).

  • Decimal is not a floating-point data type. The Decimal structure holds a binary integer value, together with a sign bit and an integer scaling factor that specifies what portion of the value is a decimal fraction. Because of this, Decimal numbers have a more precise representation in memory than floating-point types (Single and Double).

  • The Decimal data type is the slowest of all the numeric types. You should weigh the importance of precision against performance before choosing a data type.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Valid point that decimal will not suffer from precision issues at these orders of magnitude. However, it is also a 14 byte data type that is not directly supported in hardware. For this calculation that will probably not matter, but in general be aware that working with decimal is slower. Note also that there are number ranges for decimal that suffer from the same issue, as there are only a finite number of bits to represent infinite numbers. – Eric J. Oct 20 '15 at 19:38
  • Yep, though googling `decimal vba data type`, Google highlights an answer claiming 14 bytes. That source is from 2004, so either a typo in an old source (14 bytes does seem a very unusual choice), or the precision changed at some point. – Eric J. Oct 20 '15 at 19:59