Ben Franklin's Will - Lab
Goal: Investigate real-world data and interpret key features
Goal: Utilize technology to adapt and use mathematical formulas that include cell referencing to answer real-world questions and interpret results
There have been numerous presidents and founding fathers who have connections to mathematics. James Garfield, who served as the 20th president, also created a proof of the Pythagorean Theorem. Recent presidents have highlighted the importance of mathematics and science in global competitiveness. While Benjamin Franklin was never a president, he certainly is considered a founding father.
Click on this Excel file link. You will either see the data come up automatically or you must open it yourself from Excel. You will then see a chart that is partly filled in with descriptors.
"...I wish to be useful even after my Death, if possible, in forming and advancing other young men... 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....."
The Fund in Boston Even though the fund charged borrowers 5% interest (as Franklin had planned on earning), it was not always possible to find as many borrowers as Franklin had planned and there were other problems as well since some of the borrowers did not pay back their loans. The average earned rate is the weighted average of all the rates each part of the money actually earns.
Question 1: Hypothetical Situation: If the fund lent out half of its money at 5% to borrowers who all paid back their loans plus interest, but if the fund could not find any borrowers for the other half of the money (ie 0%), what would the average earned rate of the fund be?


Question 2 We know from the homework readings that the fund lent some of its money to borrowers who didn't repay anything. How would that "rate" contribute in the calculation of the average earned rate? Circle one:       negative       0

In real life, in January 1894, at the end of 100 years from the inception of the Franklin gift, because of these types of problems, the Boston fund had grown from $4444.44 dollars (the equivalent of $1000 pounds) to $391,000 dollars. (Note that this is less than the $582,221.64 (the equivalent of 131,000 pounds) that Franklin had imagined, because even though the fund charged 5%, the problems reduced the earnings.) We'll use Excel to solve the lump sum equation for the unknown rate. Benjamin Franklin only put in a one time infusion of money, so lump sum is appropriate as the rest of the money comes in as what was borrowed plus interest. This exactly matches the scenario we used when we derived the formula: We obtained the general formula for lump sum using the total from the year before to calculate the principal and interest for the next year. This process works fine, but is too difficult to use when the number of years is large. So we looked for a way to obtain a simplified formula. We looked for the commonality and recognized the repeated appearance of (1+rate) after factoring. Once we found this pattern, we used it to find a simplified formula, which represented one lump principal with only new interest coming in. In this context lump sum is
391,000 = 4444.44(1+average earned rate)100

  • In the C2 box of your downloaded Excel file, type (and don't forget the equals sign that always comes before Excel equations!):
    =4444.44*(1+D2)^100
    and then hit return. This now reads 4444.44.
  • Click back on C2. We want Excel to solve for the average earned interest rate that will result in 391000.
    Under Tools, scroll down to
    Goal Seek... and release.
    Set cell should already read C2.
    Put 391000 in the To value slot.
    Type D2 in the By changing cell slot.
    Goal Seek will find a solution, so then click on OK. Notice that the average earned interest rate solution is in box D2.

    Question 3 Does your solution for the average earned rate match with the solution in D2 in first row of the table below? (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 in the formula bar - if this is not visible, under View, release on Formula Bar). Make sure that you understand the Excel commands and the related lump sum formula before you move on so that you can adapt them. Notice that the Excel process is that we set up the right hand side of the lump sum formula in the C box, while referring to the unknown rate in the D box, and then we used Goal Seek back on the C box to have it equal the savings and solve for the average earned interest rate.

    A B C D
    1 Info Time Excel Formula Average Earned Rate Lump Sum Formula Goal Seek Amount
    2 Boston 100 years =4444.44*(1+D2)^100 4.5787863% 391,000 = 4444.44(1+average earned rate)100 $391,000
    3 Boston 200 years    
    4 Philly 100 years
    5 Philly 200 years    

    Question 4 As per Ben Franklin's wishes, a portion of the earnings from the first hundred years went back into the fund to be loaned out, while the remainder was given to the cities. In Boston, $100,000 of the $391,000 was reinvested at the end of the first hundred years. By lending money to borrowers at 5% interest, the fund grew to 5 million dollars ($5,000,000) at the end of the second hundred years. Since the lump sum principal has been modified from the original, we must start a new lump sum calculation for the second hundred years, with the new principal amount. Here is both sides of the lump sum formula with the numbers filled in for the second 100 year period:
    5,000,000 = 100,000(1+average earned rate)100
  • Use Excel to solve for the average earned rate of the fund (the answer is not 5% since the fund faced similar problems as were faced during the first hundred years). Be careful that you set up the lump sum formula in the correct box (C3), refer to (D3) for the unknown rate, and that you use goal seek on the correct box (C3). When you are finished, compare your answer with a neighbor and then fill in the relevant row of the table above.

    The Fund in Philadelphia
    Question 5 At the end of first 100 years the Philadelphia fund had grown from $4444.44 dollars to only $172,350 dollars. In the above table, fill in both the left and right hand sides of the lump sum formula with the numbers filled in (but leave the earned rate of the fund as a variable). Next, solve for the average earned interest rate responsible for this Philadelphia growth in your Excel sheet in boxes C4 and D4, and fill in the table above.

    Question 6 In Philadelphia, $39,274 was reinvested at the end of the first hundred years and the fund grew to $2,256,952.05 by the end of the second hundred years. In the above table, fill in both the left and right hand sides of the lump sum formula with the numbers filled in (but leave the earned rate of the fund as a variable). Next, solve for the average earned rate of the fund in your Excel sheet in boxes C5 and D5, and then fill in the relevant row of the table above.
    Question 7 In The Heart of Mathematics you read about "The difference that one percentage point makes" for a hypothetical situation involving Adam and Eve. We see similarities here. During the first hundred years, Boston partially gave up on Franklin's plan and invested in savings accounts and a life insurance company rather than loaning to individuals in order to help them. Compute the difference in Boston and Philadelphia's average earned rates during the first 100 years?



    Question 8 In your own words, explain the seemingly disparate concepts of the average earned rate of the fund versus the 5% lent rate.







    Question 9 After 200 years, Boston and Philadelphia ended up with a lot of money using Franklin's loan method, but they ended up with a lot less money than he had planned. They had to decide what to do with their respective earnings. What would you do with these funds for the cities of Philadelphia and Boston - be sure to relate your answer to Benjamin Franklin's original goals and wishes to form and advance others (ie no keeping it for yourself!) as well as his wishes for the end of the 200 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 sterling; of which I leave one million sixty-one thousand pounds to the disposition of the inhabitants of the town of Boston, and three millions to the disposition of the government of the State, not presuming to carry my views farther."






    Question 10 Search the Internet for information related to Benjamin Franklin. Write down something you found interesting.



    Question 11 Skim through Project 1 Criteria. Any questions?



    Question 12 What city would you work on for Project 2? Circle one:       Boston       Philadelphia?