Formula Error - Don't Know What the Error Is

YahooGoogle

New Member
Joined
Nov 7, 2017
Messages
14
Office Version
  1. 2007
I found some information/formulas on a website and I tried to transfer the information to my own spreadsheet so that I could play around with other stock/exercise prices and dates. I am trying to get the formulas listed below for D1 (cell B12), Call Price (B16), and Put Price (B17) to work out to the values provided as the "answers" but these formulas give back an error message, but do not specify what the error is. 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)) - Formula Provided (did not work)
B13 D2 0.4198
B14 N(D1) 0.7248
B15 N(D2) 0.6627

B16 Call Price 64.99 (B3\EXP(-B8\B7))\B15-B4\EXP(-B6\B7)\B15 - Formula Provided (did not work)

B17 Put Price 17.99 (B17-(B3\EXP(-B8\B7))+B4\EXP(-B6\B7) - Formula Provided (did not work)


Can someone please tell me why I am getting the error messages for these three formulas and if they were able to get them to work so the formulas calculate the values listed as the answers?
Thank you in advance
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
First, you have not bona fide Excel formula for us to look at. There is no "=" at the beginning. I don't know if that is part of the problem, or if you are simply misrepresenting the facts.

Second, if these are Excel formulas, backslash ("\") is not a valid Excel operator. It is a VBA operator.

Finally, if this is indeed VBA code, B3, B8, B7, etc are not valid cell references. They might be VBA variables. But you have not provided sufficient context for us to make that conclusion.

Bottom line: Do a better job of presenting the context. For example, I have no idea what B14 N(D1) 0.7248 is intended to represent.

For Excel, use thee XL2BB feature to copy-and-paste Excel into your posting.

For VBA, use the "vba" code tag on the toolbar to surround a complete (or sufficient) VBA code.
 

YahooGoogle

New Member
Joined
Nov 7, 2017
Messages
14
Office Version
  1. 2007
Thank you for the reply. To start, I mistakenly omitted the "=" at the beginning of the formulas, my apologies. Secondly, I myself do not know what exactly D1, D2, N(D1), and N(D2) are supposed to represent. I am simply interested in the Call Price (B16) and Put Price (B17) values, but in order to get these two values it would seem I require the other values as well. My Excel knowledge is unfortunately limited so I am not familiar with VBA operators & code. If it will help to understand the context of where this information comes from, here is the website where I pulled the info:

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

The information I am referencing is located about a quarter of the way down the page, under the "Setting Up the BSM Model in Excel" heading
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,018
Office Version
  1. 2010
Platform
  1. Windows
here is the website where I pulled the info:
toptal.com/finance/financial-analysts/option-pricing

Yes, that helps. And the mistakes are not yours. The webpage is a poor presentation of the Excel design. It contains some typos, to add to the confusion. And apparently, "\" is the multiplication operator in his version of Excel; or more likely, he substituted "\" for the usual "*" for some reason.

I hope you can understand the following presentation.

Book1
AB
2Valuation Date6/30/2018
3Stock Price (S)$550.00
4Strike Price (X)$500.00
5Volatility (s)25.00%
6Risk-free Rate1.00%
7Time to expiration (T)0.504
8Dividend Yield2.00%
9#Options (000)1,200
10Expiry12/31/2018
11Annual Dividend11.00
12
13D10.59730
14D20.41980
15N(D1)0.72485
16N(D2)0.66269
17Call Price$64.99
18Put Price$17.99
Sheet1

I cleaned up some of the author's annoying habit of entering unnecessary parentheses. For example, "(B5)" is never necessary nor useful. Simply write "B5".

You can copy the above into Excel by clicking the "copy" icon under "f(x)" and above "2" (row number).

Alternatively, you can copy-and-paste the following formulas into the cells that are indicated on the left.

Rich (BB code):
B7:  =(B10-B2)/365
B8:  =B11/B3
B13: =(LN(B3*EXP(-B8*B7)/B4) + (B6 + (B5^2)/2)*B7) / (B5*SQRT(B7))
B14: =B13-B5*SQRT(B7)
B15: =NORMSDIST(B13)
B16: =NORMSDIST(B14)
B17: =B3*EXP(-B8*B7)*B15 - B4*EXP(-B6*B7)*B16
B18: =B17 - B3*EXP(-B8*B7) + B4*EXP(-B6*B7)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,126
Messages
5,628,858
Members
416,345
Latest member
sayad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top