Dr. Sarah's Condo
Dr. Sarah's Condo
NAME_________________________________ Circle Class Time 9:30
or 11:00
From The Heart of Mathematics:
We have been watching money coming in and growing, but in real life
we frequently watch money going out and shrinking. Often money
happily comes in when we borrow, and sadly goes out when we have
to pay back the loan or mortgage, as loans are often called.
This process is deadly.
Morticians bury dead people. Mortuaries are where dead people
are praised before they are buried. Mortality means death is in
our future. There's a pattern here. Mort means death,
and words with mort in them are often deadly.
Among the deadliest are mortgage and amortize.
Mortgage comes from the Latin meaning a "death pledge."
To amortize a debt means to "kill the debt."
To understand loans and mortgages, interest, and payments,
we adopt the effective thinking strategy that we have encountered so
frequently, namely, we start with simple cases, understand them
deeply, and then apply the ideas we have learned to more
complicated situations.
Together,
we'll answer questions in order to prepare to use an
amortization table in Excel in lab on Monday.
In the process, we'll face some decisions
that I had to make before buying my condo.
The ONLY thing I changed is the price of the condo.
Everything else is exactly as it happened in reallife.
Your condo lab 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
before writing down an answer
and be sure to write in complete sentences!
Show work means that you should show the details of the
calculation but that you do not need to explain in full sentences.
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. It is standard to make a downpayment
of 20% of the cost.
How much did I need for a downpayment?
Show work.
The remainder of the condo must be paid for by a loan.
How much did I need to take out as a loan?
Show work.
While you are waiting for us to come back together in order to
derive the loan payment formula,
reread p. 9395 in How Do You Know? Keep reading these pages
until we come back together.
Explain in words how we came up with the loan payment formula.
Set up the loan payment
formula with numbers to solve for the monthly loan payment. Explain
why the periodic payment formula cannot be used.
Solve for the monthly loan payment on your calculator. Write down
the calculator keys that you used and the answer (you should write down
enough so that when you are studying, you will recall
how to put this in on your calculator)
My monthly payments go towards paying off the loan and the interest
that compounds on the loan.
How much interest ($) do I pay over 30 years? Show work.
Now we see why this is called a mortgage.
While this seems ridiculously high,
explain why it makes sense that the bank should receive
a lot more money back from us than the amount that they loan to us
(Hint: Think about
what could the bank have done with their money instead of loaning it
to us).
When I make my first payment, I make it at the end of the first
month after the bank loans me the money. So, I have had the
money for an entire month before I make my first payment.
This is the way it is set up for all loans in real life 
you make your downpayment on your house or car, get a loan for the rest
and take possession, and then make your first loan payment at the end of
the first month. The interest ($ not rate) is the
yearly rate divided by 12 times
the outstanding loan balance at the end of the preceeding month.
What is the interest ($ not rate) that I owe at the end of the first month?
Show work.
My first payment goes towards paying off the interest that has
compounded. The money left over goes towards reducing the principal
balance of the loan.
How much money did I pay towards the principal balance of
the loan at the end of the first month? Show work.
My new loan balance is the original loan balance minus
the amount that I payed towards the principal balance of the loan that
month.
What is my new loan balance after I make my first payment? Show work.
I now owe less money since my loan balance is lower, and so my
interest ($) for the 2nd month will be lower than it was for the first
month. Review the calculation of the interest ($) that we owed for
the first month.
What is my interest ($) for the 2nd month? Show work.
Loans are set up so that the
payment at the end of the each month
is the same as at the end of the first month
and so we do not need to reuse the loan payment formula to figure out
my monthly payment for the second month.
Notice that my
interest ($) for the 2nd month is less than my interest ($)
for the 1st month
because my outstanding balance on the loan is smaller, and so the rate
applied to this balance yields a smaller number.
This enables me to put more money towards paying off the principal
so that little by little I can pay off the loan (over 30 years).
How much money did I pay towards the principal balance of
the loan at the end of the second month? Show work.
What is my new loan balance after I make my second payment? Show work.
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.
The vice
president of the bank gave me two options.
For each option, find the monthly payment (using the
loan payment formula) AND how much
interest ($)
I pay over the life of the loan (30 years). Show the setup of
the formulas (with numbers) and work for both options.
You must finish this BEFORE lab on Monday.
Option 1: Buy down the rate
I could leave the loan amount the same, and
pay extra money at the time of the downpayment
in order to reduce the rate from 6.75% down to 6.25%.
Option 2: Take out
a smaller loan
The rate would stay at
6.75%, but I could reduce the loan amount by $2000.
Your success in lab on Monday depends on completion of this worksheet
up to this point.
Lab Work with an Excel Amortization Table
In lab, open up NETSCAPE,
(under the apple, under internet), go to the main class web page
http://www.mathsci.appstate.edu/~sjg/class/1010
and then go to this lab to click on this
excel file
Hit OK, click on the public saves folder, open it
and then click
to save the file as condolabsol.xls in the public saves folder.
Click replace, if that comes up.
Under the apple, under Math 1010
Software, 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 by taking out a loan, 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  NOT NUMBERS to complete the following table
that I've started for you.
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
notes and compare the formula with our byhand
calculations to make sure that you understand how each entry in
an amortization table works.
If there is something that you
don't understand, talk to people around you, and ask me as
I make my way around the lab.

A 
B (FORMULAS) 
C (FORMULAS) 
D (FORMULAS) 
E (FORMULAS) 
1 
condo cost 
105265 
monthly rate 
1st years total interest


2 
loan amount 
=.8*B1 
=.0675/12 
=c6 + c7 + ... + c17 

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 




Compare the numbers in the table to our byhand calculations from
class. Notice that some of them are off by a little bit. Explain what went
wrong.
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.
Recall from above that the monthly payment is located in
cell B3, the 30 years interest is located in cell C4,
and the 1st years total interest is located in cell D2.
For the year 2 (24 months)
and year 5 balances, be careful to look in the A column for the proper
month (NOT the gray markers, which are numbered differently than the
month  for example, grey 24 is only month 19) and then
look at the corresponding E value for the loan balance.
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.
The vice
president gave me two options.
Option 1: Buy down the rate
We have seen (from class) that this option results in
the lowest monthly payment and total interest paid.
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 on option 1.
If I choose option 1, then I have the lowest monthly payment.
Perhaps I can afford to pay more per month. Let's investigate this to
see what happens.
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.
What month do I finish paying off the loan? (it
will be less than month 360 since I'm paying more than is
required  look for the
part of the Excel chart that shows the loan balance changing
from positive (black) to negative (red).
Notice that the formula in C4 will no longer be correct
since we are not paying for 30 years. In fact, a zero balance
does not coincide with a specific month so we need to be a little more
clever.
Hint:
Look at the just 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. We pay our monthly payment for the exact number of months that
it takes for
the chart to turn to negative (red)
when we have finished paying off the loan
BUT we receive money back
(the amount in red) since we have overpaid on this last month.
Exactly how much total do I end up paying over the life
of the loan when I pay this
extra $20 each month? This will be the monthly payment times
the number of months paid  the amount we overpaid on the last month.
Show work.
How much interest do I pay over the life of the loan now?
This will be the total you paid (your last answer) 
loan amount. Show work.
Option 2: Take out
a smaller loan
The rate would stay at
6.75% (so in the formula bar (not the cell itself) 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*B12000). 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
Let's use our Excel table in another situation which is probably more
relevant to most of you  buying 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
5 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 base 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 the base 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.
B1 Click on B1 in your Excel sheet.
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
and then hit return.
B2 Click on B2 and then click up top next to
the = sign.
Assume that you will put 10% down on your car. Then you will need a loan for
90% of the car's total price.
You must change the loan to 90% of the car cost
instead of 80%, so do that and hit return.
Assume that you will take out a loan for 6 years at 7.25% compounded
monthly
(I obtained the interest rate from the State Employees Credit Union at
http://www.ncsecu.org/ by clicking on vehicle loans
and then clicking on new car loans on 9/10/02.)
C2
Click on C2 and then click up top next to the = sign.
Change this to the rate listed for the car loan.
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 chart on this sheet. The top row should contain numbers,
while the 2nd row should contain your excel formulas.

EXCEL MONTHLY PAYMENT 
TOTAL INTEREST OVER THE LIFE
OF THE LOAN 
NUMBER 


FORMULA 


Byhand Work
Set up the loan payment formula with numbers and
solve for the monthly loan payment by hand. You
might want to do this on scrap paper, and then compare with the excel
answer to be
sure that you did it correctly before writing it down here.
You must at least get here by the end of lab, but you should
be able to get further. There will be a little bit of
time in class tomorrow to finish up the end of the lab.
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.
DepttoIncome Ratio
Assume the bank allows a 35% debttoincome ratio and assume
that you have the
following monthly debts:
Use these to figure out your necessary income by looking up
"depttoincome ratio" in the How Do You Know? book and using that formula.
rent = $425.00, student loan = $80.00, insurance = $40.00,
credit card minimum payments = $50.00, car payment=
monthly car payment amount in above chart. What is your monthly dept?
What is the monthly income necessary? Use this to figure out
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
The vice president told me about both option 1 and option 2 but then
she said that that option 1 would only be worth it if
I was going to stay in the house a long time, at least
6 years. Let's see what she meant by this and look at other issues that
she didn't tell me about.
Option 1  Lower Rate
How much money do I pay to buy down the rate (these are called "points") if
I must pay 2.375% of the
original 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 by choosing
option 1 instead of the original info (look at your chart with the numbers
filled in). 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 (this is the terminology used to describe the
process of buying down the rate) 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 I spent to buy down the rate)?
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?
Show work.
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. This occurs in future years because
you receive a tax savings on the interest ($) that gets paid,
and option 2 pays a lot more interest (a lot larger than the tax
savings since the tax savings is only a small percentage of the extra
interest money paid).
So, option 1 has a large tax savings in
the first year, a lower monthly payment, and less total interest,
but it also has a higher loan balance.
Option 2 has a lower loan balance and a
small tax savings from year 2 onwards, but it also has
a higher monthly payment and higher interest payments.
WORTH MORE
Which option would you have chosen? Explain.