C SPREADSHEET PROJECTS

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.

1. MALTHUS: POPULATION OUTSTRIPS FOOD SUPPLY

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 19^th 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.¹⁶ 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 21^st 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.

2. CREDIT CARD DEBT

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?

3. CHOOSING A BANK LOAN

A local bank offers the following loan packages. Use a spreadsheet to decide which option is the best. The packages are as follows:

• A loan of $2000, at an annual rate of 9%, payable in 24 monthly installments.
• A loan of $2000, at an annual rate of 10%, payable in 36 monthly installments.
• A loan of $2000, at an annual rate of 9.25%, payable in 52 biweekly installments.

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.

1. Use a spreadsheet to decide which loan is cheapest in terms of total payoff to the bank. (See the following hint.)
2. Use a spreadsheet to decide which loan is easiest to afford in terms of lowest monthly payment. (See the following hint.)

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)² = $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.

4. COMPARING HOME MORTGAGES

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:¹⁷

1. Using the information given, as well as the fact sheet you got from the bank, determine which loan (30-year, 30-year biweekly, 20-year or 15-year) is best if you intend to live in your house for the full term of the loan. Assume the best mortgage is the one that ends up costing you the least overall. (The situation in real life can be more complicated when points and taxes are considered.) Although it's possible to work this problem without a spreadsheet, you might want to set one up anyway.
2. Banks usually require that the monthly payments not exceed some stated fraction of the applicant's monthly income. For this reason, it is generally easier to qualify for loans with smaller monthly payments. Thus, it may be that the “best loan”—the one you found in Question 1—is not the “easiest” loan to qualify for. Which of the loans on your fact sheet has the lowest monthly payment? The highest?
3. Suppose that you expect to sell your house for $145,000 in five years. In this case, which loan should you take? [Hint: The goal here is to maximize profit. Figure out how much money you have paid to the bank after five years, and your remaining debt at that time. When you sell your house, the remaining debt is paid to the bank immediately, so your total profit is (Selling price of house)–(Loan payoff to bank)–(Amount paid to bank during first five years).]
4. Derive the formula for the monthly payment, x. [Hint: Set up a geometric series in terms of x and r to give your loan balance after n months. The loan balance equals 0 when you have paid off your loan; use this fact to solve for x. Simplify the resulting expression (by summing a geometric series) to get the formula given for x.]

5. PRESENT VALUE OF LOTTERY WINNINGS

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?

1. Compute the present value of the money paid out by the Lottery Commission, assuming annual discount rates (interest rates) of 5%, 10% and 15%. In each case, what percent does the present value represent of the face value of the prize, $26,680,940?
2. What discount rate would result in a present value of the payments worth only half the face value of the prize?
3. Graph the present value of the payments against the discount rate, ranging from a rate of 0% up to 15%. Describe the graph. What does it tell you about why the Lottery Commission does not pay the prize money up front?

6. COMPARING INVESTMENTS

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?

1. Compute the present values of both projects assuming an annual discount rate (interest rate) of 4%. Which project seems preferable? [Hint: Treat expenditures as negative and income as positive.]
2. Compute the present values of both projects assuming an annual discount rate of 16%. Which project seems preferable now?
3. Describe in complete sentences why one of the investment projects is favored by a low discount rate, whereas the other is favored by a high discount rate.
4. The discount rate at which the present value of a project becomes zero is known as the internal rate of return. What is the internal rate of return of project A? Of project B? [Hint: Guess different discount rates until you find the one that brings the present value down to $0.]
5. Make a chart of the present value of the two investments against discount rates ranging from 0% to 30%. What features of this chart correspond to the internal rates of return of the two projects?

7. INVESTING FOR THE FUTURE: TUITION PAYMENTS

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.

1. Use a spreadsheet to model the savings account that the parents opened. At the end of every year, the account earns 7% interest, and then there is a $10,000 withdrawal. Determine what initial deposit provides just enough money to make the eight yearly payments of $10,000. Do this by guessing different values, and seeing which value leaves you with nothing exactly nine years later.
2. Having answered Question 1, use a spreadsheet to compute the present value of eight yearly payments of $10,000 each, beginning one year in the future, at a discount rate of 7%.
3. Compare your answer to Question 1 with your answer to Question 2. Is this a coincidence? Discuss.
4. Suppose the parents have only $50,000 to deposit into the savings account. What annual interest rate must the account earn if the eight payments of $10,000 are to be made? [Hint: Compute the present value of the payments for various discount rates.]

8. NEW OR USED?

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.

1. Set up a spreadsheet that gives, for each year, the value of the car, its loss in value, its repair costs, and the total cost for that year. The first two lines look like this, rounded to the nearest dollar:

   

2. Which year has the lowest cost?
3. You intend to keep your car 5 years. Compare your total costs for a new car and for a two-year-old car.
4. How old a car should you buy if you plan to keep it for 5 years?
5. Add two columns to your spreadsheet showing the average yearly cost for second-hand cars of different ages kept for 4 years and 5 years. Which is the best buy?
6. A new car costs $30,000 and loses 25% of its value each year; repairs start at $500 and increase at 10% per year. If you buy a seven-year-old car, should you keep it for 4 or 5 years? What is the average yearly cost in each case?
7. A car costs $30,000 when new. You buy a four-year-old car and keep it for 5 years; repairs are as in Problem 6. Find the rate at which it loses value if the average yearly cost is $2300.

9. VERHULST: THE LOGISTIC MODEL

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.

1. Let P be the population and r be the relative growth rate per month. Verhulst assumed that the relative growth rate decreases linearly as population increases. This means that r goes from 100% to 0% as P goes from 0 to 10,000 rabbits. Explain why the following formula for r corresponds to Verhulst's assumptions: r = 0.0001(10,000 − P).
2. Use a spreadsheet to model the monthly rabbit population on the island for the first two years (24 months). Graph the rabbit population over time. Describe the behavior of the rabbit population. [Hint: Start with two rabbits, and compute the growth rate using the formula in Question 1. Then, for each month, update the rabbit population as well as the relative growth rate.]
3. Draw a graph comparing your logistic model of the rabbit population to a population growing exponentially at a constant relative growth rate of 100% per month. Both models should start out with two rabbits. Describe the similarities and the differences between the two charts. What advantages does the logistic model have over the exponential model? (You'll have to be careful when setting the chart parameters; otherwise, all you'll be able to see is the exponential population, which climbs so quickly that the logistic one will not be visible at all.)
4. The key to the logistic model is that the relative growth rate is decreasing linearly as the population increases. However, this does not mean that the relative growth rate is decreasing linearly over time. Make a chart of the relative growth rate against time for the first two years. Describe the behavior of the relative growth rate over time.
5. (a) Different assumptions about the growing rabbit population lead to different logistic curves. In Question 1, we assumed that the relative growth rate was 100% initially, dropping to 0% when the population reached 10,000. This led to the formula r = 0.0001(10000 − P). Now assume that the initial relative growth rate is 10% (instead of 100%). What is the new formula relating r and P? (The carrying capacity is still 10,000, so r = 10% when P = 0, and r decreases to 0% as P increases to 10,000.)

   (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?

10. THE SPREAD OF INFORMATION: A COMPARISON OF TWO MODELS

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.

1. Using spreadsheets, compare the spread of information throughout the population using both models. Make a chart comparing both models' predictions of the number of people over time who have the information. Describe the similarities and differences between the two models. Why is Model 1 used when mass media are present? Why is Model 2 used when mass media are absent?
2. Which of the two models is logistic? How can you tell? What type of growth does the other model exhibit? How can you tell?
3. By definition, a population exhibits logistic growth if its relative rate of change is a decreasing linear function of the current population. Explain why Model 2 leads to logistic growth although it was defined in terms of absolute growth rates.
4. The solutions from our spreadsheets are only approximations. Discuss why this is the case. [Hint: There's more going on here than rounding error.]

11. THE FLU IN WORLD WAR I

During World War I, a particularly lethal form of flu killed about 40 million people around the world.¹⁸ 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 S₀ and I₀, for any increment in time Δt, the changes in the number of susceptibles and infecteds are approximated by

1. Choosing Δt = 0.1, a = 0.0003, b = 10, make a spreadsheet whose first few lines look like this:

   

2. How many soldiers got sick on the fifth day? How many were susceptible on this day?
3. Alter the spreadsheet so that it accepts any values of Δt, a, b input by the user.
4. Using the values a = 0.000267, b = 9.865 for the 1918 epidemic, decrease the value of Δt until a stable estimate is reached for the number of soldiers sick on September 16^th. How many soldiers had been infected by this date?
5. Approximately how long did it take for the 1918 epidemic to run its course?