Using the table below:

In Excel, to determine an annuity serial payment on a loan amount of $200 million, click on the Formulas tab, then choose Financial and then click on PMT. You should get a box with several options in it. Enter the interest rate for the loan in the first box (7%). Enter the number of payments over the loan period (30 years).

Next enter the loan amount ($200 million).

In the next box enter the ending amount after you finish paying off the loan ($0).

Finally enter whether you will pay at the beginning of the year or at the end of the year (lets pay at the end, 0). The equation should look like this: =PMT(7%,30,200000000,0,0)

This will give us the amount that has to be paid at the end of each year to pay off the loan in thirty years. In this case: ($16,117,280.70).

Now figure out a payment schedule for the thirty-year period, see the table below:

In the first row enter all of the headings.

In the second row enter the value of the loan amount in the 5th column.

Starting in the third row, in the first column, enter in each year of the loan.

In the fourth column enter in the total payment for each year (from above).

In the third column, enter the amount of interest paid for that year, by multiplying the loan amount remaining by the interest rate. In year 30, the amount should be $1,054,401.55 and the sum of the interest should be $283,518,421.21.

In the second column enter the principle paid for that year by subtracting the interest amount from the total payment for that year.

In the fifth column calculate the balance remaining on the loan by subtracting the principle paid down from the previous years loan amount. Continue for 30 years. (15 points)

Year Principle Interest Total Payment Principle Remaining $200,000,000.00 S 2,117,280.70 $ 14,000,000.00 $16,117,280.70$ 197,882,719.30 14 16 17 18 21 24 27 0$15,062,879.15 $199,999,999.79 $16,117,280.70 0.21 Total