A Loan Example

• An installment loan is where you borrow
  • A specific amount of money (P, the principal)
  • For a fixed number of payments per year (n)
  • For a fixed number of years (t)
  • At a fixed interest rate (r)
• If you take MATH104, I care that you can use the formula
  • 
• But in CS104 I would really rather have you forget the formula.
• Typical installment loans
  • A car loan
  • A boat loan
  • A mortgage or house loan.
  • Student loans
• Today I want to do two things with respect to loans
  • Compute the monthly payment for a loan.
    • Compute a required down payment.
    • Compute an amount financed.
  • Build an amortization table, or show how the loan will be paid off.
• In excel we will learn
  • The payment formula
  • Working with percents.
  • Absolute vs relative formats
  • Basic fill.
  • Basics of working with tables.
• We will work with payments again, and you WILL see these on the tests.
• This is a standard setup in my opinion.
• I would like to buy a sailboat
  • Price : $20,000
  • Required down payment: 15%
  • Interest rate: 6.75%
  • Time: 10 years
  • Payments per year: 12
• Enter these terms:
  • 
• In cell B3, compute the down payment (B1*B2), what is this? where does it come from? where does it go?
• In cell B5, compute the amount financed (B1-B3), what is this? where does it come from and where does it go?
• In cell B10, compute the monthly payment
  • We will use the pmt function.
  • This function requires three parameters.
    • 
    • A parameter is a value the function needs to perform a computation.
    • Think variables (r,n,P,t)
  • The payment function takes
    • The periodic interest rate. r/n
    • The number of payments (n*t)
    • The amount financed, as a negative.
    • 
  • Just learn this, memorize it or whatever. I want you to know it.
• Do this with the insert function button
  • 
• In cell B12, compute the total payments (Monthly payment * payments per year * years)
• In cell B13, compute the total interest paid (Total Payments - Amount Financed)
• In cell B14, compute the total paid for the boat (Total Payments + Down Payments)
• 
• 
• Payoff Table
•