Question

You will compare 2 different amortization schedules when buying a car. The purchase price is $17,500.
Bank A requires a 20% down payment and has an annual interest rate of 3.6%. Bank B wants only 10%
down payment but the annual interest rate is 4.8%. In both banks, the loan will be paid off in 3 years.
Formulas: A.First you need to set up the formula to find the monthly payment. Use the PMT function under
FINANCIAL FORMULAS.
B. After calculating the monthly payment, set up the formulas for the monthly interest, balance reduction,
and loan balance. Finally, calculate the total interest paid on the loan under the Monthly interest column
and the total under the Balance reduction (Which should match the loan amount borrowed). Use the
Autosum formula.
C. The first row should use the initial loan amount for the calculation of the Monthly interest. The second
row should use the Loan Balance. Once you have the formulas for the second row, drag the arrow down
to fill out all the remaining rows. Remember that in Excel you need to enter “=” before the formula so it
knows to compute the formula in the cell and doesn’t think it’s just a heading or label.
D. You may also adjust the number of decimal places by clicking on the “increase decimals” or “decrease
decimals” tab under NUMBER on the HOME page.
E. Your formulas should only have cell references. The only numbers you’re allowed to type in are the
Beginning Balances, the interest rates and the years.
F. Lastly, have Excel calculate the total amount paid for the car if you choose Bank A and Bank B. Make
sure you add the down payment, interest and loan amount.

Cost Down payment Loan amount Intrate Years Monthly Payment SIEK SEX Bank B RIDGE ODDESDE U X Month Loan Monthly Int Balance reduct Loan Bal Monthly in Balance reduct WE NE XOX XXXX KLICK XOCO XXXX XXX XXXX EX XXXX XOXOX XXX Total Paid

can you please let me know what equations you use for excel functions

Answer 1

The loan is for the cost of the car - the down payment.

The loan is compounded every month, so, the Annuity(monthly payment) is calculated for every month, which is why the rate of interest is divided by 12 and the number of years is multiplied by 12 in the calculation of annuity(monthly payment) and monthly interest amount.

The monthly payment comprises of two sub parts : The interest on the Loan Balance and the payment towards the principal loan. For example, for the first month, interest is calculated on the entire loan amount. Then the interest amount is deducted from the monthly payment to get the amount payed towards the principal payment. The balance loan(principal) amount is then calculated by deducting the payments made towards principal from the total loan amount.

The answer and the equations are given below:

Styles 12 : 4 А в Н Cost Down Payment Loan Amount Int Rate Years 17500 3500 14000 3.60% 17500 1750 15750 4.80% 2 Bank A 3 Bank B 0 Monthly Payment 410.85 470.63 wo 37.55 Bank A 6 Month Monthly Interest Balance Reduction Loan Balance 1.00 42.00 368.85 13,631.15 2.00 40.89 369.96 13,261.19 3.00 39.78 371.07 12,890.13 4.00 38.67 372.18 12,517.95 5.00 373.30 12,144.65 6.00 36.43 374.42 11,770.24 7.00 35.31 375.54 11,394.70 8.00 34.18 376.67 11,018.04 9.00 33.05 377.80 10,640.24 10.00 378.93 10,261.31 11.00 30.78 380.07 9,881.25 12.00 29.64 381.21 9,500.04 13.00 28.50 382.35 9,117.69 14.00 27.35 383.50 8,734.20 15.00 26.20 384.65 8,349.55 16.00 25.05 385.80 7,963.75 17.00 23.89 386.96 7.576.79 Sheet Sheet2 Sheet4 Sheet3 Sheets Bank B Monthly Interest Balance Reduction Loan Balance 63.00 407.63 15,342.37 61.37 409.26 14,933.11 59.73 410.90 14,522.22 58.09 412.54 14,109.68 56.44 414.19 13,695.49 54.78 415.85 13,279.64 53.12 417.51 12,862.13 51.45 419.18 12,442.95 49.77 420.86 12,022.09 48.09 422.54 11,599.55 46.40 424.23 11,175.32 44.70 425.93 10,749.39 43.00 427.63 10,321.76 41.29 429.34 9,892.42 39.57 431.06 9,461.36 37.85 432.78 9,028.58 36.11 434.51 8.594.07 31.92 Tyne here to search

Alignment Number Styles 26 27 NO 17.00 18.00 19.00 20.00 21.00 22.00 23.00 24.00 25.00 26.00 27.00 28.00 29.00 30.00 31.00 32.00 33.00 34.00 35.00 36.00 23.89 22.73 21.57 20.40 19.23 18.05 16.87 15.69 14.51 13.32 12.12 10.93 9.73 8.53 7.32 6.11 4.89 3.68 2.45 1.23 386.96 388.12 389.28 390.45 391.62 392.80 393.98 395.16 396.34 397.53 398.72 399.92 401.12 402.32 403.53 404.74 405.96 407.17 408.40 409.62 7,576.79 7,188.67 6,799.39 6,408.94 6,017.31 5,624.52 5,230.54 4,835.38 4,439.04 4,041.51 3,642.78 3,242.86 2,841.74 2,439.42 2,035.89 1,631.15 1,225.19 818.02 409.62 36.11 34.38 32.63 30.88 29.12 27.35 25.58 23.80 22.01 20.22 18.42 16.61 14.79 12.97 11.14 9.30 7.46 434.518,594.07 436.25 8,157.81 438.00 7,719.82 439.75 7,280.07 441.51 6,838.56 443.27 6,395.28 445.05 5,950.24 446.83 5,503.41 448.62 5,054.79 450.41 4,604.38 452.21 4,152.17 454.02 3,698.15 455.84 3,242.32 457.66 2,784.66 459.49 2,325.17 461.33 1,863.84 463.17 1,400.67 465.03 935.64 466.89 468.75 468.75 -0.00 5.60 3.74 1.88 44 Total 790.57 14,000.00 1,192.63 15,750.00 45 Sheet1 Sheet2 Sheet4 Sheets Sheets

Formula Page:

J U MIRIJA UUR HUNT JELUTON Defined Names Wine Function Library > Remove Arrows Evaluate Formula Formula Auditing Years Cost 17500 17500 2 Bank A 3 Bank B Down Payment =B2*20% =B3*10% Loan Amount =B2-C2 =B3-C3 Int Rate 0.036 0.048 G Monthly Payment - PMT(E2/12,F212,D2,0,0) --PMT(E3/12,F3*12, D3,0,0) 3 6. Month 71 82 9 3 10 4 115 126 137 14 8 15 9 Monthly interest =D2E2/12 =D7*SES2/12 =D8*SE$2/12 =D9*$E$2/12 =D10 SE$2/12 =D11"$E$2/12 =D12*$E$2/12 =D13*SES2/12 =D14 SE$2/12 =D15*SE$2/12 =D16 SE$2/12 =D17" SES2/12 =D18 SES2/12 =D19*SES 2/12 =D20 SE$2/12 =D21"SES2/12 =D22SES2/12 Sheet1 Sheet2 Bank A Balance Reduction Loan Balance =$G$2-B7 =D2-C7 =$G$2-B8 =D7-08 =$G$2-09 D8-C9 =$G$2-B10 =D9-C10 =$G$2-B11 =D10-C11 -SGS2-B12 =D11-C12 =SG$2-B13 =D12-C13 =$G$2-B14 =D13-C14 =$G$2-B15 =D14-C15 =SG$2-B16 =D15-C16 =$G$2-B17 =D16-C17 =$G$2-B18 =D17-C18 =SGS2-B19 =D18-C19 =$G$2-B20 =D19-C20 =SG$2-B21 =D20-021 =SGS2-B22 =D21-C22 =SGS2-B23 =D22-C23 Sheet4 Sheets Sheet + Monthly Interest =D3*E3/12 =H7*SE$3/12 =H8*$E$3/12 =H9*SE$3/12 =H10*SE$/12 =H11 "SE$3/12 =H12*$E$3/12 =H13 SE$3/12 =H14 SE$3/12 =H15 $E$3/12 =H16*SE$3/12 =H17 SE$3/12 =H18 SE$3/12 =H19 $E$3/12 =H20"SE$3/12 =H21"$E$3/12 =H22 SES3/12 Bank B Balance Reduction =G3-F7 =$G$3-F8 =$G$3-F9 =$G$3-F10 =$G$3-F11 =$G$3-F12 =$G$3-F13 =$G$3-F14 =$G$3-F15 =$G$3-F16 =$G$3-F17 =$G$3-F18 =SG$3-F19 =$G$3-F20 =$G$3-F21 $G$3-F22 =SGS3-F23 Loan Balance =D3-67 =H7-G8 =H8-G9 =H9-610 =H10-G11 =H11-612 =H12-13 =H13-614 =H14-G15 =H15-16 =H16-17 =H17-G18 =H18-619 =H19-20 =H20-621 =H21-G22 =H22-G22 16 10 17 11 18 12 19 13 20 14 21 15 22 16 23 17 Ready Te here to search

Function ADOSUR Recenty Financial Logical Text Date & Lookup & Math & More Used - - Time - Reference. Trig. Functions Function Library Name Manager Create from Selection Defined Names e Dependents E rror Checking - Remove Arrows - Evaluate Formula Formula Auditing Watch Window 144 4A 23 17 24 18 25 19 26 20 27 21 28 22 29 23 30 24 31 25 32 26 33 27 34 28 35 29 36 30 37 31 38 32 39 33 40 34 41 35 42 36 =D22*$E$2/12 =D23$E$2/12 =D24*$E$2/12 =D25"$E$2/12 =D26*$E$2/12 =D27"$E$2/12 =D28 $E$2/12 =D29*$E$2/12 =D30*$E$2/12 =D31"$E$2/12 =D32*$E$2/12 =D33*$E$2/12 =D34 SE$2/12 =D35*$E$2/12 =D36*$E$2/12 =D37"SE$2/12 =D38*$E$2/12 =D39*$E$2/12 =D40 $E$2/12 =D41-SE$2/12 $G$2-B23 =$G$2-B24 =$G$2-B25 =$G$2-B26 =$G$2-B27 =$G$2-B28 =$G$2-B29 =$G$2-B30 =$G$2-B31 =$G$2-B32 =$G$2-B33 =$G$2-B34 =$G$2-B35 -SG$2-B36 =$G$2-837 =SG$2-B38 =$G$2-B39 =$G$2-340 =SG$2-141 =$G$2-842 =D22-C23 =D23-C24 =D24-C25 =D25-C26 =D26-C27 =D27-028 =D28-C29 =029-C30 =D30-C31 =D31-C32 =D32-C33 =D33-034 =D34-C35 =D35-036 =D36-C37 =D37-C38 =D38-C39 =D39-040 =D40-041 =D41-042 =H22*$E$3/12 =H23*$E$3/12 =H24 $E$3/12 =H25*$E$3/12 =H26*$E$3/12 =H27*$E$3/12 =H28*SE$3/12 =H29"$E$3/12 =H30*$E$3/12 =H31 "SE$3/12 =H32*SE$3/12 =H33*$E$3/12 =H34*SE$3/12 =H35*$E$3/12 =H36*$E$3/12 =H37*SES3/12 =H38 SE$3/12 =H39*$E$3/12 =H40*SE$3/12 =H41 "SES3/12 =$G$3-F23 =$G$3-F24 =$G$3-F25 =$G$3-F26 =$G$3-F27 =$G$3-F28 =$G$3-F29 =$G$3-F30 =$G$3-F31 =SG$3-F32 =$G$3-F33 =$G$3-F34 =$G$3-F35 =$G$3-F36 =$G$3-F37 =$G$3-F38 =SGS3-F39 =$G$3-F40 =SGS3-F41 =$G$3-F42 =H22-G23 =H23-G24 =H24-G25 =H25-626 =H26-G27 =H27-G28 =H28-G29 =H29-G30 =H30-G31 =H31-G32 =H32-G33 =H33-34 =H34-G35 =H35-G36 =H36-G37 =H37-G38 =H38-G39 =H39-G40 =H40-G41 =H41-642 =SUM(FZ:F42) 44 Total =SUM(C7:042) =SUM(87:842) =SUM(G7:542) Sheet1 Sheet2 Sheet4 Sheet3 Sheets Ready BE Type here to search o Rio 3