"...In 1785 a French mathematician named Charles-Joseph Mathon de la Cour wrote a parody mocking the spirit of American optimism represented by Franklin. The Frenchman wrote a piece about Fortunate Richard leaving a small sum of money in his will to be used only after it had collected interest for 500 years. Franklin, who was 79 years old at the time, wrote back to the Frenchman, thanking him for a great idea and telling him that he had decided to leave a bequest to his native Boston and his adopted Philadelphia of 1,000 pounds to each on the condition that it be placed in a fund that would gather interest over a period of 200 years. Franklin's plan was to lend money to young apprentices. The fund would charge a certain interest rate to the borrowers, and would earn annual interest when each borrower would pay back principal plus interest each year. In addition, Franklin had plans for how the money would be used:I wish to be useful even after my Death, if possible, in forming and advancing other young men that may be serviceable to their Country both in Boston and Philadelphia.To this end I devote Two thousand Pounds Sterling, which I give, one thousand thereof to the Inhabitants of the Town of Boston in Massachusetts, and the other thousand to the Inhabitants of the City of Philadelphia, in Trust and for the Uses, Interests and Purposes herinafter mentioned and declared....."

"... If this plan is executed and succeeds as projected without interruption for one hundred Years, the Sum will be one hundred and thirty-one thousand Pounds of which I would have the Managers of the Donation to the Inhabitants of the Town of Boston, then lay out at their discretion one hundred thousand Pounds in Public Works......The remaining thirty-one thousand Pounds, I would have continued to be let out on Interest in the manner above directed for another hundred Years.....At the end of this second term if no unfortunate accident has prevented the operation the sum will be Four Millions and Sixty-one Thousand Pounds.... of which I leave one million sixty-one thousand pounds to the disposition of the inhabitants of the town..., and three millions to the disposition of the government of the State, not presuming to carry my views farther."

Your lab grade will be based on the depth, clarity and correctness of your responses.

1. What interest rate was Franklin planning on for the first 100 years? If

=1000*(1+ben1)^100

and then hit return. You will see "#NAME?" since Excel doesn't know what ben1 means. We'll define it.

2. Franklin planned that reinvesting

=31000*(1+ben2)^100

and then hit return. You will see "#NAME?"

We set up the lump sum formula in the C box, define the name in the D box, and then use Goal Seek back on the C box.

3.
In January 1894, at the end of 100 years from the
inception of the Franklin gift, because of these problems, the Boston
fund had grown from $** 4444.44** dollars
(the equivalent of $1000 pounds) to
$

4. At the end of 100 years the Philly fund had grown from
$** 4444.44** dollars to only $

5. In Boston, $

6. In Philly, $** 39,274**
was reinvested at the end of the first hundred
years. By lending money to borrowers at 5% interest,
the fund grew
to $

We'll see what happened to the these earnings in Ben Franklin Part 2.

For the first table, to remind yourself of the formulas that you used, click on the corresponding box in Excel, and look at the top next to the equal sign (under view, release on Formula Bar if you do not see this already).

A | B | C | D | |

1 | Info | Time | Money Formula |
Defined Rate
Names |

2 | Ben's Plan | 100 years | =1000*(1+ben1)^100 | ben1 |

3 | Ben's Plan | 200 years | =31000*(1+ben2)^100 | ben2 |

4 | Boston | 100 years | ||

5 | Philly | 100 years | ||

6 | Boston | 200 years | ||

7 | Philly | 200 years |

A | B | C | D | |

1 | Info | Time | Money in Dollars or Pounds | Average Earned Interest Rate
as a Percentage - don't round |

2 | Ben's Plan | 100 years | 131000 pounds | 4.99599% |

3 | Ben's Plan | 200 years | 4061000 pounds | 4.99599% |

4 | Boston | 100 years | dollars | |

5 | Philly | 100 years | dollars | |

6 | Boston | 200 years | dollars | |

7 | Philly | 200 years | dollars |

1. Take out class notes and write down both the left and right hand sides of the general (no numbers yet) lump sum formula when n=1 (we compound yearly since the fund earns interest once a year when each borrower would pay back principal plus interest once a year) and highlight or circle the previously unknown variable that that we just solved for. (ie Savings = Principal* ...)

2. For Ben's plan for the first 100 years, we knew that 131,000 = 1000(1+rate)^100. So, we entered the right hand side of the equation into Excel in the C box. We then defined the rate in the D box. Since we wanted the savings (which was in the C box) to equal 131000 by solving for the rate (in the D box), then we used goal seek on the C box. Our "goal" was for the C box to equal 131000 by changing the rate in the D box. We then let Excel do the work. Notice that we repeated this process for Ben's plans for the second 100 years, but the numbers in the lump sum formula were different. Write down both the left and right hand sides of the lump sum formula with the numbers filled in for all BUT the previously unknown rate for Ben's plans for the second 100 years.

3. Write down both the left and right hand sides of the lump sum formula with the numbers filled in for all BUT the previously unknown rate for the actual first hundred years in Boston and then Philly AND the actual second hundred years in Boston and then Philly (ie you should have 4 equations).

4. If the borrowers are paying back money each year (so that the fund can earn interest), why is the lump sum formula appropriate to use here (Hint: think about how often Ben put money into the account and relate this to the choice of the formula)?

5. After 200 years, Boston and Philly ended up with a lot of money using Ben's loan method, but they ended up with a lot less money than he had planned (note the difference between pounds and dollars). Notice that D2 says "Average Rate". Explain in your own words why this isn't the actual lent interest rate of approximately 5% that was charged to the lenders. (Hint - think about the lending process, the problems that occurred and how this would affect the average earned rate that the fund yielded).

6.