Problem translating math into excel formula (correct picture)

[Bramboos]

Hi,

I have a problem translating this excel formula (actually the combination of two) into excel spreadsheet. Probably I do something wrong regarding the syntaxis for the natural log (ln()), as my math skills are a bit rusty...

Does anybody have a clue how the excel-formula would look like (V0 = 100; sigma = 0.4; q = 0%; T = 1)?

 

[Eric W]

Try:

	A	B	C	D	E	F
V0	vvT (v root T)
s (sigma)	DLOM
q
T

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.227852[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]18.02388[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SQRT(B2^2*B4+LN(2*(EXP(B4*B2^2)-B2^2*B4-1))-2*LN(EXP(B4*B2^2)-1))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=B1*EXP(-B3*B4)*(NORM.DIST(E1,0,1,TRUE)-NORM.DIST(-E1,0,1,TRUE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


This should be correct, but without context or experience with this formula, I can't really vouch for its accuracy. Also, I used the standard normal distribution, which I think should be correct, depending on how the formula was constructed, but I'm not sure of that either.

Good luck.

 

[Bramboos]

Hi Eric,

Thanks a lot! I noticed that the outcome was slightly off from expectations, which was due to the fact that the formula in E2 needs to be adjusted a little (the E1 reference (both) should be devided by 2), to give the correct outcome, now it works perfectly! Thanks for your help, wouldn't have resolved this without it!

Kind regards,

Bram

 

[Eric W]

D'oh!
How could I have missed that?! Well, I'm glad you caught it, and glad it works for you now.