power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Calculating the IRR

Happy Ending

On September 5 I finally executed the trade.  That also happened to be the date of the announcement of the company's clinical trial results.  I watched with pleasure over the next few days as the stock price fell from $19.75 (at the time I placed the trade) to $17.54.

Date Open High Low Close Volume
15-Sep-06 18.11 18.13 17.44 17.54 626,800
14-Sep-06 18.26 18.30 17.80 17.96 387,500
13-Sep-06 18.13 18.66 18.13 18.39 574,100
12-Sep-06 18.34 18.34 18.08 18.30 389,400
11-Sep-06 18.26 18.58 18.25 18.29 504,300
8-Sep-06 18.48 18.54 18.25 18.43 413,900
7-Sep-06 18.40 18.73 17.84 18.35 609,400
6-Sep-06 19.99 20.00 18.40 18.46 2,122,400
5-Sep-06 19.67 19.85 19.21 19.24 441,900

 

IRR Calculation

Thus my total profit was (1) the $0.85 per option sold, plus (2) the interest earned on my cash sitting in reserve in case needed for the protective buy order.  My money market account was paying about 4%.

I held $21.10 in reserve for each option sold at $0.85.  Thus my net investment was $20.25 per share.  My percent return on the option sale was (0.85 / 20.25), or 4.2% in ten days.  There are 36 ten-day periods in a year, so the annualized value of the 4.2% return was ((1.042)^36)-1), or 339%.  Add to that the 4% rate I was earning in money market, and my total annualized IRR was 343%. 

So now you know how the boastful numbers are calculated by the guys who promised to teach you how to earn 1000% return.

Next: Download the Spreadsheet -->

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.