Spreadsheet Math: Two Investments Walk Into A Classroom...

Mar 16, 2017
Activity, Behavioral Finance, Index Funds, Investing, Stocks, Math, Excel activities, compound interest

Ok, not the best title but let’s run with it. Let’s start with a question:

You have a choice between two investments of $100,000:

  • Investment #1: Earns a consistent 8% return every year (put aside the fact that an investment like this doesn’t exist at the current time; it’s been a while since you could buy a 30 year Treasury Bond with that kind of return).
  • Investment #2: Has an average return of 8% per year but has “lumpier returns” aka it has more volatile returns but the returns each year are in the top 10% of fund returns. Some years it is up, some years it is down, but overall it averages the same 8% return as Investment #1.

Which investment has a higher balance at the end of the 20 year period?

  • Investment #1
  • Investment #2
  • They must be the same since they have the same average return!


Hat tip to Barry Ritholtz at Big Picture Blog for the idea. I modified his spreadsheet and created this one  which will answer the question. A few ideas on how you might use this in the classroom:

  • BASIC: Have students answer the question then do the REVEAL by showing them the spreadsheet with the following questions:
    • Which investment performed better?
    • The investments both have the same average return, so how can one investment be so much better than the other?
    • Are there investments that you can earn a steady 8% per year?
    • What was the range of returns from low to high for investment #2? Do you think this is typical of an investment in the stock market?
    • What is your major takeaway from this exercise?
      • Investment managers often talk about “cutting their losses.” When you look at the data from this activity, it becomes clear how difficult it can be to “catch up” if you suffer a significant loss.
    • Does the sequence of returns matter? In other words if there are more negative returns earlier in the 20 year period, would the results change?
      • Change the sequence of results for Investment #2 so that all the negative return years (#2, #6, #8, #15,#19) occur early in the 20 year sequence? What are the results?
      • Next, put all of the negative return years at the end of the 20 year sequence. What are the results?
      • What conclusion can you draw? Does sequence of returns matter?
    • Input the last twenty years of return data for the S&P500 as Investment #2. Here’s a good data set for that. Use first column for 1997-2016.
      • What is the average return over this 20 year period?
      • Plug in that average return for Investment #1 and keep that constant for Investment #1 for that 20 year period.
      • Does Investment #1 still come out ahead? Why?

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