Condo and Car Purchases: Decisions, Decisions (Part 1)

    Go to this lab on the class highlights page, control-click on this excel file, and open condocarlabsols.xls, which represents an amortization table [all the numbers are true to life except the price of the condo].
  1. Write down the loan payment formula with numbers filled in for an 84212 loan for 30 years compounded at 6.75% monthly and calculate this on your calculator. Show me your calculator with the answer of 546.20 on it.
  2. Next look at the Excel formulas in the formula bar B3 (Excel's formula for calculating our loan payment formula), D1, and D3 and write them down. Ask me any questions.
  3. Scroll to the last months payment of the 30 year loan to see the amortization ("killing the dept") occur. What is the final balance (negative or 0)?
  4. Recall that the monthly interest ($) is calculated by taking the outstanding balance and multiplying by the monthly rate. So why does the interest ($) paid each month, in column C, decrease as time increases (Hint: the monthly rate stays the same, but what happens to the outstanding balance, and how does this impact the interest?)
  5. Compare the filled in numbers of ONLY THE FIRST ROW with your Excel document, and then fill in the rest of only the FIRST ROW of Table 1 with NUMBERS from the highlighted portions of the Excel document. For the year 2 (24 months) and year 5 balances, be careful to look in the A column for the proper month (NOT the gray markers, which are numbered differently than the month - for example, grey 24 is only month 19) and then look at the corresponding E value for the loan balance; I have highlighted these in yellow to help you find them and I've included the correct year 5 balance to ensure you have the correct numbers!
    TABLE 1
    monthly
    payment
    1st yr total
    interest
    Year 2 balance Year 5 balance total interest paid
    Original Info $546.20

    $79,054.61 $112,419.07
    Option 1
    Smaller Loan



    $77,177.10
    Option 2
    Lower Rate



    $78,601.16
    Option 2 + $20
    Pay Extra $20



    $77,196.75 See #8 first:
    Before I finalized all the bank payment numbers, I found out that a deceased relative was leaving me some money, which would arrive before I closed the loan. The vice president gave me two options.
    Option 1: Take out a smaller loan I could reduce the loan amount by $2000 (ie change B2 to read =.8*B1-2000 in the FORMULA BAR and then hit enter - do not change the amount in the B2 box itself - it is much easier to change it in the formula bar). Notice that this will change the rest of the amortization table. Verify that you have the same year 5 balance as in the chart and then fill in ONLY THE SECOND ROW of table 1 with numbers.

    Option 2: Buy down the rate I could leave the loan amount the same, and pay to reduce the rate from 6.75% down to 6.25%. So change D1 to read =.0625/12 by doing this in the FORMULA BAR after clicking on D1. Click on B2 and change it back to =.8*B1 in the FORMULA BAR. Verify that you have the same year 5 balance as on the table and then fill in ONLY THE THIRD ROW of table 1.

    Paying extra each month on option 2. If I choose option 2, then I have a lower monthly payment. Perhaps I can afford to pay more per month. Let's investigate this to see what happens. Using option 2, we'll see what happens if I can pay an extra $20 each month. Namely, b3 changes via adding +20 at the end to =PMT(D1,360,-B2) + 20. Do NOT fill anything in on paper yet.

  6. What month do I finish paying off the loan? (it will be less than month 360 since I'm paying more than is required - look for the first line with a negative loan balance (in red).
  7. Notice that the formula in D3 will no longer be correct since we are not paying for 30 years. In fact, a zero balance does not coincide with a specific month so we need to be a little more clever. Look at the just the part of the Excel chart that shows the loan balance changing from an entire row of positive (black) to a row that contains the first negative amount (red), copy the last positive (black) row and the first negative (red) row down here in table 2 (but NOT in table 1), and then use the information as specified below.
    TABLE 2 Month # in col A Monthly Payment Interest Paid that Month Principal Paid that Month Loan Balance Remaining
    Last + row           +
    First - row         -
    We pay our monthly payment for the exact number of months that it takes for the chart to turn to negative (red) when we have finished paying off the loan BUT we receive money back (the amount in red) since we have overpaid on this last month. Exactly how much total do I end up paying over the life of the loan when I pay this extra $20 each month? The total paid will be the monthly payment times the number of months paid - the amount we overpaid on the last month. Show work.
  8. How much interest do I pay over the life of the loan now? The interest will be the total you paid (your last answer) - loan amount (84212). Show work and then fill in the FOURTH ROW of table 1.
  9. Which option has a lower monthly payment?     Option 1     Option 2
  10. Which option pays less total interest ($) over the life of the loan?     Option 1     Option 2
  11. Which option has a lower loan balance at the end of year 2?     Option 1     Option 2
  12. Which option has a lower loan balance at the end of year 5?     Option 1     Option 2
  13. Would you have chosen to buy down the rate or instead used that money to take out a smaller loan?
    Buying versus Leasing a Car: Next week we will use the same Excel file to look at decisions related to purchasing a car, but today you will choose a car to "purchase". You might imagine you won that lottery or be a bit more realistic-your choice!
  14. Click on this Kelly Blue Book link. Click on Car Values in the top menu bar, and then follow the directions to choose a model, a make, and a year of a new car. You might also need to select a trim or other options and enter a zip code (28608 or your home zip code will do). After you Configure your Car, click Fair Purchase Price Copy the Total Fair Purchase Price and the Out-the-Door Target Price, as well as the model, make and year.
  15. Take out your real-life rates homework and write down an interest rate for a car loan. If you did not find one previously, search for a bank loan for a car now, for a 6 year loan.