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 real-life. 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, re-read p. 93-95 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 re-use 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 by-hand 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 by-hand 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*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

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

By-hand 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.

Dept-to-Income Ratio Assume the bank allows a 35% debt-to-income ratio and assume that you have the following monthly debts: Use these to figure out your necessary income by looking up "dept-to-income 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.