Go to this lab on the class highlights page,
control-click on this
excel file from last lab, and open condocarlabsols.xls.
Buying versus Leasing a Car:
Let's use our Excel table in another situation which will probably
be more relevant to you in the next few years - buying a car!
Write down the make, model and year of the car that you have chosen in the
last lab from
Kelly Blue Book.
There is a retail price listed on the sticker,
but people often negotiate to just a few hundred dollars over the factory
invoice price.
Assume that you have negotiated with the dealer and will pay a
fair purchase price. How much money is this?
In North Carolina, you must pay a 3% "Highway Use Tax" whenever you
buy a car. Add this
to the price of your car to get your car's total price.
How much is this? Show work.
In B1 Click on B1 in your Excel sheet.
You will see 105265 appear next to the
up at the top in the formula bar.
Change the amount to match your car's total price
and then hit return.
In 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. In D1
Click on D1 and then click up top next to the = sign.
Change this to the rate for the car loan you found in #15 of last lab
divided by 12 (ie =rate/12). In B3, 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))
so that the Excel loan payment formula reads
=PMT(D1,72,-B2).
In D3, by going to the formula bar,
change 360 to 72, and change the text total interest 30 years to
6 years.
Fill in the chart below. 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.
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.
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.
Monthly payments and upfront costs on a lease can be less expensive
and allow you to drive a new car every few years.
However, with leasing
you are limited to a certain number of miles per year, or you must
pay extra, and insurance costs can be higher. Most leases charge you as much as 25 cents per mile if you exceed the annual mileage limit -- usually between 12,000 and 15,000 miles, and it is very expensive to break a lease early.
Would you rather purchase or lease your car? Why?
Debt-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
using the formula for debt-to-income-ratio from the homework reading in
How Do You Know 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 debt?
What is the monthly income necessary? Use this to figure out
what your annual income should be. Show work.
What if you can afford an extra $20 per month on the car payment?
Calculate the total interest over the life of the loan now. Show work.
[Hint: D3 will not give the correct answer since it assumes 72 months
and it does not account for the negative balance the last month, so
use your the last payment in the excel file just like we did in the
last
lab #7 and #8 with the paying extra each month.]
Go to ASULearn Dr. Sarah's Math 1010 Glossary/Wiki (Testing)
Browse by category
Choose Personal Finance from the drop down menu
Read through the first 2 finance entries
compound and
diverse ways that people succeed in and impact
finance Do you have any questions?
Check your grades on ASULearn and ask me any questions since the last
day to drop is Thursday.
Recall that you can compute a weighted average
to determine the letter grade.
You may work on hw for tomorrow here with my help, or you may leave and work elsewhere. I'm happy to help!