An Excel Example of the Kelly Criterion in Portfolio Optimization

The Kelly criterion is a widely recognized formula used in portfolio optimization, gambling, and trading to determine the optimal size of a series of bets. This article provides a detailed guide on how to create your own Excel example of the Kelly criterion in portfolio optimization, as well as where to find existing examples in various online resources.

Understanding the Kelly Criterion

The Kelly fraction f* is calculated using the following formula:

[ f^* frac{bp - q}{b} ]

f* is the fraction of your bankroll to wager. b is the net odds received on the wager (i.e., the amount you win if you win, excluding your original stake). p is the probability of winning. q is the probability of losing, which is 1 - p.

Setting Up Your Excel Sheet

To create an Excel example, follow these steps:

Create a table to input the necessary data. Use the following columns: Column A: Asset Name Column B: Probability of Winning (p) Column C: Odds (b) Column D: Kelly Fraction Calculation Column E: Amount to Invest (Total Bankroll * Kelly Fraction) In the Column D: Kelly Fraction Calculation, use the Excel formula: B2*(C2-1)-B2/C2 To calculate the portfolio weights, multiply the Kelly fraction by your total bankroll.

Finding Existing Examples

Here are several resources where you can find existing examples of the Kelly criterion in portfolio optimization:

Online Financial Blogs

Investopedia - Explains the Kelly criterion with Excel examples. QuantInsti - Offers in-depth articles with practical examples.

YouTube Tutorials

Search for “Kelly Criterion Portfolio Optimization Excel” on YouTube for visual step-by-step guides. Key channels are ExcelIsFun and Tushar Srinivas.

Academic Papers

Google Scholar - Search for papers on portfolio optimization with the Kelly criterion. Keywords to use are “portfolio optimization Kelly criterion”, “trading strategy Kelly criterion”, etc.

Financial Modeling Courses

Coursera - Offers courses that include practical applications of the Kelly criterion. Udemy - You can find courses that include downloadable Excel files with practical examples.

GitHub Repositories

Search for repositories related to financial modeling or portfolio optimization. Many developers share their Excel models and scripts. Key search terms are “financial modeling Excel” and “portfolio optimization Excel cheat sheet”.

Additional Resources

Vince’s adaptation of the Kelly criterion into the optimal f* is commendable. His text is excellent and intuitive, making it easy to adapt his walk-through into Excel formulae with minimal effort.

Remember that the Kelly criterion has several intrinsic limitations when applied to markets:

Unknown future hit rate Unstable edge Unstable odds

Therefore, it is important to use the Kelly criterion as a theoretical framework and not as an absolute rule for real-world trading.

If you need help with a specific part of the Excel setup or a deeper explanation of how to apply the Kelly criterion in portfolio optimization, feel free to ask! By understanding the formula and properly setting up your sheet, you can make informed decisions in your portfolio optimization strategies.