I NEED THE FORMULAS FOR GRAY HIGHLIGHTED CELLS [10 points] Make a new Excel file
ID: 2790094 • Letter: I
Question
I NEED THE FORMULAS FOR GRAY HIGHLIGHTED CELLS
[10 points] Make a new Excel file, right click on the tab at the bottoms that says Sheet 1 and rename it "Car Financing" to your workbook. In the top line write in assignment, class, and your name. You are buying a car and have settled on a purchase price of $33,560 (the current average new car price) and a trade-in value for your current car of $2500. You agree to a down payment of $1000 and they offer you 4 years financing with monthly payments of $775. Calculate the effective annual interest rate you are being charged. It's too much-so you try and leave, they offer you special financing of 6%. What is the new payment? Still uneasy about coming up with that much money every week. They tell you it is a great and reliable car and offer you 5 years financing instead of 4. What is the payment now? You really can't afford more than $500 payment - under these latest financing terms what is the maximum amount you can finance? Assignment 8 ENGR 3315 Fall 2017 Submitted by: Lee Hinkle (key) 2 1 33,560.00 Car Price -$2,500.00 Trade-In for current car $1,000.00 Down Payment 30,060.00 Amount Financed 4 775.00 Monthly Payment 4 Years Financed 9 11.43% Effective Annual Interest Rate 10 6% New Interest Rate $705.96 New Payment Amount 12 13 5 New Number Years Financed @New Rate $581.14 New Payment Amount 15 16 17 Same terms, but need $500 payment, how much can you finance? 18 $25,862.78 19 20 21 Numbers in white are to be entered 22 Numbers in grey are calculatedExplanation / Answer
Amount Financed = Car Price -Trade In - Down Payment = A3 - A4 - A5 = 33,560-2500-1000= $ 30,060
Effective Annual Interest Rate(EAR)
To find first find the monthly interest rate using formula= rate(nper,pmt,pv)= rate(4*12, -775,30060) = 0.91%
EAR = (1+0.91%)^12 -1 = 11.43%
New Payment Amount = PMT(rate,nper,pv) = PMT(6%/12,4*12,30060) = $705.96
New Payment amount = PMT(6%/12,5*12,30060)=$ 581.14
Finance Amount(PV) = PV(rate, nper,pmt) = PV(6%/12,5*12,-500) = $ 25,862.78
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.