Condo and Car Purchases: Decisions, Decisions

    Go to this lab on the class highlights page, control-click on this excel file, and open condolabsol.xls.
  1. 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?)
  2. Compare the filled in numbers of the FIRST ROW with your Excel document, and then fill in the rest of only the FIRST ROW of Table 1 with NUMBERS. 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.
    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
    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 - 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 and then fill in 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 C2 to read =.0625/12 by doing this in the FORMULA BAR after clicking on C2. Click on B2 and change it back to =.8*B1 in the FORMULA BAR. Verify that you have the same year 5 balance and then fill in 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, what happens if I can pay an extra $20 each month? Namely, b3 changes via adding +20 at the end to =PMT(C2,360,-B2) + 20.

  3. 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 part of the Excel chart that shows the loan balance changing from positive (black) to negative (red).
  4. Notice that the formula in C4 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 # End of Month Payment Amount Interest Paid that Month Principal Paid that Month Loan Balance Remaining
           
           
    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? This will be the monthly payment times the number of months paid - the amount we overpaid on the last month. Show work.
  5. How much interest do I pay over the life of the loan now? This will be the total you paid (your last answer) - loan amount. Show work and then fill in the FOURTH ROW of table 1.
  6. Which option has a lower monthly payment?     Option 1     Option 2
  7. Which option pays less total interest ($) over the life of the loan?     Option 1     Option 2
  8. Which option has a lower loan balance at the end of year 2?     Option 1     Option 2
  9. Which option has a lower loan balance at the end of year 5?     Option 1     Option 2
  10. 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: Let's use our Excel table in another situation which will probably be more relevant to you in the next few years - buying a car!
  11. Write down the make, model and year of the car that you have chosen from Kelly Blue Book.
  12. Notice that the Base Retail price is much higher than the Base Invoice price. The retail price is what is listed on the sticker, but people often negotiate. Assume that you have negotiated with the dealer and will pay $500 over the base invoice price (which is what I did with my last car). You must also pay the destination charge. What is the price of your car with the destination charge added to $500 over the base invoice?
  13. In North Carolina, you must pay a 3% "Highway Use Tax" whenever you buy a car. Add this to the price of your car to get your car's total price. How much is this? Show work. In B1 Click on B1 in your Excel sheet. You will see 105265 appear next to the = sign up at the top in the formula bar. Click just next to the = sign and change the amount to match your car's total price and then hit return.
    In B2 Click on B2 and then click up top next to the = sign. Assume that you will put 10% down on your car (since this is typical in real life). Then you will need a loan for 90% of the car's total price. You must change the loan to 90% of the car cost instead of 80%, so do that and hit return.
    Pull out your real-life rates homework and write down the rate for a car loan or search to find this.
    In C2 Click on C2 and then click up top next to the = sign. Change this to the rate for the car loan divided by 12.
    In B3, by going to the formula bar, change the 360 (the number of house payments) to 72 (the number of car payments in 6 years (72=6*12)) and remove the +20 at the end so that it reads =PMT(C2,72,-B2).
    In C4, by going to the formula bar, change 360 to 72. Fill in the chart below. The top row should contain numbers, while the 2nd row should contain your excel formulas.
    EXCEL
    MONTHLY
    PAYMENT    		 TOTAL INTEREST
    OVER THE LIFE
    OF THE LOAN
    NUMBER

    FORMULA

  14. By-hand Work Set up the loan payment formula with numbers and solve for the monthly loan payment by hand. You might want to do this on scrap paper, and then compare with the excel answer to be sure that you did everything correctly before writing it down here. If you do not get the same answer, then you should recheck both Excel and your by-hand work to see what you did wrong.
  15. Leasing for 3 years You also have the option of leasing your car for 3 years, and then giving the car back at the end of the 3 years. Monthly payments and upfront costs on a lease can be less expensive and allow you to drive a new car every few years. However, with leasing you are limited to a certain number of miles per year, or you must pay extra, and insurance costs can be higher. Most leases charge you as much as 25 cents per mile if you exceed the annual mileage limit -- usually between 12,000 and 15,000 miles, and it is very expensive to break a lease early. Would you rather purchase or lease your car?
  16. Debt-to-Income Ratio Assume the bank allows a 35% debt-to-income ratio and assume that you have the following monthly debts: Use these to figure out your necessary income by reading p. 36-37 in the How Do You Know? book and using the formula for debt-to-income-ratio.
    monthly rent = $425.00, monthly student loan payment = $80.00, monthly insurance = $40.00, monthly credit card minimum payments = $50.00, car payment= monthly car payment amount in above chart. What is your monthly debt? What is the monthly income necessary? Use this to figure out what your annual income should be. Show work.