How to solve this problem for Financial Options and Applications in Corporate Finance using EXCEL? Inputs: P=$40 X=$35 t= 6 months (0.5 years) rRF = 8.0 % = 0.080 Variance = 31.557% = 0.31557 How to do the above problem using Excell?
'
Question
How to solve this problem for Financial Options and Applications in Corporate Finance using EXCEL? Inputs: P=$40 X=$35 t= 6 months (0.5 years) rRF = 8.0 % = 0.080 Variance = 31.557% = 0.31557 How to do the above problem using Excell?
Solution
From the question it is induced that the value of call and value of put has asked and how calculated using excel sheet.
I am considering Black Scholes model to calculate value and it as follows:
I only changed variance to standard deviation and standard deviation = sqrt of variance.
You can downloard excel sheet or you can use the formula in excel for calculating value of option using Blackscoles model or any other model as you like appropriate.
Input Data 


Stock Price now (P) 

40 
Exercise Price of Option (EX) 

35 
Number of periods to Exercise in years (t) 

0.5 
Compounded RiskFree Interest Rate (rf) 

8.00% 
Standard Deviation (annualized s) 

56.18% 






Output Data 


Present Value of Exercise Price (PV(EX)) 

33.6276 
s*t^.5 

0.3973 
d1 

0.6355 
d2 

0.2382 
Delta N(d1) Normal Cumulative Density Function 

0.7374 
Bank Loan N(d2)*PV(EX) 

19.9795 



Value of Call 

9.5179 
Value of Put 

3.1455 
'