Activity Idea (with Spreadsheets): Let's Make An Index Fund

Jan 29, 2017
Index Funds, Activity, Behavioral Finance, Investing, Mutual Funds, Teaching Strategies, Math, Activities

I awoke this morning thinking “how can you make index funds more tangible for students?” Why do I care about this? Anyone who has heard my rantings before either in this blog or on the NGPF podcast knows that I abhor the Stock Market Game. It teaches all the wrong lessons about investing: the short term nature of it, the “luck” factor, the highest risk strategy wins and so on. At some point, I will create a game to counter these lessons that is focused on index funds. The trick is how to make it appealing to a risk-seeking teen audience who loves the “action” of buying and selling stocks. Unfortunately good investing isn’t really about “action”, my buddy Allan Roth has it right when he says, as investors we should “dare to be dull.”

So, here’s the kernel of the idea: Have students take on the role of an investment manager hired to do the following:

  • Create a TEEN index fund made up of  10 “teen” companies in the U.S.
  • You are excited about the potential to sell this investment to parents who want to get their kids interested in investing.
  • You have $1,000,000 to invest and need to know how many shares of each company to purchase to create this index.
  • You would like to create a market weighted  index, in other words, you will own more of the larger companies and less of the smaller companies.

Let’s break the assignment into manageable pieces:

  1. How to find the 10 “teen-based” companies?  I gave you a head start with this list of ten companies that your students should be familiar with.  I have created the spreadsheet with the novice in mind. The only data that the students will enter are in #2 below; the rest of the spreadsheet has formulas to automatically make the calculations.
  2. Next, go to Yahoo Finance and fill in the spreadsheet with the number of shares outstanding (that the company has issued) and the current stock price. Be sure that you check the units for the number of shares outstanding as it is in the MILLIONS of shares on the spreadsheet. Multiply these two numbers and you get the market capitalization of the company. When analysts talk about the largest companies they are typically referring to the market capitalization which is quite simply the total value of all the stock of the company.
  3. Next, we want to determine how important each company is to the overall index. Two step process to determine how important the company is:
    1. Determine the market capitalization of the entire index by adding up the market cap. for each of the companies.
    2. Figure out what percentage each of the companies is to the overall index by dividing each company’s market cap to the market cap of the ENTIRE index.
  4. Now that we know how important each company is, we have to take the $1,000,000 investment and split it up among the ten companies based on their importance. For example if Apple’s market cap is 15% of the TEEN index, then $150,000 of the $1,000,000 investment needs to be invested in Apple.
  5. Finally, we have to figure out how many shares of each stock we need to buy. For example, if $150,000 is going to be invested in Apple and Apple’s stock price is $100, then we would need to buy 1,500 shares of Apple ($150,000 divided by $100).
  6. Once you have done that for each company, you now have the stock orders you need to place to create your TEEN index fund. Congratulations!
  7. Now ask your students the following questions:
    1. Would they invest in this fund? Why or why not?
    2. Is it diversified?
    3. Should it even be called an index fund? How is it different from the S&P500?

The TEEN index fund isn’t exactly an example of a diversified fund. Why not? Well it only has 10 companies in the fund and it is primarily consumer based companies. What if you wanted to build a fund with 20 of the largest companies in the U.S. which could be more diversified. How would you do that? Here are the steps.

  1. Identify which 20 companies to include in the index (I created a second tab on the spreadsheet for your students to complete). Here’s a good source which provides market cap data on the top 20 companies in the US (number of shares that a company has issued multiplied by the stock price) and appears  to be updated frequently. 
  2. Now complete steps #2-#7 that you did for the Teen Index.

Have some fun with this and ask students which of the two index funds they would choose and check back every week and update the results to see how the value of the $1 million investment has changed.


Want to humble those intrepid stock pickers in your class? Check out this NGPF Activity: Think You Can Beat the Stock Market?

About the Author

Tim Ranzetta

Tim's saving habits started at seven when a neighbor with a broken hip gave him a dog walking job. Her recovery, which took almost a year, resulted in Tim getting to know the bank tellers quite well (and accumulating a savings account balance of over $300!). His recent entrepreneurial adventures have included driving a shredding truck, analyzing executive compensation packages for Fortune 500 companies and helping families make better college financing decisions. After volunteering in 2010 to create and teach a personal finance program at Eastside College Prep in East Palo Alto, Tim saw firsthand the impact of an engaging and activity-based curriculum, which inspired him to start a new non-profit, Next Gen Personal Finance.