Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
what are the steps for this? 9. Calculate the monthly payment amount for each loan. a. In cell C20, enter a formula using the PMT
what are the steps for this? 9. Calculate the monthly payment amount for each loan. a. In cell C20, enter a formula using the PMT function to calculate the monthly loan payment for the first vehicle.Hint: Divide the interest rate by 12 in the Rate argument to reflect monthly payments. b.Hint: Multiply the number of years by 12 in the Nper argument to reflect the number of monthly payments during the life of the loan.Hint: Use a negative value for the loan amount in the Pv argument so the payment amount is expressed as a positive number. c. Copy the formula to the appropriate cells for the other vehicles. H A B c D E F 1 Vehicle Purchase Assumptions 2 3 $9,000 Total Cash Available for Vehicle Purchase 4 $650 Total Monthly Amount Available to pay for Operating Vehicle 5 650 Number of Miles | Expect To Drive Each Month 6 $2.79 Gas Price Per Gallon 7 6 00 9 Registration Fee Lookup Table 2 10 column # 11 12 13 14 15 16 17 1 Cargo Van Farm Vehicle Motor Home Motorcycle Passenger Trailer Truck 100 68 100 60 100 30 1001 AutoSave Trevor Bottom-EX2019-Chalenge Yourself-3-3(2) - Excel o Trevor Bottom File Home Insert Draw Page Layout Formulas Data Review View Help Share CIS f If D Analysis Vehicle 2 Vehicle 3 Vehicle 4 1 Vehicle Purchase Vehkle 1 Description 2019 Ford Mustang Convertib Vehicle Type Passenger 2019 Ford F150 Truck 2017 Toyota Prius Passenger 2017 Ducati Super Sport Motorcycle Averara Lowest dealer $31,895 21 dealer $26.115 19 25 22 private seller $24.463 58 53 555 private seller $12,995 36 36 96 Price MPG City MPG Highway 26 Average MPG $600 100 $1,200 $500 100 $800 $300 100 $900 $500 60 $1,500 Maintenance/Yearl Registration Feel Insurance/Year NPR 1.90 Seller 7 Price UPG CITY 9 MPG Highway 10 Averape MPG 11 Yearly Costs 12 Maintenance/Year Registration fee Insurance/Year 15 Need Loan? 16 Amount to Borrow 17 Years 19 Monthly Costs 20 Loan Payment 21 Gas 22 Maintenance/Month 23 Insurance/Month 24 Total Monthly 25 Affordable? 27 28 29 30 3.203 5 2 90% 3 5.90 Amount to Borrow APA Years 3 2 Loan Payment Gas Maintenance/Month Insurance/Month Total Monthly 9. Calculate the monthly payment amount for each loan. a. In cell C20, enter a formula using the PMT function to calculate the monthly loan payment for the first vehicle.Hint: Divide the interest rate by 12 in the Rate argument to reflect monthly payments. b.Hint: Multiply the number of years by 12 in the Nper argument to reflect the number of monthly payments during the life of the loan.Hint: Use a negative value for the loan amount in the Pv argument so the payment amount is expressed as a positive number. c. Copy the formula to the appropriate cells for the other vehicles. H A B c D E F 1 Vehicle Purchase Assumptions 2 3 $9,000 Total Cash Available for Vehicle Purchase 4 $650 Total Monthly Amount Available to pay for Operating Vehicle 5 650 Number of Miles | Expect To Drive Each Month 6 $2.79 Gas Price Per Gallon 7 6 00 9 Registration Fee Lookup Table 2 10 column # 11 12 13 14 15 16 17 1 Cargo Van Farm Vehicle Motor Home Motorcycle Passenger Trailer Truck 100 68 100 60 100 30 1001 AutoSave Trevor Bottom-EX2019-Chalenge Yourself-3-3(2) - Excel o Trevor Bottom File Home Insert Draw Page Layout Formulas Data Review View Help Share CIS f If D Analysis Vehicle 2 Vehicle 3 Vehicle 4 1 Vehicle Purchase Vehkle 1 Description 2019 Ford Mustang Convertib Vehicle Type Passenger 2019 Ford F150 Truck 2017 Toyota Prius Passenger 2017 Ducati Super Sport Motorcycle Averara Lowest dealer $31,895 21 dealer $26.115 19 25 22 private seller $24.463 58 53 555 private seller $12,995 36 36 96 Price MPG City MPG Highway 26 Average MPG $600 100 $1,200 $500 100 $800 $300 100 $900 $500 60 $1,500 Maintenance/Yearl Registration Feel Insurance/Year NPR 1.90 Seller 7 Price UPG CITY 9 MPG Highway 10 Averape MPG 11 Yearly Costs 12 Maintenance/Year Registration fee Insurance/Year 15 Need Loan? 16 Amount to Borrow 17 Years 19 Monthly Costs 20 Loan Payment 21 Gas 22 Maintenance/Month 23 Insurance/Month 24 Total Monthly 25 Affordable? 27 28 29 30 3.203 5 2 90% 3 5.90 Amount to Borrow APA Years 3 2 Loan Payment Gas Maintenance/Month Insurance/Month Total Monthly
what are the steps for this?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started