ExtremeCPA - Technology for Business

Tuesday, January 17, 2006

Binomial Pricing Model for Options

Hi all! I hope you all had fantastic Holidays and a wonderful New Year. As I mentioned in my previous post, I'm going to try to be better about writing more starting now and moving forward. We'll see how it goes :) The title for this post is a little misleading. I recently read an article in the JoA about how to create a binomial pricing model for options in Excel. I've recently been thinking about how technology can influence the way you solve a particular problem; furthermore, I've been thinking about how uninformed I was (an still am) in regards to technology and you never really know how much you don't know.

In reality, I'm sure quite a few of us feel that we are quite well versed insofar as technology is concerned. I myself felt this way several years ago when I began to learn VBA and Excel. I found that I could do things that many other people could not simply because I could write VBA code. I then found myself turned on my head when I found out about Python (an open source scripting language). I realized that not only was I pretty clueless, but I would have never even realized how clueless I was if I was not introduced to the language in the first place. This turned me on to looking into other technology areas that further enhanced my skills. I am not, however, quite as naive as before and I realize that I still have a *long* way to go before I would consider myself well versed in technology.

You are probably wondering, why am I writing this now? Well, just to let you know that I think you should be learning more and more each and every day. Perhaps try to tackle a new project by using VBA if possible wherever you can. There are several fantastic tutorials about VBA at places like Mr. Excel, Beyond Technology and others. Just do a Google search for VBA tutorials (or something similar) and you'll find a ton of resources. You can always write and ask me if you have help as well. I started off by talking about a binomial pricing model for options in Excel, and I'll conclude by giving you an example of something you can create using VBA once you become fairly proficient. I've tried to document the code although I'm sure there will be questions if you start reading it. Not all of the functionality from the article is included in the model (specifically, it was too time consuming trying to figure out how to include the ability to calculate an early exercise if the option reaches a certain multiple using VBA), but much of the functionality is there. Feel free to try it out and give comments ... click here to get the model.

Again, this is simply in the hopes of inspiring some of you to learn more about technology. Good luck!


  • Hi Kevin,

    What simple formula can I use to figure out the share price of a start-up company. I would like to use the discounted rate cashflow model to figure out the cost per share for the IPO. If the cashflow at the end of the 1st 12 months is projected at $850,000, then what would it cost per share. Assuming the discounted rate is the prime rate, plus 4% (11.25%), and issuing 10,000 shares. Does this make sense?

    By Blogger Search Hippie, at 11:55 AM  

  • Well, it depends. If you want to assume even cash flows on a monthly basis, you could write:

    =PV(.1125/12, 12, 70,833.33)

    This indicates an 11.25% rate compounded monthly, for 12 mos, with cash flows = 850/12. If you assume a pymt at the end of 12 mos, you could write:

    =PV(.1125/12, 12, 0, 850000)

    This would imply 11.25% compounded monthly for 12 mos, with 1 final payout at 850k. If you don't want monthly compounding, simply take out the "/12" and change the 2nd number to 1 (from "12").

    Hope this helps!

    By Blogger Kevin T. Ryan, at 3:04 PM  

  • great post.


    By Blogger Corporate, at 8:51 PM  

  • Great Info I loved it.It is difficult to be CPA .One of the most important milestones in any accountant's career is to become a Certified Public Accountant. My question is, other than GAAP which is the other principle are implemented by companies.

    By Anonymous william bradley, at 5:55 AM  

  • I love your site. I am just getting into accounting and finance and trawling through the web trying to find great sites. Yours is definitely a great site. Thanks for the great discussion and interesting writings. I am trying to find some other sites similar to the introduction to financial ratios section at http://www.thefinanceowl.com . Can anybody recommend anything?
    Yours Maggie

    By Anonymous Anonymous, at 10:25 AM  

  • Blogs are so informative where we get lots of information on any topic. Nice job keep it up!!

    Communication Dissertation

    By Blogger marry, at 9:07 AM  

  • Great article! Keep up the good work. You might also find helpful our company's article on audit precautions:

    By Anonymous Will, at 2:43 PM  

  • This kind of information is very limited on internet. Nice to find the post related to my searching criteria. Your updated and informative post will be appreciated by blog loving people.

    Dissertation Format

    By Blogger Dissertation Writing service, at 6:04 AM  

  • The world of certified public accountants is certainly challenging. Personally I look at it as a necessary industry that is there to help businesses. You work your way up as an accountant to be CPA one day and get that extra boost of authority

    By Anonymous Provo CPA, at 4:20 PM  

  • Find the right business accounting relationship that could make a difference in helping take your business to the next level, if you’re an individual tax payer we’ll help you find that one accountant that you’ll want to keep forever.

    By Anonymous cpa corp, at 5:19 AM  

  • Yes, I am strongly agreed with you, please keep writing like this.

    By Anonymous advance technology tools, at 5:40 PM  

  • We learning every day. But i must admit it is very challenging to keep up with technology changes, buy the time to got to learn one software the new version is coming out which makes all different and you need to start all over again.

    By Anonymous Irit Hoffenberg, at 3:28 PM  

  • keep it going great article & totally agree with you and looking forward for your more update

    By Anonymous Neil Houghton, at 10:32 AM  

  • I read your article and got impressed due to have a positive ipression, many people would like to read about tech 4 technology because you have made an effort to write perfectly, please keep it up!

    By Anonymous Tech 4 Technology, at 10:34 AM  

Post a Comment

<< Home