Eric Gaze, the Director of the Quantitative Reasoning Program at Bowdoin College and also a Senior Lecturer in Mathematics, includes financial literacy and spreadsheets in his teaching to connect mathematics with real world needs. This particular example is designed for a Quantitative Reasoning or Math for Liberal Arts course, but could naturally fit into an introductory probability and statistics course.
Students are told that a friend has offered them the opportunity to invest in risky IPO’s (Initial Public Offerings) on a weekly basis. Their friend shows them data they have analyzed showing that it is equally likely to get a return of 80% in a week, or to lose 60% in a week. The friend claims that the average weekly rate of return an investor could claim will be 10%!
First, Gaze has students break into groups and determine how much a $10,000 investment will grow into, if it averages 10% return a week for one year. The answer of $1.4 million is very surprising, and exciting to many students! This assesses and activates the group’s prior knowledge of exponential functions and compound interest.
The students are now told to build a spreadsheet to model this situation in more detail for one year. Students must apply their understanding of Excel’s logic functions, IF in particular, and also create random outcomes with the RAND function. RAND generates a number between 0 and 1, which can be used to create a logical test that outputs 80% or -60% as equally likely rates of return. To make something grow by 80% or decrease by 60% you multiply by the factors 1.80 or 0.40 respectively. This is put in the IF function as follows: =IF(RAND() < 50%, 1.8, 0.4). The spreadsheet will then use this weekly rate of return to generate the ending balance, all of which students can fill down for 52 weeks. The ending balance after one year in repeated trials is going to consistently hover around $1.95, not $1.4 million!
Determining why this happens requires students to use their knowledge of geometric means related to averaging rates of return. Many people (including, apparently their friend) do not realize that you cannot take the arithmetic mean of 80% and -60% because growth rates do not combine additively. The unknown average rate of return will have an associated factor equal to the geometric mean of these two factors (1.8 and 0.4), which is approximately 0.8485. This results in an average weekly rate of return of -15.2% NOT the +10% your friend is excited about. An investment of $10,000 that loses 15.2% every week for one year results in the $1.95 balance that the spreadsheet model hovered around.
There are great questions for students to explore in this analysis. Why can we just use two values to compute the average rate of return? Is it possible for the ending balance to be $1.4 million? Is there a negative rate of return that along with the 80% will result in an average rate of 10%? Using spreadsheet technology allows students to run their model over and over to look for patterns and quickly explore their curiosities without worrying about calculation errors.
Digital Resources
Excel or any other spreadsheet
Gaze uses this as an eye-opening exercise for students. He advises instructors to anticipate students may struggle with making the spreadsheet model, which could create cognitive overload on top of the mathematical content. Creating a spreadsheet model like this requires higher level thinking to engage in the transfer of mathematical knowledge to a new domain. Scaffolding spreadsheet activities throughout the semester will build confidence and familiarity for these more advanced models. Providing students with the IF function and frequent progress checks throughout the activity can also support success.
Digital Enablement
This activity asks students to build a spreadsheet that incorporates logic functions as well as randomness to model a financial investment opportunity. Students are required to build on their prior knowledge of exponential functions and compound interest to create a robust spreadsheet model. The model allows groups to conduct sophisticated What-IF? analyses to answer questions that would be beyond their mathematical abilities otherwise, deepening student appreciation of the power of spreadsheet modeling and interest. Having students build the Excel model in groups and explore outcomes efficiently leads to meaningful conversations regarding logic functions and probability. Creating a random model that can be run multiple times quickly means the answers will constantly change and this can be very disconcerting for students who are used to there being one right answer in mathematics. This activity illustrates the variability inherent in randomness through the final balance of $1.95 appearing most frequently “in the long run”.