MATH 215Intro to Financial MathematicsSpring 2016

Lab 5: AMORTIZATION spreadsheet

Part I: Constructing amortization spreadsheet

  1. Merge A1, B1, and C1. Then type in: “loan parameters”
  2. Widen columns E, F, G, H, and I.
  3. Merge E1, F1, G1, H1, I1 and type in: “output variables”
  4. Cell A3: type in “years”
  5. Cell B3: type in 25
  6. Cell A4: type in “rate/year”
  7. Cell B4: format as percentage and then type in “6”
  8. Cell A5: type in “loan amt.”
  9. Cell B5: format as currency and then type in “100,000”
  10. Merge E3 and F3 and then type in “monthly payment”
  11. Cell G3 type in the equation below:
  1. Type in the following words: “number “ in cell E5, “payment” in cell F5, “principal” in cell G5, “interest” in cell H5, and “balance” in cell I5
  2. In cell E6 type in the number “1”
  3. In cell E7 type in the equation: =1+E6
  4. Select cells E7 down through E305 and then use fill down (instructor demonstrated)
  5. Format Cells F6 through I6 as currency
  6. In cell F6 type in the equation below (IF is in the LOGICAL menu under the FORMULAS tab):
  1. Select F6 down through F305 and fill down.
  2. In Cell H6 type in the equation:
  3. In Cell G6 type in the equation:
  4. In Cell I6 type in the equation:
  5. In Cell H7 type in the equation:
  6. In Cell G7 type in the equation:
  7. In Cell I7 type in the equation:
  8. Finally, select G7 – I7 down to G305 – I 305 and fill down
  9. Now check the calculations in the spreadsheet by hand to eliminate any bugs.

Part II: Using amortization spreadsheet

  1. Go to select a property you like. Briefly describe why you choose it.
  2. Look up online for current mortgage interest rates. Is it annual or monthly?
  3. For each of the questions below do calculations on a separate tab of one excel spread sheet. Make sure to highlight all important numbers.
  • Enter the information into the amortization spreadsheet you created earlier. Make sure to edit not only the loan amount, but loan term and interest rate.
  • Study different scenarios:
  • Repaying loan in 30 years with monthly payments;
  • Repaying loan in 20 years with monthly payments;
  • Repaying loan in 30 years with biweekly payments. Assume that you will make regular and level (i.e. same amount every time) payments.
  • Repaying loan in 30 years with biweekly payments. Assume that you will make regular and level (i.e. same amount every time) payments.
  • Repaying the loan 30 years with monthly payments plus $200 each month toward the principal.
  1. Which scenario is the best for the borrower? What is a criteria to determine which way is better for the borrower?
  1. Describe your choice, state interest rate and answer the questions above in the paragraph form. Attach Excel spreadsheets with calculations (highlight important numbers). Turn in on Friday, March 4th.