Assignment 10

You should type answers to the questions below (except where noted otherwise), as well as paste tables/graphs/charts, in one MS Word document, which has the name:
ma103Assignment09YOURNAME. Saving in MS Word should automatically give this file the proper extension of .doc or .docx. You should upload this file in Moodle prior to the deadline. Clearly label which Activity and problem you are answering. It might be helpful to put in a "Page Break" between the answers for different Activities, where appropriate.

When you are asked below to create a graph or a table in Excel, you may simply paste the graph or table into your Word document. You do NOT need to import the entire worksheet associated with it.

Note that the data used in the Activities is newer than the data provided in the textbook or the Excel instructions provided.

Friday, October 16

A. Read Topic 9 in the textbook, and work the assigned Exploration problems as listed for each day on the course web-site. You do NOT need to turn in the Exploration problems, but they are useful in preparing for the written exam.

B. Activity 9.1, Mortgages
In this activity, you will investigate some considerations that arise when looking for a home mortgage loan. You will determine basic characteristics of several options to compare the consequences of the various choices. Purchasing a home is a big decision. Generally, a down payment of at least 10 percent of the purchase price of the home is required. A lender will loan you the money for the rest of the purchase price but with additional closing fees. These closing costs include direct fees for things such as a home appraisal and title insurance and also fees for points charged; each point is 1 percent of the loan amount.

For a fixed rate mortgage, you are guaranteed that the interest rate stated when you sign the loan papers will not change for the term of the loan. With an adjustable rate mortgage (ARM), which is more attractive to the lender, your interest rate changes over time. ARMs are generally given as a fixed rate period and then an interval, such as 3/1, which means the rate is fixed for three years and then will change every year based on a rate index. In considering mortgage loan options, you will need to take into account the following:
(1) Interest rate
(2) Amount of the loan
(3) Closing costs, including points
(4) Fixed rate or adjustable rate mortgage
(5) Any prepayment penalties.

You have decided to purchase a home and have enough money for a decent down payment as well as the money to cover direct fees for closing costs (which you’ll assume do not vary much from one bank to another). You are considering several mortgage options and want to evaluate them. Not counting points, you will need a loan for $130,000. If a mortgage option requires you to pay points, you will need to add that money to your loan amount. Assume the mortgage options you are considering have no prepayment penalties.

1. Lender I offers you a fixed rate 15-year mortgage at 5.25 percent APR, compounded monthly, with no points.
1. a. Find your monthly payments under this option, using the formula (it's in the notes as well as textbook).

1. b. Find the total amount of interest (the money above and beyond the amount you borrowed) you will pay over the life of the loan.

2. Lender II offers you a fixed rate 30-year mortgage at 5.75 percent APR, compounded monthly, with one point.
2. a. Find your monthly payments under this option, using the formula (it's in the notes as well as textbook). Don’t forget to add the “point” to the loan amount.

2. b. Find the total amount of interest (the money above and beyond the amount you borrowed) you will pay over the life of the loan.

3. Excel has a special function that computes the payment required on a loan. Here are the instructions to use this function to check the previous computations. Be sure to enter the answers to #3.f. and #3.g. into your MS Word document.

4. Suppose you think that the payments required for the 15-year mortgage are too high for you to afford, but that you’d be able to afford higher payments than the 30-year mortgage requires. You are considering obtaining a 30-year mortgage at the offered rate but paying it off sooner by making larger payments than are required.
4. a. Use Excel to find the monthly payments needed to pay the loan off in 20 years and record the monthly payment in your MS Word document. The point of interest should be added to the loan amount in this problem.

4. b. What monthly payments would you need to make in order to pay it off in 25 years? The point of interest should be added to the loan amount in this problem.

5. You want to compare how much interest is paid during the first two years under each lender’s plan. Excel’s function to calculate interest can help. Be sure to enter into your MS Word document the answers to the questions below. When you are done following all the instructions in #5 below, you should enter the resulting table into your MS Word document.

6. You have another mortgage option—Lender III offers you an adjustable rate 30-year mortgage, with no points, at 4.95 percent interest for the first year. After that time, the mortgage rate will likely change, but you don’t know what the new rate will be. Find your monthly payments for the first year under this option and enter that value into your MS Word document.

7. Let’s suppose that under Lender III’s adjustable rate mortgage option, the interest rate increases by 1 percent for the second year. Calculating interest paid for the first two years under Lender III’s plan is more complicated. Here’s one way to do it.
7. a. First, use a method similar to what you did for Lenders I and II to find the interest paid for each month of the first year under Lender III’s plan and record that total in your MS Word document:

Total interest paid during the first year (as opposed to over the whole loan) under Lender III’s plan: ________

7. b. Now use your work in #6 to find the total payments made under this plan during the first year; then find the amount of principal paid during the first year and the total principal remaining. Record those values in your MS Word document:

Total principal paid during the first year (this depends on the total payments and total interest during the first year): ___________________________
(Note that when calculating this number, if Excel gives you interest as a negative number, either in red or in parentheses, then be careful if you subtract).

Total principal remaining: ______________________________________

7. c. Finally, use the IPMT function with the new principal remaining as the amount of the loan, the new interest rate of 5.95 percent, and 29 years remaining on the loan, to calculate the interest paid during each month of the second year of Lender III’s plan. Find the total interest paid during the two years under Lender III’s plan and record that total in your MS Word document:

Total interest paid for the first two years under Lender III’s plan: _______

Please paste the portions of your Excel table used to answer these questions into your MS Word document.

8. Consider the monthly payments, the total interest, and any other factors that you think are important and discuss the pros and cons of each of the lender’s plans. Which lender would you choose and why?

Summary
In this activity, you learned how to use Excel’s loan payment and interest calculation functions. You learned how to copy and paste values so they are no longer linked to cells that may change. Finally, you examined and compared several realistic options for home mortgage loans.

Wednesday, October 21

C. Read Topic 14 in the textbook, and work the assigned Exploration problems as listed for each day on the course web-site. You do NOT need to turn in the Exploration problems, but they are useful in preparing for the written exam.

D. Activity 14.1, Methods of Apportionment
In this activity, you will work with the two quota methods of apportionment: Hamilton's method and Lowndes' method. You will use each of these methods to determine how many representatives in the U.S. House would correspond to each state if the method were currently used.

All apportionment methods assign to each state a number of representatives based on the state's population. The file below contains the population of each state, as given by the Dec. 28, 2000 census figures.

1. Open the file EA14.1 State Population.xls and then find the total U.S. population. To do this in Excel, you want to add all the state's populations, which are the numbers in cells B2 through B51. To do this, enter
=SUM( B2 : B51)
in cell B53. Name this cell using the Name box above column A on the spreadsheet. (You could use the name totalpopulation, or any other appropriate name). Also, in cell A53, type the text label, Total Population = . Enter the total population in your MS Word document.

2. In cell A54, enter the label Total Seats = and in cell B54, enter the number 435; this is the total number of seats in the House of Representatives. Name this cell. You might want to call it totalseats, or another appropriate name. Recall that the standard divisor is given by:
standard divisor = total population / total number of seats

Ask Excel to find the standard divisor in cell B55 and name this cell. Add the label Standard Divisor = in cell A55.

Enter the standard divisor in your MS Word document.

3. Recall that a state's standard quota is defined by the equation:
standard quota = state's population / standard divisor

In column C, enter each state's standard quota. First, enter the column title Standard Quota in cell C1. Then ask Excel to do the computations for you by entering the appropriate formula in cell C2. Drag-and-fill to enter the rest of the values.

4. Can each state's standard quota be the apportioned number of representatives for that state? Why or why not?

In both quota methods, apportionment is done by rounding up or down each state's standard quota.
In Hamilton's method, each state is initially given the number of representatives equal to the integer value of the state's standard quota, unless this number is 0. For this case the initial and final apportioned number of seats for that state is 1 (so that every state is represented).

5. Suppose that three states, State A, State B, and State C, have standard quotas as given in the table below. Fill in the last column of the table for the initial number of representatives apportioned to those states when using Hamilton's method. Please copy and paste this table into your MS Word document.

After the initial apportionment is done using the intege value (or 1 when the integer value is 0), the remaining seats are assigned to the states with a standard quota larger than 1. The states with the largest fractional part of the standard quota are each given an extra seat.

6. Suppose the total number representatives that correspond to the three states is 7. Record in the table the number of representatives (seats) that correspond to each of the three states using Hamilton's method. Please copy and paste this table into your MS Word document.

You will now use Excel to determine the number of representatives apportioned to each of the 50 states under Hamilton's method.

7. In column d of your spreadsheet, you'll ask Excel to enter the initial number of seats for each state; that is, enter the integer part of the state's standard quota, unless this is 0. If the integer part of the standard quota is 0, then you'll enter 1. To do this, enter the integer value of the first state's standard quota in cell D2 by typing =INT(C2) then drag to autofill. If column D contains any 0's, go through and replace each 0 with a 1. Label column D with an appropriate label.

Use and appropriate formula to enter in column E the difference of each entry in column C minus the corresponding entry in column D. Label column E Fractional Part. In your MS Word document, type the names of the states for which the entries in column E are negative. Explain why these are negative. Please copy and paste the spreadsheet into your MS Word document also.

8. Add the numbers in column D (use the =sum( ) function in Excel) to decide how many seats have already been apportioned. Enter that number into your MS Word document.

Determine how many seats remain after the initial apportionment. Enter that number into your MS Word document.

This number of remaining seats is how many you will apportion using the fractional part of the standard quota.

9. To apportion the remaining seats using the fractional part of the standard quota, sort the data in columns A, B, C, D, and E by "Fractional Part" in descending order.

In column F, enter the final number of seats apportioned by Hamilton's method to each state. (Note: you do not need to enter those numbers one by one, you can use a formula. Enter =D2 + 1 in cell F2 and drag until you have apportioned all the remaining seats: then have Excel copy the numbers in column D for the remaining states.) Label column F Seats Using Hamilton's Method. Then sort the data (remember to highlight all columns) by "Seats Using Hamilton's Method," to help answer parts (a) and (b) of this question.

9. a. List the four states that get the most number of seats and say how many seats each of them gets.

9. b. What is the smallest number of seats assigned? Which states get the smallest number of seats?

Lowndes' method also uses the standard quota to apportion representatives. However, Lowndes' method uses the relative fractional part of the standard quota instead of the fractional part. The relative fractional part is used to determine which states' standard quotas will be rounded up to fill out the remaining seats.

The relative fractional part is the following quotient:
relative fractional part = fractional part / integer part

10. Assuming that State A and State D have the following standard quotas, record their relative fractional parts in the table, and copy and paste this table in your MS Word document.

To apportion the number of seats for each of the 50 states by Lowndes' method, you will first compute the relative fractional part of each state's standard quota.

11. Insert two columns to the LEFT of column F in your spreadsheet. In the new column F, recompute the integer part of the standard quota for each state. (You must recompute the integer part of the standard quota, since you'd modified column D following the directions in #7 above). In cell G2, compute the relative fractional part of the standard quota for the state in row 2. Drag-and-fill to compute the relative fractional part for each state, and label column G Relative Fractional Part.

12. Notice that you see #DIV/0! in several places in column G. Explain why this error message occurred and what it means.

13. First, sort the data by "Relative Fractional Part," in descending order (the #DIV/0! error messages should be at the top). Next, label column I, Seats Using Lowndes' Method and and start filling this column by giving one seat to each state showing the error message. Explain why you need to give each of these states one representative.

14. Finally, apportion the rest of the seats, filling column I, using Lowndes' method. Remember that you will start with the integer values in column F, and then use the relative fractional part to determine which states get one additional seat. Once your apportionment is complete, you should answer the questions below. They might be easier to answer if you sort the data by the number of seats apportioned by Lowndes' method.
14. a. List the four states that get the most number of seats and say how many seats each of them gets.

14. b. What is the smallest number of seats assigned? Which states get the smallest number of seats?

Please copy and paste the resulting spreadsheet into your MS Word document. For formatting purposes, you only need to include three columns: State, Seats Using Hamilton's Method, and Seats Using Lowndes' Method.

15. Name the states that would prefer Hamilton's method and those that would prefer Lowndes' method. Explain why you chose these states.

16. Which states, those with a large population or those with a small population, are favored by Lowndes' method? Please give an explanation, appealing to the math involved, why this is the case.

Summary
In this activity, you used Excel to investigate two quota methods of apportionment and compard the results of using them to apportion the seats of the House of Representatives. You learned the Excel command that gives the integer part of a real number and used it to find the fractional parts.