Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Pricing Formula
Does anyone have a pricing formula for stock options?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Pricing Formula
Hello
Well, I guess the normal strategy to follow there in the newsgroups, is that you browse the internet for the correct formula. If you're having trouble implementing it in an Excel sheet, then we 'd be happy to help you out if we can. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "rook" wrote: Does anyone have a pricing formula for stock options? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Option Pricing Formula
I have a Black Sholes Option Pricing Model. You can google for this.
You can try this on for size. Black-Scholes in Visual Basic By Espen Gaarder Haug Visual Basic: easy to program but quite slow! '// The Black and Scholes (1973) Stock option formula: Public Function BlackScholes(CallPutFlag As String, S As Double, X _ As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) If CallPutFlag = "c" Then BlackScholes = S * CND(d1) - X * Exp(-r * T) * CND(d2) ElseIf CallPutFlag = "p" Then BlackScholes = X * Exp(-r * T) * CND(-d2) - S * CND(-d1) End If End Function '// The cumulative normal distribution function Public Function CND(X As Double) As Double Dim L As Double, K As Double Const a1 = 0.31938153: Const a2 = -0.356563782: Const a3 = 1.781477937: Const a4 = -1.821255978: Const a5 = 1.330274429 L = Abs(X) K = 1 / (1 + 0.2316419 * L) CND = 1 - 1 / Sqr(2 * Application.Pi()) * Exp(-L ^ 2 / 2) * (a1 * K + a2 * K ^ 2 + a3 * K ^ 3 + a4 * K ^ 4 + a5 * K ^ 5) If X < 0 Then CND = 1 - CND End If End Function "rook" wrote: Does anyone have a pricing formula for stock options? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pricing | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Old to New Pricing | Excel Worksheet Functions | |||
spreadsheet for sundries with option for better and best pricing | Excel Discussion (Misc queries) | |||
Formula to extract pricing from a chart | Excel Worksheet Functions |