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?