Condo and Car Purchases

Goal: When our own future is at stake, most of us want to use every effective approach we can find. The mathematical mode of thought is not the only way to approach decisions, but the reasoned strategies that mathematics illustrates are powerful tools that give us surprising strength for analyzing and conquering life's issues. Here we apply these methods of thoughts to the decisions we will face in life releated to house and car buying. The events described here all actually happened, and the same language is used to describe them. We will work on this in a number of classes and in lab.

For homework, you read p. 24-27 in How Do You Know? If you are waiting for Dr. Sarah at any point, then skim through this again. 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. 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.

You will answer questions in order to prepare to use an amortization table in Excel in lab on Monday. In the process, we'll face some common real-life decisions. The ONLY thing I changed is the price of the condo. Everything else is exactly as it happened in real-life. Show work means that you should show only the details of the calculation (1+1=2).

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 the 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 money did I need for a downpayment (20% of 105265)? Show work.

The remainder of the cost of the condo must be paid for by a loan. This loan is the same as the loan balance for month 1. I will slowly pay back the money I owe and reduce the loan balance in future months, and interest will compound on whatever I owe at the time. How much money did I need to take out as a loan (remainder of cost after the downpayment)? Show work.

Set up the loan payment formula with numbers plugged into the equation in order to solve for the monthly loan payment.

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).

Notice that my monthly payment isn't too bad - in fact it was lower than what I was paying when I was renting. My monthly payments go towards paying off the loan and the interest that compounds on the loan. As I pay off the loan, I build up equity in the condo (think of this as a savings account, but instead of putting money in a bank, I would get my money back when I sell the condo) wheras with renting, my money just goes to the rental company. In addition, there are tax benefits to owning a house or condo, which we'll examine later. Yet, there is some bad news: How much interest ($) do I pay over 30 years (total amount I pay - loan amount)? Recall that the total amount I pay equals my monthly payment times 12 months/year times the number of years. Show work. Now we see why this is called a mortgage.

This seems ridiculously high. Is the bank cheating us? Relate your answer to what the bank could 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)/12 times the outstanding loan balance at the end of the preceding month, because interest compounds on however much money I owe at that point, as usual. Note that this is not the total interest over 30 years divided by the number of payments I make because the interst($) changes as the loan balance decreases. What is the interest ($ not rate) that I owe at the end of the first month ((yearly rate / 12) x loan balance)? 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 (monthly payment - interest paid 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 (old loan balance - payment to principal)? Show work.

I now owe less interest($) 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 (yearly rate / 12 * new loan balance)? 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.

The following is the beginning of an amortization table, as in the homework readings. It would take a long time to fill out an amortization table using by-hand work. We will see that we can easily create and modify amortization tables in Excel. Fill in the entries marked with ** in the following table. All of the entries should be filled out below the listed directions except A2 and A3 which should be filled out in B2 and B3, respectively. You have already completed the calculations for almost all of the table. You will have to compute the monthly rate and place that in C2, and will also have to compute and fill out the info for month 3. Note that End of Month Payment Amount is the same as the Monthly Payment.

	A	B	C	D	E
1	condo cost	105265	monthly rate
2	loan amount	**	**
3	monthly 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	**	**	**	**

Before I finalized all the bank payment numbers, I found out that a deceased relative was leaving me $2000, which would arrive before I closed the loan. The vice president of the bank gave me two options.
Option 1: Get a lower rate I could leave the loan amount the same, but use the $2000 in order to reduce the rate from 6.75% down to 6.25% (this is called "buying down the rate.")
Option 2: Take out a smaller loan The rate would stay at 6.75%, but I could reduce the loan amount by $2000.
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 show work for the total interest for both options. You must finish this BEFORE lab on Monday as your success in lab depends on completion of this worksheet up to this point.

Monday Lab Work with an Excel Amortization Table

In lab, go to the class highlights page and then scroll down to control-click on this excel file. The file may open automatically or else you will need to open it (condolabsol.xls) from Excel.
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, 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 Excel to our by-hand calculations from class. Notice that some of them are off by a little bit. Explain what went wrong. (Hint: The issue is similar to the one on the Jane and Joan worksheet and shows us that we should use common sense with computer/ calculator work.) Which is correct? Why?

Notice that the interest ($) paid that month, in column C, decreases as time increases. Why is this the case? Relate your answer to the way the monthly interest ($) is computed.

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.

TABLE 2	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 homework) 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 table 2 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 an entire row of positive (black) to a row that contains the first negative amount (red), copy the last positive (black) row and the first negative (red) row down here in table 3 (but NOT in table 2), and then use the information to help you below.

TABLE 3
Month #	End of Month Payment Amount	Interest Paid that Month	Principal Paid that Month	Loan Balance Remaining

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 table 2 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 the New Car link on the top, and then follow the directions 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.

Notice that the Base Retail price is much higher than the Base Invoice price. The retail price is what is listed on the sticker, but people often negotiate. Assume that you have negotiated with the dealer and will pay $500 over the base invoice price (which is what I did with my last car). You must also pay the destination charge. What is the price of your car with the destination charge added to $500 over the base invoice?

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 (since this is typical in real life). 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 5.50% compounded monthly (I obtained the interest rate from the State Employees Credit Union at http://www.ncsecu.org/ on 4/12/04. Note that in September of 2002, the rate was 7.25%)

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 everything correctly before writing it down here. If you do not get the same answer, then you should recheck both Excel and your by-hand work to see what you did wrong.

You must at least get here by the end of lab, but you should be able to get further. If time remains in lab, then you may work on homework or continue working below.

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 and relate your answer to the points mentioned above.

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 reading p. 36-37 in the How Do You Know? book and using the formula for debt-to-income-ratio.
monthly rent = $425.00, monthly student loan payment = $80.00, monthly insurance = $40.00, monthly 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

Now we return to the condo options. 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 in order to decide which option would be better. Use the information in Table 2.

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.

So, this is what she meant when 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. But, this is not the entire story since 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. If I am in the 27.5% tax bracket (ie taxed at 27.5%), how much money does this actually save me for this year (27.5% of the "points" money I spent to buy down the rate)? 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 27.5% (if you were in the 27.5% 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 27.5% tax bracket. Then our tax savings for the first year, by choosing the lower loan option 2, would be the above number times .275. What is this savings? Show work.

Comparing this option 2 savings to the option 1 "points money" tax savings shows that option 1 gives more of a tax savings for the first year, since the money spent to buy down the rate is tax deductible (option 1), but it is not tax deductible to get a lower loan (option 2). Since we can only deduct this "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 and more money (a lot larger than the tax savings since the tax savings is only a small percentage of the extra interest money paid).

Notice that the money that could be used to buy down the interest rate is exactly the same amount of money that the loan could be reduced. Would you have chosen to use this money to buy down the rate as in Option 1, or take out a lower loan as in Option 2? First, circle the following answers and fill in the following information by using the information in Table 2.

Which option has a lower monthly payment?	Option 1	Option 2
How much lower?
Which option pays less total interest ($) over the life of the loan?	Option 1	Option 2
How much less?
Which option has a lower loan balance at the end of year 2?	Option 1	Option 2
How much less?
Which option has a lower loan balance at the end of year 5?	Option 1	Option 2
How much less?
Which option has a large tax savings during the first year? Note that this tax savings is related to how the money was used (ie choosing Option 1 or Option 2), and that the other option does not get this tax savings due to the setup of tax laws.	Option 1	Option 2
How much is this tax savings?
Which option has a small tax savings from year 2 onwards? Note that this tax savings is a result of larger interest paid, and that the savings is only a small percentage of the higher interest paid.	Option 1	Option 2

Would you have chosen to buy down the rate or instead used that money to take out a smaller loan? Relate your answer to the above information.