Who wants to be a millionaire?

1. Ask students to first attempt this problem using pen and paper.
2. Ask students this central question:

   Is the king correct? How many metric tons of rice would be required on the board, assuming they would fit(!), if each grain weighs 0.025g? 
   
   (Note: 1,000 kg equals a metric ton.) 

3. Issue students with a sheet of 1 cm grid paper and ask them to begin writing the number of grains required to be placed in each square, until they give up.
4. Revise exponential numbers and notation at this point, as it will become a necessity.
5. Students establish that the number in each square is 2 raised to one less than the number of the square, thus:

   

   TOTAL   1+2+4+8+ ... +2^63 grains of rice

   TOTAL GRAINS

   =1+2+4+8+…+2^63

Student spreadsheet task

6. Ask students if we might be able to use our knowledge of spreadsheets to automate this tedious calculation and find the answer. See if they can come up with the iterative solution.
7. If not: 

   Demonstrate the first three lines of a spreadsheet solution. Assuming students know about Filling down, have them complete the task and add a final SUM formula to total all the squares.
   (Enter a 1 in A1 and B1
   Enter =A1+1 in cell A2 and =B1*2 in cell B2
   then Fill down both columns to row 64)
   A completed spreadsheet is provided for this lesson sequence (Spreadsheets and iteration worksheet: Chessboard problem).

8. Students should get the following grains for their total:

   1.844674407370960000000000000000E+19

9. Discuss the inaccuracy of this answer. (Spreadsheets are limited to 15 decimal places.)
10. Ask students to research briefly why this limitation exists … Shouldn't we expect a well-known spreadsheet like Excel to be more accurate?
11. Have students calculate the weight in metric tons of this many grains.

(Note: Actual value is
= 18,446,744,073,709,551,615 grains 
= 461,168,602,000 metric tons, which would be a pile of rice larger than Mount Everest and around 1,000 times the total global production of rice in 2010.)

The surprising conclusion:
On the entire chessboard there would be 
2^64 − 1 = 18,446,744,073,709,551,615 
grains of rice, weighing 461,168,602,000 metric tons, which would be a pile of rice larger than Mount Everest and around 1,000 times the total global production of rice in 2010 (which was 464,000,000 metric tons).

• Students will learn how to use spreadsheets to simulate real-life iterative problems, such as compound interest rates. They will use absolute cell referencing to automatically extend formulas.
• This lesson adds to previous spreadsheet skills of advanced filtering, pivot tables and automation.

1. Ask: How much money will we have after 100 years if we invest $100 now at a compound interest rate of 10% per annum, and do not withdraw any money?
2. Demonstrate in step form year by year how this works (explain each term clearly, especially the 1.10):

   End yr 1: $100 x 1.10 = $110.00
   End yr 2: $110 x 1.10 = $121.00
   End yr 3: $121 x 1.10 = $133.10
   Etc
   End yr 100: ????
   Tell students the answer may surprise them!

3. Suggest that we might be able to use the spreadsheet iteration ideas we have learned from the chessboard problem to construct a spreadsheet to perform this for each of the 100 years.
4. Work with students to develop technique.

1. Students design a spreadsheet to calculate the value of the investment at the end of each of the years 1 to 100.
   • Their goal will be to find out how rich they will be at the end of the 100th year.
   • The spreadsheet should use:
     • a cell holding the interest rate expressed as a decimal (here 0.10)
     • a cell holding the principal (here $100) 
     • 100 rows, each labelled with 'year n'.

Final stage (Goal seeking)

2. Ask: 100 years is a long time, and you are unlikely to be alive to see the money, despite medical advances! What would you have to invest as principal to make $1,000,000 within 30 years?
3. Say that we can use goal seeking to find the answer. Demonstrate goal seeking. Tools > Goal seek…
4. Experiment with other possibilities (such as changing the interest rate).

These will be the values if row 32 is year 30 (as is the case with the accompanying Spreadsheets and iteration worksheet: Compound interest) 

Goal seeking is quite exciting as students see the figures down the spreadsheet rippling as the calculation proceeds – a rare visible example of the time taken for the extensive recalculations involved.

Answer: We need to invest $57,309 now to be a millionaire within 30 years with an interest rate of 10% p.a. 

1. Ask the students:

   What other iterative problems could we solve using these techniques?
   (Moore's law, exponential growth, radioactive decay, Fibonacci problems, factorials) 

Challenge

2. Challenge students to themselves devise or research a problem that can be solved by iteration in a spreadsheet, and to design and run the spreadsheet calculation to solve it.
3. Suggest to students that they will discover that the requirements for this spreadsheet introduces important techniques for using spreadsheets:
   • A cell holding the interest rate expressed as a decimal (here 0.10)

   (The part of the formula using this interest rate will need to have it absolute referenced. It will be instructive for students to fall into this error to amplify the distinction between absolute and relative addressing when filling down a formula.)

   • A cell holding the principal (here $100)
   • 100 rows each labelled with 'year n'

   (This will likely require the CONCATENATION formula and a column of integers used for this purpose.)

   • Suitable formulas need to be filled down to perform the calculations.  
4. After students have completed this activity, discuss iteration.
5. Give students the formula (explain that it will be proven in maths classes).

   A=P(1+r)^n
   =100*(1.1)^100
   =$1,378,061.23 

6. Tell them they will be millionaires, after 97 years!
7. Ask students to check their final answer for the end of the 100th year from the spreadsheet against the answer using this formula.