Dr. Sarah's Condo

The ONLY thing I changed is the price of the condo. Everything else is really true and is all exactly as it happened!
In August, 1998, I moved into the area and rented an apartment. Even though I had enjoyed renting for many years during college and graduate school, I disliked renting in this area (mainly because of landlord hassles, but also because of expense). In the fall of 1998, interest rates were very low and I found a condo in Blowing Rock that I liked. I locked in a rate of 6.75% for a 30 year mortgage. Let's say my condo cost 105,265. If I couldn't put 20% down, then I had to pay extra each month for special insurance which protects the bank (but does not help me), so I decided to put down 20%. The bank vice president told me (and I got this in writing) that I could pay the loan off at any time by sending in a different check marked "please pay towards principal". (One needs to mark this down, or it ends up paying interest, which means that you are not reducing the loan balance remaining!).

Together, we'll fill in an amortization table on Excel, and then face some decisions that I had to make before closing on the condo.
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 or three and turn in one per group 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 click Save to save the file as condolab.xls. Click replace, if that comes up. Under the apple, under Math 1010 Apps, release on Microsoft Excel. Under File, release on Open, and then click on condolab.xls and Open it. You will see a chart that has words but not numbers filled in. As you wait for my instructions, read through the lab.

  • As we go through this together, fill in the Excel FORMULAS we use in the following table. Then fill in first row of Table 2.
    A B C D E
    1 condo cost     monthly rate 1st years total interest  
    2 loan amount        
    3 payment   30 yrs interest    
    4          
    5 Month # End of Month Payment Amount Interest Paid that Month Principal Paid that Month Loan Balance Remaining
    6 1        
    7 2        
    8 3        
  • How much did I need for a downpayment? Explain with a formula and answer.








  • When I made my first payment, why did I already have interest to pay?








  • Why do we use a $ sometimes in the formulas?








  • If we use the periodic payment formula, with a payment of the number in B3, how much money does the bank end up with? Set up the formula here and then solve for an answer. Notice that the answer is a lot more money than the loan amount in B2.










  • Why does it make sense that the bank should end up with more money than the original loan amount?








  • Explain in words the formula that I gave you for the 1st years total interest in D2. Why does it make sense that this is equal to the first years interest?








  • Explain in words the formula for the 30 years interest in C4. Why does it make sense that this is equal to the interest paid over 30 years.








  • Explain C6, D6 and E6 in words and explain why they make sense.










  • Explain C8, D8 and E8 in words and explain why they make sense.










  • Make sure that you have filled in first row of Table 2 (with numbers). Then under file, release on Save As... and save as yourfirstname.xls into the IMAC # folder where # is the number of your computer.
    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 2.375% of the loan amount (these are called points) to reduce the rate from 6.75% down to 6.25%. Change C2 to read =.0625/12.

    Option 2: Take out a smaller loan The rate would stay at 6.75% (so change C2 back), but I could reduce the loan amount by $2000 (ie change B2 to read =.8*B1-2000). Fill in the table below

  • Fill in the following with NUMBERS not formulas:

    		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




    Show my your filled in table Then use campus pipeline to e-mail your .xls (excel) file as an attachment to your group members since you will need this for next Monday's lab.

    Comparing Options and Tax Consequences

    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 in points to buy down the rate? Explain.









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









  • 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? Explain?









  • But, this is not the entire story. 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? The 28% bracket? Explain.









    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? Explain.









  • 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? Explain.









  • Compare this option 2 savings to the option 1 "points money" 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 since option 2 has higher interest.

  • Which option would you have chosen? Why?












    Extra Credit Number 1 due with the lab

  • 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 to =PMT(sameasbefore) + 20. What month 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? (notice that the formula in B3 will no longer be correct). Explain.