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

  1. (By-hand) 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. As I make my way around, show me your calculator with the answer of 546.20 on it or ask me any questions.
  2. 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].
  3. For homework you computed some by-hand computations related to a car loan. Notice a lot of similar info up top in Excel and in the amortization table. If you click on a box with a number in it, you can see Excel's formula for that in the formula bar. In B3 =PMT(D1,360,-B2) is Excel's formula for calculating our loan payment formula. Write down the formulas in D1 and D3 and make sure you understand them. Ask me any qustions you have about computing these or the amortization table by hand.
    condo cost monthly rate
    loan amount 1st years total interest
    payment =PMT(D1,360,-B2) total interest 30 years
    month # end of month payment interest paid that month principal paid that month loan balance
  4. Scroll to the last months payment of the 30 year loan (month 360 in the A column) to see the amortization ("killing the dept") occur. What is the final balance (negative or 0)?
  5. 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?)
  6. 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 left of it, 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 CHECK FIRST:
    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 #9 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.

  7. 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).
  8. 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 all + row           +
    First row with a -         -
    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 x the number of months paid in col A - the amount we overpaid on the last month in col E.
    Use the first row with a negative in it to compute this and show work.
  9. 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.
  10. Which option has a lower monthly payment?     Option 1     Option 2
  11. Which option pays less total interest ($) over the life of the loan?     Option 1     Option 2
  12. Which option has a lower loan balance at the end of year 2?     Option 1     Option 2
  13. Which option has a lower loan balance at the end of year 5?     Option 1     Option 2
  14. 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!
  15. 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, find a Fair Purchase Price and write it down. If you can't find that, copy down an Invoice or MSRP price. Also write down the model, make and year.
  16. 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 rate for a car now, for a 6 year loan, and write down the rate.