Dr. Sarah's Condo Part 2
2 PERSON GROUP - NAMES______________________________________________________________________
Circle one of 9:30 or 11:00 as
your class time.
The ONLY thing I changed is the price of the condo.
Everything else is really true and is all exactly as it happened!
In the fall of 1998,
I locked in a rate of 6.75% for a
30 year mortgage.
Let's say my condo cost
105,265. In Dr. Sarah's Condo Part 1
we explored issues related to the purchase of the condo and
performed calculations by hand for month 1 and 2 of the condo payments.
Today's lab uses an amortization table in Excel to examine the rest of
the payments and other issues.
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 and turn in only one per group (part 1 and part 2)
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 find the public saves folder
(click on desktop and then double click on the folder) and then click
Save to save the file as condolabsol.xls in the public saves folder.
Click replace, if that comes
up.
Under the apple, under Math 1010, 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, 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 in the following table.
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
condo lab part 1 AND class notes to make sure that you understand how
an amortization table works. If there is something that you don't understand,
talk to your partner about it, and ask me as I make my way around the
lab.
| |
A |
B (FORMULAS) |
C (FORMULAS) |
D (FORMULAS) |
E (FORMULAS) |
| 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 |
|
|
|
|
Compare the numbers in the table to our by-hand calculations from
class. Notice that they are off a little bit. Explain what went
wrong (recall we talked about this in class).
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. For the year 2
and year 5 balance, be careful to look in the A column for the proper
month (NOT the gray markers, which are numbered differently than the
month).
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
(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
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. 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.
When 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 over the life
of the loan when I pay this
extra $20 each month? Show work.
(Notice that the formula in B3 will no longer be correct. Hint:
look at 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.)
Option 2: Take out
a smaller loan
The rate would stay at
6.75% (so 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*B1-2000). 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
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
10 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 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 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.
Assume that you will put 10% down on your car. Then you will need a loan for 90% of the car's total price (in bold above).
What is 90% of the car's total price (which you will need to take out as a loan)? Show work.
Assume that you will take out a loan for 6 years at 7.5% compounded
monthly
(I obtained the interest rate from the State Employees Credit Union at
http://www.ncsecu.org/). Set up the loan payment formula and
solve for the monthly loan payment by hand. Show your setup and answer (you
may want to do this on scrap paper, and then compare with the excel
you will do next to make
sure that you did it correctly before writing it down here).
B1 Click on B1. 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 (NOT the 90% that you
will take out as a loan)
and then hit return.
B2 Click on B2 and then click up top next to
the = sign. You must change the loan to 90% of the car cost
instead of 80%, so do that and hit return.
C2 Click on C2 and then click up top next to the = sign.
Change this to the rate listed for the car loan that you used when you
did the by hand work. 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 first row of
the chart on this sheet. The top row should contain numbers,
while the 2nd row should contain your excel formulas.
|
EXCEL
MONTHLY
PAYMENT |
FIRST YEAR'S
TOTAL INTEREST |
TOTAL INTEREST
OVER THE LIFE
OF THE LOAN |
TOTAL MONEY PAID
OVER THE LIFE
OF THE LOAN |
| NUMBER |
|
|
|
|
| FORMULA |
|
|
|
|
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?
Dept-to-Income Ratio
Assume the bank allows a 35% debt-to-income ratio and assume you have the
following monthly debts:
Use these to figure out your necessary income by looking up
"dept-to-income ratio" in the textbook and using that formula.
rent = $425.00, student loan = $80.00, insurance = $40.00,
credit card minimum payments = $50.00, car payment=amount in above chart
Determine 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
Option 1 - 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 to buy down the rate (these are called "points") if
I must pay 2.375% of the 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?
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 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)
? 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?
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. So, option 1 has a large tax savings in
the first year, a lower monthly payment and less total interest.
Option 2 has a lower loan balance and small tax savings from year 2 onwards.
Which option would you have chosen? Why?