The following projects require the use of a spreadsheet. They can all be done using only the ideas in Chapter 1. In addition, Project 9 (Verhulst: The Logistic Model) and Project 10 (The Spread of Information) give another perspective on material in Chapters 4 and 9. Project 4 (Comparing Home Mortgages) uses a geometric series but can be done before Chapter 10.
In this project, we compare exponential and linear growth. We see the eventual dominance of exponential functions over linear functions.
One of the most famous models of population growth was made by Thomas Malthus in the early 19th century. Malthus believed that while human population increased exponentially, its means of subsistence increased linearly. The gloomy conclusion that Malthus drew from this observation was that the population of the earth would inevitably outstrip its means of subsistence, resulting in an inadequate supply of food. (Malthus went on to note that this state of affairs could only be averted by war, famine, epidemic disease, wide-scale sexual restraint, or other such drastic checks on population growth.)
The following table shows part of a spreadsheet showing such as a scenario.16 The starting population is 1 million, while the available food feeds 2 million people. The population grows at an annual rate of 3% per year and the food production increases by 100,000 per year. These population and food growth rates are in cells on the right of the spreadsheet. The fourth column contains the ratio of available food per person in the population. The spreadsheet includes a safety ratio—so long as the food-to-population ratio is above this figure of 1.5, the fifth column displays “Yes”; whenever the ratio drops below this figure the fifth column displays “No” (as it does by the end of the 21st century).
We see that at first there is plenty of food—the ratio of food to population is 2, which means that there is twice as much food as is necessary to feed the population. For the first few years the ratio increases, but at a certain point it starts to decrease, and eventually the ratio drops below one.
1. Set up your spreadsheet to look like the one shown in the table, extending it to the year 2100. Virtually every cell must contain a formula — the exceptions being the six cells containing “1999,” “1,000,000,” “2,000,000,” “3.00%,” “100,000,” and “1.5.”
2. (a) About what year is the food-to-population ratio the highest?
(b) In which year does this ratio reach 1?
3. There are at least two ways to improve upon the current situation: we can lower the population growth rate, or we can increase the food supply.
(a) What would the population growth rate have to be lowered to, in order to have the food-to-population ratio not reach 1 until the year 2100? (Keep the food supply increasing at 100,000 per year.)
(b) What would the food supply rate have to be increased to, in order to achieve this same goal, of the ratio not reaching 1 until the year 2100? (Keep the population growth rate at its original 3%.)
4. Using the original scenario, create each of the following charts (both line and column). Extend the charts to the year 2100, so that the point where the population outstrips the food supply is clearly evident.
(a) Showing population and food, with the years on the horizontal axis.
(b) Showing only the ratio, with the years on the horizontal axis.
You have a credit card on which you owe $2000. Your credit card company charges a monthly interest rate of 1.5% and requires a minimum monthly payment of 2.5% of your current balance. (This payment scheme is similar to ones used by many credit card companies, but see Question 8.)
[Note: For Questions 1–2, you will not need a spreadsheet, although you will need a calculator.]
1. If the monthly interest rate is 1.5%, what is the effective annual interest rate?
2. As a rule, the minimum monthly payment required exceeds the interest accrued in a month. (For example, here the minimum monthly payment of 2.5% exceeds the monthly interest charges of 1.5%.) Explain why this should be the case. What would happen to the card balance if the minimum required payment was less than the accrued interest?
Suppose that you decide to pay off your $2000 credit card debt by making only the minimum required payment every month. Assume that you make no further charges to the card, since you're trying to pay it off.
3. Since 2.5% of $2000 is $50, your first monthly payment is $50. Before you do any spreadsheet calculations, guess how long it will take to bring your total balance down from $2000 to less than $50, assuming that you make only the minimum required payment every month. A rough guess is fine; use common sense and explain your reasoning.
4. Over time, your monthly payments, which start at $50, will decrease. Explain why this happens. Does the fact that your monthly payments decrease affect the answer you gave to Question 3?
5. Although you only owe the credit card company $2000, you will end up paying quite a bit more than $2000, due to interest charges. Make your best guess (before making any specific calculations) as to how much, roughly, you will end up having paid the credit card company for your initial $2000 debt.
Set up a spreadsheet showing the number of months since you began paying off your debt, your current balance, the interest due that month, and the payment you make, each in a separate column. Each quantity should be calculated by a formula.
Example: To figure out what formulas you need, recall that your initial balance is $2000, the monthly interest charged is 1.5%, and the minimum required payment is 2.5%. Thus, at the beginning of Month 1, your balance is $2000, since you have paid off nothing yet. Therefore, at the end of Month 1, the interest that you owe is 1.5% of the $2000 balance, or $30. Your minimum payment is 2.5% of the $2000 balance, or $50. Thus, at the beginning of Month 2, your new balance is the old balance of $2000 plus the $30 in interest minus the $50 payment, or $1980. Notice that the figures for Month 2 depend on the figures for Month 1; similarly, the figures for Month 3 depend on Month 2 figures, and so on. Follow this procedure to figure out what formulas you need in each column of your spreadsheet.
Once you have set up a working spreadsheet, answer the following questions.
6. How good was your guess in Question 3? Using your spreadsheet, find out how many months it takes to bring your balance down to less than $50. Was your guess close, or were you surprised by how long it really takes?
7. How good was your guess in Question 5? Using your spreadsheet, figure out exactly how much you pay the credit card company to bring your balance down to less than $50. How does this figure compare to the original debt of $2000?
8. Use your spreadsheet to find out how long it takes to bring your balance down to $0. Or can't you tell? Does there ever come a point when you have exactly paid off your debt? [Hint: Eventually, the minimum monthly payments and the interest charges become unrealistic. In what way are they unrealistic? How do real credit card companies avoid this problem?]
9. Now let's try experimenting with the numbers and see what happens. In each of the following cases, make the appropriate changes to your spreadsheet. Assume that as soon as your balance is under $50, you pay it off in a lump sum.
(a) If every month you pay $1 more than the minimum required payment, how long does it take to bring your debt down to less than $50? How much do you end up paying to your creditors in total? How much money do you save by using this payment scheme instead of the one in Question 5?
(b) Your first monthly payment is $50. If you paid $50 every month, instead of the minimum required payment, how long does it take to bring your debt down to less than $50? How much money do you save by using this payment scheme instead of the one in Question 5?
(c) Recently, many credit card companies have made offers similar to the following: if you transfer your debt from a competitor's card to their card, they will charge you a lower interest rate. Suppose you find a credit card company willing to make this transaction, and that their monthly interest rate is 1%, not 1.5%. Leaving all the other original assumptions unchanged, how long does it take to bring your debt down to less than $50, and how much do you pay your creditors in total? How much money do you save compared to what you would have paid your original card company?
10. Comparing the scheme used in Questions 3–5 with each of the schemes in Question 9, what conclusions can you reach about paying off a credit card debt?
A local bank offers the following loan packages. Use a spreadsheet to decide which option is the best. The packages are as follows:
Interest is compounded with the same frequency as payments are made. Notice that the first and last loans have two-year payoff periods; the middle loan is for three years.
Hint: The difficult part of Questions 1 and 2 is figuring out your monthly (or biweekly) payments. There are formulas that give the payment based on the period and amount of the loan and the interest charged, but instead of using them, we will use a spreadsheet. The idea is that you can make an educated guess as to what the payment ought to be, and then use a spreadsheet to check your answer. By looking at the spreadsheet, you can decide whether your guess was too high or too low, and thus improve upon your original guess. It's surprising how quickly you can zero in on the required monthly payment, down to the nearest penny, by this guess-and-check method.
For example, consider the first loan, the two-year $2000 loan at 9%. Set up a spreadsheet with the initial balance of $2000, the interest for the first month, which is (9%/12) · $2000 = $15, and a guess at the monthly payment. There are lots of ways to make a guess at the monthly payment. One way is to say that if you were to borrow $2000 for 2 years at 9%, you would owe about $2000(1.09)2 = $2376. (Never mind the monthly compounding—this is just a rough approximation.) To pay off this amount in 24 equal monthly payments would require $2376/24 = $99. So, we guess a monthly payment of $100. Using this guess, the second month's balance will be
Thus, the next month's interest will be (9%/12) · $1915, and the next month's payment should be the same as the first month's payment, or $100. Continue this process until 24 months' (two years') worth of payments have been made. You will see that the final balance is negative, meaning you paid the bank more than you really owed. This means that $100 is too high a monthly payment to pay off your $2000 loan. (We could have predicted that this was the case when we made our estimate above. Do you see why?) So, since $100 is too high, you might guess that an $80 monthly payment would be right. If you do, you'll see that you'd still owe the bank some money after 24 months had passed. This tells you that $80 is too low a monthly payment, and that the actual payment is somewhere between $80 and $100. This procedure can be repeated until the exact monthly payment is reached.
To do this project, first go to any bank and ask for a fact sheet of their most recent mortgage loan rates. Banks are happy to provide them.
Obtain rates for a thirty-year loan, a fifteen-year loan, a thirty-year biweekly loan, and a twenty-year loan (if available) for $100,000 with zero points. (Note: Some loans include points. A point is an additional fee paid to the lender at the time of the loan equal to 1% of the amount borrowed. Typically, you get lower interest rates by paying a point or two. We will only consider loans with zero points.)
The following formula can be used to determine your payment, x:
where P is the amount of the loan—in this case $100,000—and n is the number of payments. For a thirty-year loan with monthly payments, n = 360; for a thirty-year biweekly loan, n = 780 (there are 26 payments every year). Finally, r is the interest rate per period plus 1. (For example, if the interest rate is 2%, then r = 1.02.) In Question 4, you will derive this formula for x using the following formula for the sum of a geometric series:17
On Thursday, February 24th, 1993, Bruce Hegarty of Dennis Port, MA, received the first installment of the $26,680,940 prize he won in the Mass Millions state lottery. Mr. Hegarty was scheduled to receive 19 more such installments on a yearly basis. Each check written by the Lottery Commission is for one twentieth of the total prize, or $1,334,047. Why doesn't the Lottery Commission pay all of Mr. Hegarty's prize up front, instead of making him wait for twenty years?
Consider two investment projects. Project A is built in one year at an initial cost of $10,000. It then yields the following decreasing stream of benefits over a five-year period: $5000, $4000, $3000, $2000, $1000. Project B is built in two years. Initial costs are $10,000 in the first year and $5000 in the second year. It then yields yearly profits of $6000 for the next four years. Which of these investment projects is preferable?
Parents of two teenagers, ages 13 and 17, deposit a sum of money into an account earning interest at the rate of 7% per year compounded annually. The deposit will be used for a series of eight annual college tuition payments of $10,000 each. Payments out of the account will begin one year after the initial deposit.
You are deciding whether to buy a new or used car (of the same make) and how many years to keep the car. You want to minimize your total costs, which consist of two parts: the loss in value of the car and the repairs. A new car costs $20,000 and loses 20% of its value each year. Repairs are $400 the first year and increase by 25% each subsequent year.
The relative growth rate of a population, P, over a time interval, Δt, is given by
In exponential growth, the relative growth rate is a constant. Although exponential growth is often used to model populations, this model predicts that a population will increase without limit, which is unrealistic. In the 1830s, a Belgian mathematician, P. F. Verhulst, suggested the logistic model, in which the relative growth rate of a population decreases to 0 linearly as the population increases. Verhulst's model predicts that the population size eventually levels off to a value known as the carrying capacity.
To see how Verhulst's logistic model works, assume that a pair of breeding rabbits is introduced onto a small island with no rabbits. At the outset the rabbit population doubles every month. This means that initially the relative growth rate is 100% per month. Eventually, though, as the population grows, the relative growth rate drops down to 0% per month. Suppose that the growth rate reaches 0 when the population reaches 10,000 rabbits. (Thus, 10,000 is the carrying capacity of the island.) Using spreadsheets, we will model the rabbit population over time.
(b) Using your new formula for the relative growth rate, r, let's see how different initial populations of rabbits lead to different logistic curves. Model the following scenarios, over a 5-year (60-month) period: a population starting at 100 rabbits, a population starting at 5,000 rabbits, a population starting at 12,500 rabbits, and a population starting at 17,500 rabbits. Place all of your data on the same chart. What happens to the rabbit population when it starts out above the island's rabbit carrying capacity? Why does this make sense?
The spread of information through a population is important to policy makers. For example, agricultural ministries use mathematical models to understand the spread of technical innovations or new seed types through their countries.
In this project, you will compare two different models—one of them logistic —for the spread of information. In both cases, assume that the population is 10,000 and that initially only 100 people have the information. Let N be the number of people who have the information at time t.
Model 1: If the information is spread by mass media (TV, radio, newspapers), the absolute rate, ΔN/Δt, at which the information is spread is assumed to be proportional to the number of people not having the information at that time. If t is in days, the constant of proportionality is 10%. For example, on the first day, the number of people not having the information is 10,000 − 100 = 9900. Since 10% of 9900 is 990, the rate of spread of information is 990 people per day on the first day. This means that on the second day, the number of people not having the information is 8910 and that the rate of spread is 10% of 8910, or 891 people per day, and so on.
Model 2: If instead the information is spread by word of mouth, the absolute rate at which information is spread is assumed to be proportional to the product of the number of people who know and the number of people who do not know. If t is in days, the constant of proportionality is 0.002%. For example, on the first day, the product of the number of people who know and the number of people who do not know is 100 · 9900 = 990,000. Since 0.002% of 990,000 is about 20, the rate of spread of information is 20 people per day on the first day. This means that on the second day, the product of the number of people who know and the number who do not know is 120 · 9880 = 1,188,600, giving a rate of spread of 0.002% of 1,188,600 or about 24 people per day, and so on.
During World War I, a particularly lethal form of flu killed about 40 million people around the world.18 The epidemic started in an army camp of 45,000 soldiers outside of Boston, where the first soldier fell sick on September 7, 1918. In this problem you will make a spreadsheet for the SIR model of the 1918 flu outbreak. Starting from the initial values S0 and I0, for any increment in time Δt, the changes in the number of susceptibles and infecteds are approximated by
16From Graeme Bird.
17Geometric series are discussed in detail in chapter 10.
18“Capturing a Killer Flu Virus,” J. Taukenberger, A. Reid, T. Fanning, in Scientific American, Vol. 292, No. 1, Jan. 2005.
3.141.47.25