### 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.
Work in groups of two or three and turn in one per group. One person in your group should open up Netscape Communicator, (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!
 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?

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

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

• Fill in first row of the table on the reverse side. Then click on E8. Scroll up to the left to A1, and under Edit, release on Copy. Open up Microsoft Word, and under Edit, release on Paste Special. Next to Float over text, UNCHECK the box by clicking on it. Under File, Save As yournamecondo.doc
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. Fill in the table below and then copy and paste special (as above, unchecking the float over text box) E8 to A1 into your Word document.

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 and then copy and paste special E8 to A1 into your Word document.

• 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 and Microsoft Word Document. Then use campus pipeline to e-mail your microsoft word document as an attachment to your group members.

### 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, how much money does this actually save me? 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?

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

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

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.

### Write a report explaining which option you would have taken if you were me and your reasons for choosing that option.

Your letter should summarize the issues involved, explain in detail what we did in excel, and highlight the differences between the different options by referring to the above questions that you answered and the tables that you filled in. Be sure to follow the writing checklist. This means (among other things), that you should explain each formula used to fill out the first table. For example, you should explain cells B1, B2, C2, B3 and C4 in words and by-hand formulas. You do not need to write the Excel formula - you may refer to this on your sheet. But, you do need to explain each formula, such as the loan payment formula from class that is used in B3. Then you should explain 1 row of the rest of the table in words and by-hand formulas. You may refer to the sheet for the other rows since they are done similarly (say this). You will turn in
• This sheet filled in
• Your report with the three excel charts pasted in.

### 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.
• Using the option 1, what would have happened if I had waited until now to buy the condo? (Assume that the cost of the condo had remained the same, but use today's interest rate). Namely, what is the monthly payment, the 2 and 5 year loan balance, and the interest paid over 30 years? Be sure to give the bank name and branch or web page source of today's interest rate.

### Extra Credit Number 2 due in a few weeks

Ask your parents, a friend or someone else for their house or condo loan information. You will need the loan amount, interest rate, term (15 years or 30 years), and what payment month they are in. Do an amortization table for them, and explore the following:
• What happens if they start paying \$20 extra a month now. For example, if they are in payment month 145 (ie they took out the loan 12 years and 1 month ago), then you would go down to that month, and start adding in \$20 from that point onward. Notice that the formula in C3 no longer accurately reflects the 30 years interest. (see the above extra credit). How early is the loan paid off? What is the savings in interest?