Sometimes the Simple Solution… : How to Think About Investing (Fun With Spreadsheets)

Sep 30, 2014
Personal Finance, Lesson Idea, Index Funds, Investing, Mutual Funds

Investing is hard to teach.  Talk to personal finance teachers and ask them what concept they find most challenging to teach to high school students and many will tell you it is investing.  How can you possibly cover a landscape that encompasses thousands of stocks, thousand of mutual funds, not to mention that other staple, bonds?  There are so many choices for you to teach (but also for investors to choose from), that it is easy to feel overwhelmed.  No surprise that the market for financial advisors is thriving in this sea of complexity.  Can’t we just teach our students “Don’t try this at home?”

But what if there was a simple solution?  One that didn’t require evaluating thousands of options but was elegant in its simplicity without sacrificing investment returns.  Having been an investor for over 25 years now and bought a few “can’t miss companies” and “mutual funds of the year” and “low priced stocks that couldn’t go lower,” I have personally experienced so many of the psychological misjudgments that afflict investors (check out this Charlie Munger speech laying them all out).  When I saw this article a year ago, I thought what a great way to teach our students about investing.

So, what’s an investor to do?  Think more simply and stay away from owning individual stocks (from WSJ article):

“With well over 8,000 mutual funds and exchange-traded funds at their fingertips, individual investors have never had more investment options from which to choose. But with all of that choice comes the potential for overload, and overwhelmed investors can make bad decisions or give in to impulses to chase returns from previously high-performing funds.

That is why some experts are pushing a back-to-basics approach to investing. As drastic as it may sound, they say it’s possible—even at times desirable—to construct a very well-diversified portfolio using just three low-cost mutual funds or ETFs.”

So, how can you get a diversified portfolio from just three mutual funds or ETFs?

“Many “Bogleheads”—a group of investors who favor index investing as inspired by Vanguard Group founder John Bogle —suggest a three-fund portfolio consisting of the U.S.-focusedVanguard Total Stock Market Index fund,Vanguard Total International Stock Index, and Vanguard Total Bond Market Index. Together, the three mutual funds, which also offer ETF shares, track more than 15,000 global securities.”

So, how to make this exciting for students?  How about tossing in a little math and a little competition?  Assume that 15 years ago, you were faced with a decision about investing a $1,000 gift from your aunt.  You only had one decision to make;

  • What percentage of the $1,000 to put in each of these three funds (need to add up to 100%):
    • Vanguard Total Stock Market Index Fund (VTSMX is ticker)
    • Vanguard Total International Stock Index (VGTSX is ticker)
    • Vanguard Total Bond Index (VBMFX is ticker)

Once they have selected that mix of funds, students commit to not change it over the 15 year period. I have created a spreadsheet which students can complete for this activity:  InvestingActivity_NGPF

For example, they might have chosen 50% Stock Market Index, 30% International Stock and 20% Total Bond Index.

So, how can they figure out how much they would have made.  Here is the five step process:

  1. Pick an asset allocation which adds up to 100% by choosing a percentage to put into each fund. For example, a student might have chosen 50% Stock Market Index, 30% International Stock and 20% Total Bond Index.  Ask them to write a few thoughts on why they selected the allocation they did.
  2. Find out what the price was on the date they purchased the fund (January 4th, 1999).  What was the starting price?  Go to Yahoo Finance and find historical prices for each of the three funds (if strapped for time, you can lead class through this process):  VTSMX, VGTSX and VBMFX.
  3. Find out how many shares were purchased for each of the three funds.  Using my example (#1 above), VTSMX made up 50% of the portfolio so the student invested $500 (50% of the $1,000 gift which is automatically calculated in the spreadsheet) in this fund.  Using January 4th, 1999 as the purchase date for VTSMX, I see that the Adjusted Close (the last column which is adjusted for dividends granted over time) for the fund is $20.96.  (Hint:  put the date January 4, 1999 into the Historical Price tool and go to last page in the series to get this price).  Divide $500 by $20.96 per share to get number of shares purchased (calculated in the spreadsheet).  Do the same for the other two funds.
  4. Determine the current value of the shares today using Yahoo Finance.  Students can input the tickers into Yahoo Finance and then input those prices directly into the spreadsheet.
  5. Calculate the value of the portfolio using today’s prices.  The spreadsheet will do this automatically but students should understand that the values were simply determined by multiplying the number of shares purchased in 1999 by the current stock price.  Students should compare the TOTAL VALUE to the $1,000 gift to see how they did.

As students are working on the spreadsheet, the teacher can check their work to ensure they understand what data to input into the spreadsheet and what it means.  Put student’s asset allocations on the board and their FINAL VALUES which will lead to interesting discussions.

  • Which allocation had the highest return?  Total Stock Market Index had highest return (136% over the period) so anyone that had 100% allocated to this fund would have the largest portfolio. Note that these figures will change as this is based on the value of the fund on 9/29/14 and your students will be using different final prices.  Not unusual to have all stock outperform but students need to know that this will lead to greater differences on year-to-year basis.
  • Which fund performed best/worst over this period?   Interestingly the Total Bond Index Fund (117%) outperformed the International Stock Index Fund (110%) which is unusual since bonds are generally considered to generate lower returns.  Important lesson is that past performance no guarantee of future results so this relationship doesn’t always hold.
  • How did a mix of funds perform? Since all three funds had returns from 110% to 136% over this period, you will not find much difference in the dollar value of the portfolio at the end of the period.  While this might be the case, it would be helpful to show that student’s experiences on year-to-year basis would have been much different based on their asset allocations.


For example, if they said 40% to go in the Total Stock Market Fund, that would be a $400 investment (40% X $1,000).

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.

Share This Post