I already turned this in, but struggled and think that I did it wrong. I seleced a dodge durango, with good credit, at a purchase price of $24,881 and APR at 6.74 for 5 years. I picked a "max" monthly payment of 550, and a down payment of 5000. I struggled with the final equation, a nested one---I think I did it wrong. Any help would be appreciated.

This is homework for EXCEL, to be put into a spreadsheet and correct formulas---I'm struggling on the very last formula,I've put in bold italics.

Purchasing a vehicle has several options that go into the decision. From a list of vehicles in the Excel file, you will make decisions and compute the outcome. Included in the process are a series of loan annual percentage rates (APRs) based on the length of the loan and credit rating. Apply this information to have Excel assist in making the decision on purchasing a vehicle.

A list of basic information is listed in the Output column of the table. The information that is listed in purple is meant to be a prompt to assist in completing the assignment. Change the color of this column to the color of your assigned Group Name. Everyone in the group should use the same color.

From the List of Vehicles table provided in the Vehicle and Loan Data tab, every member of the group should pick a different vehicle and declare your selection on your Discussion Board per the Activity Directions in Blackboard.

Enter the information for the Year, Make, and Model that you have chosen in cell C4 of the Financing a Vehicle tab.

Enter the information for the List Price in cell C5 of the Financing a Vehicle tab.

From the Loan Options table provided in the Vehicle and Loan Data tab, every member of the group should pick a Credit Rating. Repetition is permitted among the group members for Credit Rating. From the Loan Options table provided in the Vehicle and Loan Data tab, every member of the group should pick a unique value for the Length of Loan. Use the Loan Options table to look up the APR (%) from the values selected from Credit Rating and Length of Loan.

Enter the information for the Credit Rating in cell C6 of the Financing a Vehicle tab.

Enter the information for the Length of Loan in cell C7 of the Financing a Vehicle tab.

Enter the information for the APR (%) in cell C8 of the Financing a Vehicle tab.

Choose a dollar value for the Maximum Monthly Payment that you feel your hypothetical customer can afford. Select a value between $0 and $1200. Repetition is permitted among the group members for this value.

Enter the Maximum Monthly Payment value in cell C9 of the Financing a Vehicle tab. Display the results to the nearest $1.

Choose a dollar value for the Down Payment that you feel your hypothetical customer can afford. Select a dollar value between 0% and 50% of the List Price.

Enter the Down Payment value in cell C10 of the Financing a Vehicle tab. Repetition is permitted among the group members for this value. Display the results to the nearest $1.

In this hypothetical situation, if a customer provides more than 25% of the List Price for the Down Payment, the financing company will lower the APR by 1.5%. Compute the amount necessary to reach that discount by computing 25% of the List Price.

Enter the Amount to Discount APR value in cell C11 of the Financing a Vehicle tab. Display the results to the penny.

Compute the Updated APR (%) on the Financing a Vehicle tab by either:

Entering the Updated APR (%) in cell C12 by computing APR (%) minus 1.5% if the Down Payment is greater than or equal to 25% of the List Price.

Entering the Updated APR (%) in cell C12 with just the APR (%) if the Down Payment is less than 25% of the List Price.

Compute the Amount Financed from the List Price and Down Payment.

Enter the Amount Financed value in cell C13 of the Financing a Vehicle tab.

Use the PMT function from the Financial category of Excels functions to determine the Monthly Payment. Be sure to calculate the input for the function properly. The input for the function may be computed within the function or in separate cells. Make sure the output is a positive value as the amounts due are being calculated as positive values elsewhere in the worksheet.

Enter the Monthly Payment result in cell C14 of the Financing a Vehicle tab.

Based on the Length of the Loan and the Monthly Payment, calculate the Loan Total, that is the total amount paid back over the course of the loan.

Enter the Loan Total in cell C15 of the Financing a Vehicle tab.

Based on the Loan Total and the Amount Financed, calculate the Interest Paid, that is the amount more than the Amount Financed that is the price for obtaining a loan.

Enter the Interest Paid in cell C16 of the Financing a Vehicle tab.

Determine if the vehicle is Affordable into cell C17 of the Financing a Vehicle tab by using a formula to enter either:

Yes into cell C17 for the Affordable entry if the Max. Monthly Payment is greater than or equal to the Monthly Payment.

No into cell C17 for the Affordable entry if the Max. Monthly Payment is less than the Monthly Payment.

Determine by using formulas if the hypothetical customer will Purchase the vehicle and enter the results of the Financing a Vehicle tab by entering:

No into cell C18 for the Purchase entry if the result for Affordable is no.

Yes into cell C18 for the Purchase entry if the Monthly Payment is between 0% and 80% of the Max. Monthly Payment.

Ill think about it into cell C18 for the Purchase entry if the Monthly Payment is between 80% and 100% of the Max. Monthly Payment.

Financing a Vehicle
Entry		Output
Year, Make, Model	:	From available options. Each student must chose a different vehicle.
List Price	:	Based on the options chosen in previous cell
Credit Rating	:	Chosen by student from available options. Repetition within the group is permitted.
Length of Loan	:	Chosen by student from available options. Each student must chose a different Length of Loan.
APR (%)	:	Credit Rating and Length of Loan leads to APR.
Max. Monthly Payment	:	Some dollar amount chosen by student between $0 and $1200.
Down Payment	:	Some dollar amount chosen by student between 0% and 50% of the List Price.
Amount to Discount APR	:	Computed from 25% of the List Price
Updated APR (%)	:	APR (%) minus 1.5% if Down Payment greater than 25% of the List Price. Otherwise, list the original APR (%).
Amount Financed	:	Computed from List Price and Down Payment
Monthly Payment	:	Computed using PMT from Updated APR, Length of Loan, and Amount Financed.
Loan Total	:	Computed from Length of Loan and Monthly Payment.
Interest Paid	:	Computed from Loan Total and Amount Financed
Affordable	:	"Yes" or "No:" Determined by comparing Max. Monthly Payment to Monthly Payment.
Purchase	:	"No" if Affordable is No.
		"Yes" if Monthly Payment is 0% to 80% of Max. Monthly Payment.
		"I'll think about it" if Monthly Payment is 80% to 100% of Max. Monthly Payment.

Vehicle and Loan Data
List of Vehicles
	Year	Make	Model	List Price
	2007	Lexus	CT	17361
	2009	Pontiac	G5 GT	7380
	2010	Ford	Super Duty F-250	22761
	2011	Dodge	Durango	24881
	2011	Ford	Mustang GT	17881
	2012	Ford	Fusion SEL	11991
	2014	BMW	328d	35595
	2014	Subaru	Forester 2.5i	21888
Loan Options
		Length of Loan
	APR (%)	1 Year	2 Years	3 Years	4 Years	5 Years	6 Years
Credit	Fair	22.99	18.84	16.79	10.19	9.79	10.29
Rating	Good	13.69	10.04	9.24	6.79	6.74	6.99
	Very Good	6.49	4.99	4.69	3.74	3.79	3.89
	Excellent	4.89	3.89	3.64	3.04	3.04	3.04