Amortization Schedule One of the most common types of personal loans are amortized loans. In order to repay the loan, the borrower will make fixed payments every period. A portion of the payment is comprised of an interest payment to the lender and another portion reduces the principal balance of the loan. An amortization schedule shows the payment amount, amount of interest paid, amount of principal paid, and the balance remaining for each payment.
Objective:
You are looking into getting a mortgage and have gotten quotes from two different banks. The principal of the mortgage for both quotes is $125,000. Bank A is offering you a traditional amortized loan with a 15 year term, monthly payments, and fixed annual rate of 5% (where the borrower pays off the loan with 180 equal monthly payments). Bank B is offering you a 15 year amortized loan with a variable interest rate. With this loan, the annual rate is only 2%, but when making your model you're going to assume the 2% rate will only last for the first 5 years and then for the last 10 years you will be charged an annual rate of 9%. Bank B's loan also has monthly payments.

Construct your model so that the user can update the loan amount and the models update correctly. The user should also be able to change the annual interest rates for each bank (Bank A 5%, Bank B 2% first 5 years and 9% last 10 years) and have the model update correctly (the time to maturity will always remain fixed at 15 years and the timing of the interest rate change for bank B will always occur after year 5).
What to Do:

Construct an attractive, easy to use spreadsheet that displays the amortization schedule for the loan from Bank A and a separate amortization schedule for Bank B. Have the loan inputs clearly provided at the top of the sheet and the model constructed so that the user can easily change the annual interest rates and loan amount. As outputs the user should be able to clearly identify: the monthly cash flows(payments), amount of interest paid, amount of principal paid, and the outstanding balance at the end of each period over the life of each loan.
Each line of the amortization table should contain the following information:
-A sequence of numbers (from one to n, where n is the total number of months of the loan)
-The beginning of the period loan amount outstanding
-The monthly payment
-Interest paid that month
-Reduction in principal for that month
-The outstanding balance of the loan at the end of the month

Answers to each question bullet points.
1. If the borrower kept the loan for the full term, how much total interest did the borrower pay on the loan from Bank A?
2. If the borrower kept the loan for the full term, how much total interest did the borrower pay on the loan from Bank B?
3. Why may you want to know how to construct or interpret an amortization schedule? 4. What happens if you change the input variables (higher or lower loan amount or rate)?
5. What problem happens with our model when the length of the loan changes (shorter or longer)? If you change this variable, does it update correctly?
6. When purchasing a home, what are some of the additional costs that should be considered?
7. What may impact your decision to get a 15 year versus 30 year loan?
8. What may impact your decision to get an adjustable rate versus fixed rate?