Ben Franklin's Will - Part 1
From the class highlights page, click on this lab (it is under today's date). For homework you read the web reading for lab, the introduction to this project. You may wish to skim over that again. 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 see a chart that is partly filled in.
"...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....."
NAME________________________________________
1. Take out class notes and write down the lump sum formula.

savings =

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.

2. 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 $391,000 dollars. (Note that this is less than the $582,221.64 (the equivalent of 131,000 pounds) that Franklin had imagined.) To solve for the average earned rate of the fund (the answer is not 5% because of the problems that the fund faced) we could guess what average earned rate compounding yearly would give a balance of 391,000 dollars by trying different rates, but instead we'll use Excel to solve the lump sum equation for the unknown rate:

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+ben1)^100
    and then hit return. You will see "#NAME?"
  • Click on D2 and then type ben1 into the Name Box on the toolbar (if you don't see this, then make sure View/ Formula Bar is checked).
  • Click on the C2 box (which now reads 4444.44). 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. Type 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.
  • Compare your work with the tables below (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 in the formula bar):
    A B C D
    1 Info Time Money Formula Defined Rate Names
    2 Boston 100 years =4444.44*(1+ben1)^100 ben1
    3 Boston 200 years    
    4 Philly 100 years
    5 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 Boston 100 years 391000 dollars 4.5787863%
    3 Boston 200 years           dollars  
    4 Philly 100 years           dollars
    5 Philly 200 years           dollars  

    Make sure that you understand the Excel commands and the related lump sum formula before you move on. Notice that the Excel process is that we set up the right hand side of 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 to have it equal the savings and solve for the average earned interest rate.

    3. 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. Here is both sides of the lump sum formula with the numbers filled in (but leave the earned rate of the fund as a variable) 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) in your Excel sheet in boxes C3 and D3, and then fill in the relevant row of both tables above. Be sure to use a different name for the rate. For example, you could use ben2. In addition, be careful that you set up the lump sum formula in the correct box (C3), insert/name into the correct box (D3), and that you use goal seek on the correct box (C3). When you are finished, fill in the relevant row of both tables above.

    The Fund in Philly

    4. At the end of first 100 years the Philly fund had grown from $4444.44 dollars to only $172,350 dollars. 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):

    _____________________________________________ Solve for the average earned interest rate responsible for this Philly growth in your Excel sheet in boxes C4 and D4.

    5. 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 $2,256,952.05 by the end of the second hundred years. 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):

    _____________________________________________ 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 both tables above.

    6. We'll see what happened to the these earnings in Ben Franklin Part 2. Show Dr. Sarah your filled in tables, save the file, and then post the file to your personal storage space in ASULearn.


    7. 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? Think about how often Ben put money into the account and relate this to the choice of the formula. Also address why the interest coming back every year is already accounted for in the lump sum formula by relating your answer to our discussion of the philosophy of the derivation of the formula in class.

















    8. 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. Notice that D2 says "Average Earned Interest Rate". Explain in your own words why this isn't the actual lent interest rate of approximately 5% that was charged to the lenders? Relate your answer to the definition of average and how this relates to the lending process and the problems that occurred here. Ie how these would affect the average earned rate that the fund yielded?






















    9. As I mentioned previously, this all really happened (and all of the numbers are the actual numbers). So, at the end of the second hundred years, Boston and Philly had to decide what to do with their respective earnings. Given Ben Franklin's goals and wishes, what would you do with these funds for the cities of Philadelphia and Boston? Relate your answer to his goals and wishes from the homework reading.