Ben Franklin's Will - Part 1

This all actually happened! It is a great project since it combines financial mathematics with the disciplines of history, economics, political science, ethics and philosophy. In addition, it gives you a chance to see how interest is actually earned by a fund or a bank when they loan out money. Once I give you part 2, this will become a major writing project.
"...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....."
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:
"... 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."
NAME________________________________________
Your lab grade will be based on the depth, clarity and correctness of your responses.
Ben Franklin's plans
1. What interest rate was Franklin planning on for the first 100 years? If 1000 pounds were invested for 100 years, which interest rate compounding yearly would give a balance of 131000 pounds? We could guess by trying different rates, but instead we'll use Excel.
  • From the main web page, click on this lab (it is under the due date of Thursday), scroll down, and then click on this Excel file link from Internet Explorer . 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.
  • In the C2 box, type EXACTLY what you see on the next line (including the equal sign)
    =1000*(1+ben1)^100
    and then hit return. You will see "#NAME?" since Excel doesn't know what ben1 means. We'll define it.
  • Click on D2, and you will see that the D2 box is outlined. Under Insert, scroll down to Name and then over to Define and release the mouse button. Type in ben1 (in the Names in workbook slot). Notice that the Refers to line will already say =Sheet1!$D$2. Now click on OK.
  • Click on the C2 box (which now reads 1000). We want Excel to solve for the interest rate that will result in 131000. Under Tools, scroll down to Goal Seek... and release. Set cell: should already read C2. Type 131000 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 interest rate solution is in box D2.

    2. Franklin planned that reinvesting 31,000 pounds for the second hundred years would result in 4,061,000 pounds. If 31000 pounds were invested for 100 years, which interest rate compounding yearly would give a balance of 4061000 pounds? We'll answer this together using a similar process to the one described above.
  • In the C3 box, type EXACTLY
    =31000*(1+ben2)^100
    and then hit return. You will see "#NAME?"
  • Click on D3. Under Insert, scroll down to Name and then over to release on Define. Type in ben2. The Refers to line will already say =Sheet1!$D$3. Click on OK.
  • Click on the C3 box (which now reads 31000). We want Excel to solve for the interest rate that will result in 4061000 pounds. Under Tools, scroll down to Goal Seek... and release. Set cell: should already read C3. Type 4061000 in the To value: slot. Type D3 in the By changing cell: slot. Goal Seek will find a solution, so then click OK. Notice that the interest rate solution is in box D3.
  • Save your Excel sheet and answer questions 1-2 in the Questions to Answer section below. Look at Table 1 and 2 below and make sure that your work matches what is listed there. In addition, make sure that you understand the Excel commands and the related lump sum formulas (from questions 1-2) before you move on. Notice that the Excel process is the same in both cases:
          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.
    The Actual First Hundred Years Even though the fund charged borrowers 5% interest, 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. Note: I'm changing to dollars for the rest of the lab since this change also occurred historically before the end of the first hundred years. So, use my dollar figures (I've done all the conversions already).

    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 $391,000 dollars. (Note that this is less than the $582,221.64 (the equivalent of 131,000 pounds) that Franklin had imagined.) What average earned interest rate was responsible for this Boston growth (the answer is not 5% because of the problems that the fund faced)? Answer this in your Excel sheet in boxes C4 and D4 using a similar process to the one described above (which you should be fairly familiar with by this point!). Think about what your new lump sum formula will be. Be sure to use a different name for the rate. For example, you could use ben3. In addition, be careful that you set up the lump sum formula in the correct box (C4), insert/name into the correct box (D4), and that you use goal seek on the correct box (C4).

    4. At the end of 100 years the Philly fund had grown from $4444.44 dollars to only $172,350 dollars. What average earned interest rate was responsible for this Philly growth Answer this in your Excel sheet in boxes C5 and D5. Make sure to 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.

    Note that a small difference in the average earned interest rate resulted in a substantial difference in the fund's income!

    The Actual Second Hundred Years 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.

    5. In Boston, $100,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. What average earned interest rate was responsible for this Boston growth (the answer is not 5% since the fund faced similar problems as were faced during the first hundred years). Answer this in your Excel sheet in boxes C6 and D6.

    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 $2,256,952.05 by the end of the second hundred years. What average earned interest rate was responsible for this Philadelphia growth? Answer this in your Excel sheet in boxes C7 and D7.

    We'll see what happened to the these earnings in Ben Franklin Part 2. Show Dr. Sarah your Excel work and then fill in the tables below. After that, answer question 3. Then you may either work on homework due tomorrow or answer questions 5 - 6 below (this lab is due on Thursday in order to give you time to reflect and allow for depth and clarity in your responses).

    Tables Fill in the following 2 tables. I have filled in the first two rows for you.

    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  

    Questions to Answer
    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.



    Go back to the end of Ben Franklin's plans (above) now.
    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. Worth More 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.