Share
Explore BrainMass

Assume that, at some point in your life, you will purchase a home or a car. When purchasing an item of this size and cost, there is much to be considered. You will need to take out a loan to purchase the item. When taking out a loan, there are many factors that you must consider. How much does the item cost, how much of a down payment will you have, how long do you want the loan for, and what interest rate will you get for the loan?

Utilizing your knowledge of PMT functions, PV functions, data tables, and amortization schedules, create 2 spreadsheets comparing the 2 cars that you would like to purchase. One car should be a used car. You may research any car that you wish to purchase, but the cost must be between \$5,000 and \$12,000. The second car will be a new car. You may research any car that you wish to purchase, but the cost must be more than \$20,000.

In your spreadsheet, you will state the amount you are purchasing the car for. You want the loan for 4 years. Your interest rate will be 4.5%. Your down payment will be \$1,000 for the used car and \$5,000 for the new car.

From the information that you have gathered in your research, prepare a spreadsheet to compare the 2 cars you have purchased.

Sheet 1: Car 1

Create and format a Loan Payment Calculator section. In this section, you will calculate monthly payments, total interest, and total cost of the car you are purchasing.
Create and format a data table that includes the interest rate schedule.
Create and format the amortization schedule using the present value and future value functions.

Sheet 2: Car 2

Create and format a Loan Payment Calculator section. In this section, you will calculate monthly payments, total interest, and total cost of the car that you are purchasing.
Create and format a data table that includes the interest rate schedule.
Create and format the amortization schedule using the present value and future value functions.

Sheet 3: Comparison

On a separate tab, discuss the following changes:

What is the difference in payments for the 2 cars with the same down payment and time frame of the loan?
If you have a poor credit rating and your interest rate will be 8%, what is the difference in your monthly payments?

#### Solution Summary

The solution computes monthly payment for buying a new or a used car in different scenarios along with amortization schedule. Attached in Excel.

\$2.19