This was a great video Carita! Thanks a lot! You earned a sub. And you definitely deserve a lot more subs. Sure your channel will pick up one day. Wish you the best!!!
Good day, I too am not able to access this EXCEL link. I am only getting 'read only' access, I can't change anything within the binomial tree itself. Cheers.
Hi - thank you for reaching out. In order to use the sheet you have to save a copy on your computer (and you probably have to activate the editing - usually easy to see at the top of the sheet). The cells open for input are: Stock price, Volatility given, Total lifetime in years, Number of periods, Strike price and Risk free rate. The other cells are protected in order to preserve the formulas and thus keep the sheet functioning. I hope, this will work for you - I have had it tested by some of my friends and they could use the sheet as here described.
Hi again - one more thing: The cells are only protected in the first sheet with the "standard" binomial tree. The other sheets (dividend, currency and futures) are completely open for you to alter as you wish 🙂
Hi Carita, thank you for the wonderful video. I downloaded the worksheet and try to change the type of option in cell I3J3 but the excel returns "Name?". Looking into the cells highlighted in green between row 18 and 39, the equations are referred to cell I3, which should be correct. I also get the same "Name?" in the green cells when I change the editable cells like stock price. Am I doing something wrong?
Hi Timothy 🙂Glad, you like it 🙂 I just tested the sheet and it works as intended. I cannot say why you get this error. Did you download a copy or did you just use it online? My best guess is that downloading a copy to your own computer having Excel software will fix the problem. Kind regards, Carita
Hi Carita, Very much appreciate the free download. I had a question regarding binomial modeling for futures pricing on a contract that is the monthly average of the daily index... For example, we are 9 days into the month of March today, March 13th (21 business days), and the index so far is $100; if I know the historical volatility, is there a way to model with the binomial tree what the index will come out at the rest of the month? If there is already research done on this and you could point me to the right source I'd appreciate it.
Hi Taylor I cannot say that I have any particular knowledge regarding your question nor am I able to point to any research regarding the topic. However, my thoughts are that you would need a binomial tree for each time period (each business day) since you need an average of the index. Hence placing the index at time 0 and use periods of 2, of 3, of 4 up to 21 business days and after that calculate a simple average of the prices from the 21 binomial trees. I cannot say if this rather simple idea is the best way to go - but it's the only soution that I can see without using overlapping formulas in Excel - which of course doesn't work. Yo can easily drag the formulas in my binomial trees to extend the periods up to whatever you need. Kind regards Carita
Thank you for the video. I seem to be getting wrong answers, the correct answer is $15.13, but the model does not show this. My uni question is: Determine the price of a two-year AMERICAN put option with a strike price of $52 on a stock that is currently priced at $48.78 which has a volatility of 46.5%pa if the risk free rate was 3.2%. After the first year the stock pays a dividend of $2.15 and after the second year the stock is expected to pay a dividend of $2.32. What is the price of this put option?
Hi 😊Given the data you supply and assuming that the tree has two steps dyring the two years, the price for an American put is 14,2994 in my model. This is exactly the same you get using DerivaGem in the book "Options, Futures, and Other Derivatives" by John Hull. Therefor I cannot see that the correct answer would be 15,13 unless of course there is a typo in some of the given data or that the number of periods (not stated) is different from 2. I have tested with a various different number of periods and neither of my tests result in an option price of 15,13. I hope, you find out - I would be interested to hear you solution ☺
Hello, Thanks for this educative video, after I inserted all the necessary stuff, the up-movement, down-movement, and probability changed to #NAME? without touching it, Please any idea of what may go wrong? Response needed urgently
Hello, My best guess is, that the formula isn’t working due to the way, you typed in the information. Perhaps you used a dot (.) instead of a comma (,)? My advice would be to download the sheet again and observe how I’ve entered the information and look for any mismatches in how you entered the information. The upmovement, the downmovement and the probability are all calculated using Excel formulas - and as I’m sure you are very well aware of, they only function if they can recognize the numbers and signs in the entered data which the formulas are using. Hope it works out for you 🙂
hi ! first of all i want to thank you for this great job! i want to ask you for a favor, you built a 10 period model, and in my assignment i have 15 period . would you please help me for getting a 15 one please! i'm struggling
Hi and thank you 😊. The formulas in Excel are made, so you easily can drag the cell further out, hence expanding to 15 periods. Of course you have to insert some empty rows first in order to have the needed amount of rows. If this does not help you, I recommend to study the formulas in the cells of the 9th and 10th period, see how the develope and add the 11th up to 15th period in a similar manner. Kind regards, Carita
Hi..Thank you very much for sharing this. I hope you will get many likes because you help us a lot. However, I cannot change the types of option etc from your excel, so I could not try it. I have tried to download or export it in excel but I cannot find the menu. Could you give me some enlighment? Thank you.
Hi..Thanks a lot for your appreciative comment 😊. My first thought is whether you have activated the sheet after download - you probably did that already. However, if you for some reason cannot choose the option type in the scroll down menu, it’s really not a problem - you just scroll down the sheet itself instead and all 4 possible types of options are shown below. The feature of choosing option type in the menu was just to make it more user friendly as you would always have your option presented in the above tree of the page. Hope you will be able to use it. Kind regards, Carita
Hi again - I replaced the link and I hope it will work for the download now. I will be so grateful, if you will let me know whether it is working or not.
Hi - it's the formula to discount or compound the interest over the periods of time. Microsoft explaines the formula like this: "This article describes the formula syntax and usage of the EKSP function in Microsoft Excel. Description Returns e raised to a power of number. The constant e is equal to 2.71828182845904, which is the base of the natural logarithm." Kind regards, Carita
This was a great video Carita! Thanks a lot! You earned a sub. And you definitely deserve a lot more subs. Sure your channel will pick up one day. Wish you the best!!!
Thank you so much 😊
Good day, I too am not able to access this EXCEL link. I am only getting 'read only' access, I can't change anything within the binomial tree itself. Cheers.
Hi - thank you for reaching out. In order to use the sheet you have to save a copy on your computer (and you probably have to activate the editing - usually easy to see at the top of the sheet). The cells open for input are: Stock price, Volatility given, Total lifetime in years, Number of periods, Strike price and Risk free rate. The other cells are protected in order to preserve the formulas and thus keep the sheet functioning. I hope, this will work for you - I have had it tested by some of my friends and they could use the sheet as here described.
Hi again - one more thing: The cells are only protected in the first sheet with the "standard" binomial tree. The other sheets (dividend, currency and futures) are completely open for you to alter as you wish 🙂
Hi Carita, thank you for the wonderful video. I downloaded the worksheet and try to change the type of option in cell I3J3 but the excel returns "Name?". Looking into the cells highlighted in green between row 18 and 39, the equations are referred to cell I3, which should be correct. I also get the same "Name?" in the green cells when I change the editable cells like stock price. Am I doing something wrong?
Hi Timothy 🙂Glad, you like it 🙂 I just tested the sheet and it works as intended. I cannot say why you get this error. Did you download a copy or did you just use it online? My best guess is that downloading a copy to your own computer having Excel software will fix the problem. Kind regards, Carita
goated channel, thanks a bunch for the informative content and the sheet download :)
Thx a lot😊
Hi Carita, Very much appreciate the free download.
I had a question regarding binomial modeling for futures pricing on a contract that is the monthly average of the daily index... For example, we are 9 days into the month of March today, March 13th (21 business days), and the index so far is $100; if I know the historical volatility, is there a way to model with the binomial tree what the index will come out at the rest of the month? If there is already research done on this and you could point me to the right source I'd appreciate it.
Hi Taylor
I cannot say that I have any particular knowledge regarding your question nor am I able to point to any research regarding the topic.
However, my thoughts are that you would need a binomial tree for each time period (each business day) since you need an average of the index. Hence placing the index at time 0 and use periods of 2, of 3, of 4 up to 21 business days and after that calculate a simple average of the prices from the 21 binomial trees. I cannot say if this rather simple idea is the best way to go - but it's the only soution that I can see without using overlapping formulas in Excel - which of course doesn't work.
Yo can easily drag the formulas in my binomial trees to extend the periods up to whatever you need.
Kind regards
Carita
Thank you for the video. I seem to be getting wrong answers, the correct answer is $15.13, but the model does not show this. My uni question is: Determine the price of a two-year AMERICAN put option
with a strike price of $52 on a stock that is currently priced
at $48.78 which has a volatility of 46.5%pa if the risk free
rate was 3.2%. After the first year the stock pays a
dividend of $2.15 and after the second year the stock is
expected to pay a dividend of $2.32.
What is the price of this put option?
Hi 😊Given the data you supply and assuming that the tree has two steps dyring the two years, the price for an American put is 14,2994 in my model. This is exactly the same you get using DerivaGem in the book "Options, Futures, and Other Derivatives" by John Hull. Therefor I cannot see that the correct answer would be 15,13 unless of course there is a typo in some of the given data or that the number of periods (not stated) is different from 2. I have tested with a various different number of periods and neither of my tests result in an option price of 15,13.
I hope, you find out - I would be interested to hear you solution ☺
Hello, Thanks for this educative video, after I inserted all the necessary stuff, the up-movement, down-movement, and probability changed to #NAME? without touching it, Please any idea of what may go wrong? Response needed urgently
Hello, My best guess is, that the formula isn’t working due to the way, you typed in the information. Perhaps you used a dot (.) instead of a comma (,)? My advice would be to download the sheet again and observe how I’ve entered the information and look for any mismatches in how you entered the information. The upmovement, the downmovement and the probability are all calculated using Excel formulas - and as I’m sure you are very well aware of, they only function if they can recognize the numbers and signs in the entered data which the formulas are using. Hope it works out for you 🙂
hi ! first of all i want to thank you for this great job! i want to ask you for a favor, you built a 10 period model, and in my assignment i have 15 period . would you please help me for getting a 15 one please! i'm struggling
Hi and thank you 😊. The formulas in Excel are made, so you easily can drag the cell further out, hence expanding to 15 periods. Of course you have to insert some empty rows first in order to have the needed amount of rows. If this does not help you, I recommend to study the formulas in the cells of the 9th and 10th period, see how the develope and add the 11th up to 15th period in a similar manner. Kind regards, Carita
Thank you you are really kind and a game changer :)
Thx 😊
Hi..Thank you very much for sharing this. I hope you will get many likes because you help us a lot. However, I cannot change the types of option etc from your excel, so I could not try it. I have tried to download or export it in excel but I cannot find the menu. Could you give me some enlighment? Thank you.
Hi..Thanks a lot for your appreciative comment 😊. My first thought is whether you have activated the sheet after download - you probably did that already. However, if you for some reason cannot choose the option type in the scroll down menu, it’s really not a problem - you just scroll down the sheet itself instead and all 4 possible types of options are shown below. The feature of choosing option type in the menu was just to make it more user friendly as you would always have your option presented in the above tree of the page. Hope you will be able to use it.
Kind regards, Carita
Hello! thank you for this video. Unfortunately the link isnt working for the sheet download
Hi Lana. Thank you for your message - I’ll look into it and fix it. It was kind of you to let me know. Kind regards, Carita
Hi again - I replaced the link and I hope it will work for the download now. I will be so grateful, if you will let me know whether it is working or not.
may i ask what’s the eksp
Hi - it's the formula to discount or compound the interest over the periods of time.
Microsoft explaines the formula like this:
"This article describes the formula syntax and usage of the EKSP function in Microsoft Excel.
Description
Returns e raised to a power of number. The constant e is equal to 2.71828182845904, which is the base of the natural logarithm."
Kind regards, Carita