Car Payment Problem Analysis homework help

... ...

Car Payment Problem Analysis homework help

Question Description

This assignment is to help you get more familiar with excel. This is a very challenging Excel analysis problem as it summarizes everything you have learned up to this point and even introduces you to a new built-in function called the "pmt" function. This will test your ability to understand how to input the correct data so that the built-in functions work properly. It will also test your ability to think logically about what your columns and rows of data actually mean in a worksheet.

Please complete the assignment using the template document attached, filling in the YELLOW cells with your answers.

Car Payment Analysis You have a new job and are considering the purchase of a car to reward yourself for your hard work. Using the first car in the list HERE and the current 60 monthinterest rate found HERE, answer the questions below. Assume the car will be paid for over 60 months and the interest rate is compounded monthly. Part 1

1. Determine the payment using the PMT function. Consider the interest rate is ANNUAL and payments are MONTHLY. Since this is a payment, or money you’re paying out to someone, it is calculated as a negative number. (2pts) •

2. Compute the monthly balance and the interest paid each month. (6pts) • Interest is paid each month on the previous month’s balance. Consider the interest rate is ANNUAL and payments are MONTHLY. • The balance for each month is the previous month’s balance, plus interest, plus the negative payment value. • You formulas should make use of both relative and absolute references.

3. Compute the total cost of the vehicle by computing the total interest and adding to the price of the car. (1pts)

4. Plot the balance owed per month. Make sure there is a title, axis labels, and units. (3 pts) Part 2 Assume that you can negotiate some of the terms on the car. Determine how much you could save if you could negotiate a 10% better price, lower interest rate, OR shorter term. You don’t have to recalculate the monthly balance for each scenario. This can easily be done by changing the values in the PMT function.

5. Recalculate total cost and monthly payment with the price lowered by 10%. Interest and load duration stays the same as listed above. (2 pts)

6. Recalculate total cost and monthly payment with interest lowered by 0.5%. Principle balance and loan duration stay the same as listed above. (2pts)

7. Recalculate total cost and monthly payment with length of loan reduced to 54 months. Principle balance and interest stays the same as listed above. (2pts)

Answer these questions related to the tasks in Part 2 • • Which of the 3 choices above saves you the most total money (lowest total cost)? (1pt) Which of the 4 choices (original problem and Tasks 5, 6, 7) allows you to pay you the lowest monthly payments? Mark clearly on the Excel sheet. (1pt) Name ENGR 101 - Sec ### Car Payment Analysis Date NEW CAR Price Interest Rate Term Payment Month Balance 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Interest Paid 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 TOTAL $0,00 $0,00 INSERT GRAPH HERE Monthly Payment Total Cost Lower price Lower Interest Shorter duration Answers from worksheet questions Option that saves the most total money: Option with the lowest monthly payment:

Do you have a similar assignment and would want someone to complete it for you? Click on the ORDER NOW option to get instant services at

Order Now

Our Advantages