Please fidn the solution below: Calculating car payments Price $30,000 Down paym
ID: 373086 • Letter: P
Question
Please fidn the solution below:
Calculating car payments Price $30,000 Down payment $5,000 Amount financed $25,000 Term in months 36 Can be any multiple of 12, up to 60 Annual interest rate 8% Monthly interest rate 0.22% Monthly payment $783.41 Total interest paid $3,202.73 Payment schedule (works for any term up to 60 months) Month Beginning balance Payment Interest Principal Ending balance 1 $25,000.00 $ 783.41 $ 166.67 $ 616.74 $ 24,383.26 2 $24,383.26 $ 783.41 $ 162.56 $ 620.85 $ 23,762.40 3 $23,762.40 $ 783.41 $ 158.42 $ 624.99 $ 23,137.41 4 $23,137.41 $ 783.41 $ 154.25 $ 629.16 $ 22,508.25 5 $22,508.25 $ 783.41 $ 150.06 $ 633.35 $ 21,874.90 6 $21,874.90 $ 783.41 $ 145.83 $ 637.58 $ 21,237.32 7 $21,237.32 $ 783.41 $ 141.58 $ 641.83 $ 20,595.49 8 $20,595.49 $ 783.41 $ 137.30 $ 646.11 $ 19,949.39 9 $19,949.39 $ 783.41 $ 133.00 $ 650.41 $ 19,298.97 10 $19,298.97 $ 783.41 $ 128.66 $ 654.75 $ 18,644.22 11 $18,644.22 $ 783.41 $ 124.29 $ 659.11 $ 17,985.11 12 $17,985.11 $ 783.41 $ 119.90 $ 663.51 $ 17,321.60 13 $17,321.60 $ 783.41 $ 115.48 $ 667.93 $ 16,653.67 14 $16,653.67 $ 783.41 $ 111.02 $ 672.38 $ 15,981.29 15 $15,981.29 $ 783.41 $ 106.54 $ 676.87 $ 15,304.42 16 $15,304.42 $ 783.41 $ 102.03 $ 681.38 $ 14,623.04 17 $14,623.04 $ 783.41 $ 97.49 $ 685.92 $ 13,937.12 18 $13,937.12 $ 783.41 $ 92.91 $ 690.50 $ 13,246.62 19 $13,246.62 $ 783.41 $ 88.31 $ 695.10 $ 12,551.52 20 $12,551.52 $ 783.41 $ 83.68 $ 699.73 $ 11,851.79 21 $11,851.79 $ 783.41 $ 79.01 $ 704.40 $ 11,147.39 22 $11,147.39 $ 783.41 $ 74.32 $ 709.09 $ 10,438.30 23 $10,438.30 $ 783.41 $ 69.59 $ 713.82 $ 9,724.48 24 $9,724.48 $ 783.41 $ 64.83 $ 718.58 $ 9,005.90 25 $9,005.90 $ 783.41 $ 60.04 $ 723.37 $ 8,282.53 26 $8,282.53 $ 783.41 $ 55.22 $ 728.19 $ 7,554.34 27 $7,554.34 $ 783.41 $ 50.36 $ 733.05 $ 6,821.29 28 $6,821.29 $ 783.41 $ 45.48 $ 737.93 $ 6,083.36 29 $6,083.36 $ 783.41 $ 40.56 $ 742.85 $ 5,340.50 30 $5,340.50 $ 783.41 $ 35.60 $ 747.81 $ 4,592.70 31 $4,592.70 $ 783.41 $ 30.62 $ 752.79 $ 3,839.91 32 $3,839.91 $ 783.41 $ 25.60 $ 757.81 $ 3,082.10 33 $3,082.10 $ 783.41 $ 20.55 $ 762.86 $ 2,319.24 34 $2,319.24 $ 783.41 $ 15.46 $ 767.95 $ 1,551.29 35 $1,551.29 $ 783.41 $ 10.34 $ 773.07 $ 778.22 36 $778.22 $ 783.41 $ 5.19 $ 778.22 $ 0.00Explanation / Answer
The file “carloanshell.xlsx” contains a template for a car loan.
Here is link to the file (https://drive.google.com/file/d/1X35v7rRsTaeZ71mI9e-_MZcMNMjbfy3E/view?usp=sharing)
Specifically, once values are entered in the input cells (which are now blue), you need to enter formulae in the grey cells to calculate the amount financed, the monthly payment (assuming that monthly payments stay the same throughout the term of the loan), the total interest paid, and an amortization schedule. For the latter, fill in the entire grey area with formulae, but use the IF functions so that blanks appear past the term of the loan. You'll need to learn about the “PMT( )” function for cell B9. (You can google this). Think of this as a tool for users. Change the formatting to be as we learned about in class using cell styles. Lock all cells so that users can't even click on the formula cells, but only input cells (make it idiot proof, and a useful tool for a loan officer). Paste a screenshot of your excel sheet here, and also upload your model.