The example exercise is to work through a loan amortization example using Excel. Go to the Exercise 1 worksheet. The example loan conditions to be entered into the Loan Terms table are: Loan amount borrowed (principal or pv) $300,000 Loan interest (rate) is 6.75% Loan term (number of payments or nper) is 9 years Annual payments of principal and interest to be found in part 1.

a) Calculate the annual loan payment using the PMT function in Excel. The PMT function is in the Formulas tab under the Financial option. In the PMT Menu box, the Rate is the interest rate, Nper is the number of payments or term, and PV is the principal amount borrowed (enter this as a negative value). FV and Type should be blank or you can enter 0. Reference the appropriate cells for the required entries.

b) Enter the needed formulas in the Loan Amortization table given to calculate the interest payment and the principal payment for each period payment. 1st, Calculate the interest payment as follows: Interest payment = period interest rate * the outstanding loan balance. 2nd, Subtract the interest payment from the PMT you calculated to calculate the amount paid on principal. 3rd, Subtract the principal payment from the previous period outstanding balance. 4th, copy and paste the formulas for the 5 payments. 5th, enter formulas to sum the totals in your table.

1) Calculate the total amount paid (Principal + Interest) using values in Term (cell C6) and Loan Payment (cell C7).

2) Use the Excel IPMT formula to calculate the interest payment for payment number 3 in cell G9. Again, enter PV as a negative value.

3) Use the Excl PPMT formula to calculate the principal payment for payment number 4 in cell H10.

4) Are the results of 1, 2 and 3 the same as calculated by your table?

Exercise 2 Go to the Exercise 2 worksheet. Lets assume you just graduated college and want to purchase a house. Your dream home is a 4 bedroom, 2 bath, with 2,200 square feet and is listed at $275,000. 2 Assume you have enough money to put a 5% down payment ($275,000 * 5% = $13,750). You will qualify for a 30-year fixed mortgage of 8.05% APR. You will make monthly payments for those 30 years. Fill in the Loan Terms table and make a loan amortization table for financing the house in the home loan worksheet. When filling in the Loan Terms table, the periodic interest rate and the repayment term become: Monthly interest rate = APR / 12 Term in months = Number of years * 12

a) What is the monthly payment?

b) How much total interest will you pay when paid in full according to the loan terms?

c) How much principal will you pay when paid in full?

d) How much will the house cost in total (principal + interest) when paid in full.

e) What is the interest payment for period 12 using the excel IPMT function?

f) What is the principal payment for period 12 using the excel PPMT function?

g) How much money would you save in interest over the life of the loan if the interest rate was 4.25%, instead of 8.05%?

h) How much money would you save in interest over the life of the loan if the down payment was 10%, instead of 5% (using 8.05% interest rate)?

i) How much money would you save in interest over the life of the loan if an additional $250 was paid towards principal each month (using 8.05% interest rate and a 5% down payment)?

j) How many months faster would the loan be repaid if an additional $250 was paid towards principal each month (using 8.05% interest rate and a 5% down payment)?