Ben Franklin's Will Lab  Part 1
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, including
Presidents Bush and Obama, 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.
From the class highlights page, click on this lab (it is under today's date).
For homework you read the web reading for lab.
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.
"...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
If 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.)
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 [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]:
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.
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.
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. 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 Philly
Question 5
At the end of first 100 years the Philly 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 Philly growth
in your Excel sheet in boxes
C4 and D4, and fill in the table above.
Question 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.
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.
We'll see what happened to the these earnings in Ben Franklin Part 2,
which will be the next project.
Show me your filled in chart and table. If it is correct then
you may quit Excel.
Next answer the following questions.
Question 7
What were Ben Franklin's goals and wishes for the money at
the end of the second
hundred years? Review
the web reading for lab to find his
instructions.
Question 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. 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 Ben Franklin's goals and wishes
(ie no keeping it for yourself!)
Question 9 Search the web for information related to
Ben Franklin. Write down something you found interesting.
Question 10 Skim through
Project 2 Project Criteria
and News Article Web Readings. Any questions?
Question 11
What city would you work on for Project 2? Cirlce one:
Boston Philadelphia?