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
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
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
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.
How much did I need for a downpayment?
Explain with a formula and answer.
||1st years total interest
||30 yrs interest
||End of Month Payment Amount
||Interest Paid that Month
||Principal Paid that Month
||Loan Balance Remaining
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
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
Explain C6, D6 and E6 in words and explain why they make
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
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!).
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:
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.
||1st yr total interest
||Year 2 balance
||Year 5 balance
||30 yrs interest
Comparing Options and Tax Consequences
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
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?
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?
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.
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.
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.
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?
Which option would you have chosen? Why?
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.
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
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).