Dr. Sarah's Condo Part 2

2 PERSON GROUP - NAMES______________________________________________________________________
Circle one of 9:30 or 11:00 as your class time.

The ONLY thing I changed is the price of the condo. Everything else is really true and is all exactly as it happened!
In the fall of 1998, I locked in a rate of 6.75% for a 30 year mortgage. Let's say my condo cost 105,265. In Dr. Sarah's Condo Part 1 we explored issues related to the purchase of the condo and performed calculations by hand for month 1 and 2 of the condo payments. Today's lab uses an amortization table in Excel to examine the rest of the payments and other issues.

Much of your success in this course depends on you carefully listening to and taking notes on what I say, carefully following directions, and practicing on your own. The success of today's lab depends on you doing all three of these things. Work in groups of two and turn in only one per group (part 1 and part 2) at the end of today's lab. Your grade will be based on how many questions you answered correctly and completely, and the clarity and depth of your writing and explanations, so take your time to think carefully and discuss the issues in your group before writing down an answer and be sure to write in complete sentences! You may attach extra pages if you need more room for explanation. One person in your group should open up Netscape, (under the apple, under internet), go to the class web pages, and then go to this lab to click on this excel file You will see some symbols. Under File, release on Save As... and then find the public saves folder (click on desktop and then double click on the folder) and then click Save to save the file as condolabsol.xls in the public saves folder. Click replace, if that comes up. Under the apple, under Math 1010, release on Microsoft Excel. Under File, release on Open, and then click on condolabsol.xls and Open it from the public saves folder.

GOAL: If you will ever buy a car or home, then you will certainly see information from a loan amortization table, so the purpose of today's lab is to learn how to effectively use one and to learn about related real life issues.

  • Fill in the Excel FORMULAS in the following table. If you don't see the Excel Formula Bar, then under view release on it so that it is "checked". As you copy down each formula, go back to your condo lab part 1 AND class notes to make sure that you understand how an amortization table works. If there is something that you don't understand, talk to your partner about it, and ask me as I make my way around the lab.
    1 condo cost     monthly rate 1st years total interest  
    2 loan amount        
    3 payment   30 yrs interest    
    5 Month # End of Month Payment Amount Interest Paid that Month Principal Paid that Month Loan Balance Remaining
    6 1        
    7 2        
    8 3        
  • Compare the numbers in the table to our by-hand calculations from class. Notice that they are off a little bit. Explain what went wrong (recall we talked about this in class).

  • Notice that the interest ($) paid that month, in column C, decreases as time increases. Why is this the case?

  • Now fill in ONLY the FIRST ROW of Table 2 with NUMBERS. For the year 2 and year 5 balance, be careful to look in the A column for the proper month (NOT the gray markers, which are numbered differently than the month). When you are done with the first row, continue by following the directions below the chart.

    monthly payment 1st yr total interest Year 2 balance Year 5 balance 30 yrs interest
    Original Info

    Option 1
    Lower Rate

    Option 2
    Smaller Loan

    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 (this really happened!). The vice president gave me two options.

    Option 1: Buy down the rate I could leave the loan amount the same, and pay a percentage of the loan amount (in dollars) 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 (do not change the rate in the C2 box itself - it is much easier to change it in the formula bar). Notice that this will change the rest of the amortization table. Fill in the SECOND ROW of the above table with numbers.

    Paying extra each month. Using option 1, what happens if I can pay an extra $20 each month?- We would tell the bank that this extra $20 should go towards the principal. Namely, b3 changes via adding +20 at the end to =PMT(C2,360,-B2) + 20.

  • When do I have a zero balance left on the loan? (it will be less than month 360 since I'm paying more than is required)

  • Exactly how much interest do I end up paying total over the life of the loan when I pay this extra $20 each month? Show work. (Notice that the formula in B3 will no longer be correct. Hint: look at the part of the Excel chart that shows the loan balance changing from positive (black) to negative (red) and use the information there to help you.)

    Option 2: Take out a smaller loan The rate would stay at 6.75% (so change C2 back to =.0675/12 and change B3 back to =PMT(C2,360,-B2) by taking off the -20), but I could reduce the loan amount by $2000 (ie change B2 to read =.8*B1-2000). Fill in the THIRD ROW of the above table with numbers.

    Show me your completed table to verify that it is correct. You should continue on if you are waiting for me, but wait to see me before doing more excel work.

    Buying versus Leasing a Car

  • Click on this Kelly Blue Book link. Click on New Car Pricing on the right, enter 28608 as the zip code and click continue and then find a car that you would like to purchase. Spend no more than 10 minutes deciding on a car.
  • Write down the make, model and year of the car that you have chosen.

  • Assume that you have negotiated with the dealer and will pay $500 over the invoice price. You must also pay the destination charge, and any special taxes. Decide on any options that you want and add everything up. What is the price of your car with all options you want, the $500 over invoice, the destination charge and any other special fees?

  • In North Carolina, you must pay a 3% sales tax. Add this to the price of your car to get your car's total price. How much is this? Show work.

  • Assume that you will put 10% down on your car. Then you will need a loan for 90% of the car's total price (in bold above). What is 90% of the car's total price (which you will need to take out as a loan)? Show work.

  • Assume that you will take out a loan for 6 years at 7.5% compounded monthly (I obtained the interest rate from the State Employees Credit Union at http://www.ncsecu.org/). Set up the loan payment formula and solve for the monthly loan payment by hand. Show your setup and answer (you may want to do this on scrap paper, and then compare with the excel you will do next to make sure that you did it correctly before writing it down here).

    B1 Click on B1. 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 (NOT the 90% that you will take out as a loan) and then hit return.

    B2 Click on B2 and then click up top next to the = sign. You must change the loan to 90% of the car cost instead of 80%, so do that and hit return.

    C2 Click on C2 and then click up top next to the = sign. Change this to the rate listed for the car loan that you used when you did the by hand work. It will still be divided by 12.

    In B3 and C4, 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)).

    Fill in the first row of the chart on this sheet. The top row should contain numbers, while the 2nd row should contain your excel formulas.


    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. Plus, with leasing, you are limited to a certain number of miles per year, or you must pay extra. In addition, the amount that the value of the car decreases is the amount you will be expected to pay over the 3 year period, plus other costs, so it turns out that your monthly payment for leasing for 3 years should be similar to your monthly payment for buying over 6 years. Would you rather purchase or lease your car? Explain why?

    Dept-to-Income Ratio Assume the bank allows a 35% debt-to-income ratio and assume you have the following monthly debts: Use these to figure out your necessary income by looking up "dept-to-income ratio" in the textbook and using that formula.
    rent = $425.00, student loan = $80.00, insurance = $40.00, credit card minimum payments = $50.00, car payment=amount in above chart
  • Determine what your annual income should be in order to purchase or lease your car. Show work.

    Comparing Options and Tax Consequences of Condo Purchase Choices

    Option 1 - Lower Rate The vice president told me about this option but then she said that this option would only be worth it if I was going to stay in the house a long time, at least 6 years. What did she mean by this? Let's find out.
  • How much money do I pay to buy down the rate (these are called "points") if I must pay 2.375% of the loan amount (in dollars) to reduce the rate from 6.75% down to 6.25%? Show work.

  • How much money do I save each month on the payment? Show work.

  • How many years does it take for this savings per month to equal the amount of money that I would have to spend to buy down the rate? Show work.

  • But, this is not the entire story even though the vice president didn't tell me about the following. Paying for points is tax deductible in the year that you pay for them. In fact, on your tax return, it comes directly off of your gross earnings, which might place you in a lower tax bracket. How much "points money" do I get to deduct from my tax return? If I am in the 15% tax bracket (ie taxed at 15%), how much money does this actually save me for this year (15% of the "points" money) ? The 28% bracket? Show work.

    Option 2 - Lower Loan Note that my loan is smaller than in option 1, but there is more interest paid, and there is no first year tax deduction on the amount spent to decrease the loan. Instead, in any given year, there is only a tax deduction if you itemize your deductions instead of using the standard deduction. You would then be able to deduct 28% (if you were in the 28% tax bracket) of that years total interest minus the standard deduction. But, this also would occur with the lower interest option.

  • Take the 1st years interest for the lower loan option 2, and subtract the 1st years interest for the lower interest option 1. You get? Show work.

  • Assume that we are in the 28% tax bracket. Then our tax savings for the first year, by choosing the lower loan option 2, would be the above number times .28. What is this savings? Show work.

  • Compare this option 2 savings to the option 1 "points money" tax savings. Which option gives more of a tax savings for the first year? Explain.

    Since we can only deduct the "points money" in the first year, using a process similar to the above will show that for subsequent years option 2 will give more of a tax savings. So, option 1 has a large tax savings in the first year, a lower monthly payment and less total interest. Option 2 has a lower loan balance and small tax savings from year 2 onwards.

  • Which option would you have chosen? Why?