Formula Error - Don't Know What the Error Is

[Options78]

Can someone please tell me what is wrong with this formula based on the information below it? I copied it exactly as was posted online and got an error message (with no explanation as to what the error is). This formula is supposed to figure out one of the "Greek" values for calculating call/put option prices and the answer to this particular formula is supposed to be 0.59730.

=(LN((B3\EXP(-B8\B7))/B4)+((B6+((B5)^2)/2)\B7))/((B5)\SQRT(B7))

B2 -30-Jun-18
B3 - 550
B4 - 500
B5 - 25%
B6 - 1%
B7 - 0.504
B8 - 2%
B9 - 1200
B10 -31-Dec-18
B11 - 11


Thanks in advance

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. Normally I’d do this for you, as you are new, but I have no clue what you are trying to do - sorry.)

[Options78]

Hi Ali,

Thanks for the heads up and my apologies for the misstep. I was actually struggling with what to put in the header which is why I settled on the generic one I did. I will go back and try to make it more specific. I am trying to calculate Call/Put option prices and that formula I inquired about is supposed to help with that calculation by figuring out D1 value (on of the "greeks" associated with options).

Any suggestion on a title now that you know what I'm trying to do so that I don't waste any more of your time?

Thanks

[AliGW]

Sorry - I still don’t know what you are trying to do - it’s all Greek to me! What’s the ultimate aim? The answer to that question will be your thread title!

[Options78]

Hahaha....funny!

I found this specific formula online to calculate this "Greek" value based on the information I provided (Cells B2 - B11), and I already know the answer (0.59730). I copied the formula exactly as it was online so that I can arrive at the same answer myself and got an error message. I would just like to know what (if anything) is wrong with the formula itself and what changes I would have to make so that it returns a value of 0.59730 for me.

Does that help?

[Richard Buttrey]

The error is because in a fraction the numerator/denominator dividing line is a / Not a \

As for the result being the .59730 then it would help if you could confirm which are the Stock, & Exercise price, the Rate and Sigma

Don't expect us to guess what your numbers represent. And what makes you believe the answer is .59730 and not say .61587 that I get when I assume the first 4 numbers are the ones I mention above

[Options78]

Thank you for your reply. My apologies for the confusion, I'm new to this thread and didn't know how to properly articulate my question. I will be more mindful with future posts.

That being said, I got the answer of .59730 because I got this formula (and this answer) from a website. I was simply trying to transfer the information to my own spreadsheet (with the formulas that were provided) so that I could play with other stock & exercise prices. I tried switching all the \ to / as you suggested and I also got .61587 like you did so I am not sure where the other website got the .59730 value

Here is the complete information from the other website:

B2 Valuation Date 30-Jun-18
B3 Stock Price 550
B4 Strike Price 500
B5 Volatility 25%
B6 Risk-free Rate 1%
B7 Time to Expiration 0.504
B8 Dividend Yield 2%
B9 # of Options 1200
B10 Expiry 31-Dec-18
B11 Annual Dividend 11

B12 D1 0.5973 (LN((B3\EXP(-B8\B7))/B4)+((B6+((B5)^2)/2)\B7)) / ((B5)\SQRT(B7))
B13 D2 0.4198
B14 N(D1) 0.7248
B15 N(D2) 0.6627

Call Price 64.99 (B3\EXP(-B8\B7))\B15-B4\EXP(-B6\B7)\B16

Put Price 17.99 (B17-(B3\EXP(-B8\B7))+B4\EXP(-B6\B7)


I am trying to get the formulas listed above for D1 value, Call Price, and Put Price to work out to the values provided as the "answers" but these formulas give back an error message, and as already mentioned, switching the \ to / provides a different answer than what is provided.

[Rick Rothstein]

Any chance you can give us a link to the website where you got your formula?

[Options78]

Of course. The website link is:

toptal.com/finance/financial-analysts/option-pricing

The information I am referencing is located about a quarter of the way down the page

[Options78]

Adding to my last response - the information I am referencing is under the "Setting Up the BSM Model in Excel" heading

[protonLeah]

The \ is an integer division operator (in vba). Maybe you need to round or truncate the / quotient?

[Options78]

Thank you for your response. I must admit that sounds very advanced for my limited Excel knowledge. I wouldn't even know where to begin in terms of rounding or truncating the / quotient. Does that mean that the formula won't work as is?

[Rick Rothstein]

The \ is an integer division operator (in vba).

That is what I was thinking and that perhaps the OP had misread code for a formula... that is why I asked for the link. But visiting the link shows the author there definitely used a mixture of forward and backward slashes and that he definitely claims these to be Excel formulas. Unfortunately I am at a loss at this point. The only advice I can give the OP is to try to contact the author of the formula and ask him to clarify those backward slashes.

[bebo021999]

I believe that for some reasons of avoiding special character due to firewall, the author use "/" vs "\" to describe "divide" and "multiply" ( equal *)
So try to replace "\" with "*" in excel

[Options78]

That worked! I replaced every "\" with a "*" and the figures all lined up.
Thank you very much!

[Rick Rothstein]

I believe that for some reasons of avoiding special character due to firewall, the author use "/" vs "\" to describe "divide" and "multiply" ( equal *)
So try to replace "\" with "*" in excel

Great guess!!! Or was it a guess?

[bebo021999]

In fact I am not clever enough to fix it, have no idea how it works and where it comes from
But I try to google the formula then found out other samples with same same construction,
but there are "multiply" inside "*"
I guess there are reasons that the author "forced" to use back slash (\) instead of "*"

Capture.PNG