Question

On April 1, 2020, Clyde Barrow Company bought a delivery truck for $54,000 on a 8%, five-year installment note.  You have been tasked with preparing an amortization table in Excel for the bookkeeper to assist him in recording the monthly payments, with the first payment starting on May 1, 2020.

Answer 1

Please, do upvote for the answer. Thank you.

Answer is given first with journal entry and then excel screenshot Cell Loan amount $54,000 H5 period 60 months Interest rate 6.00% H7 EMI $1,044 monthly payment Formula -PMT(H7/12,60, H5) 60 is 60 months divided by 12 for monthly interest rate Note: Interest at 6 % is calculated on beginning balance Principal repayment = EMI- Interest payment EMI $254 Loan Amortization Schedule for the Chase Loan Loan Outstanding Period amount Interest Principal Balance (Month) Beginning at 6% Repayment end of each of month month 1 $54,000 $1,044 $270 $774 $53,226 2 $53,226 $1,044 $266 $778 $52,448 3 $52,448 $1,044 $262 $782 $51,666 4 $51,666 $1,044 $258 $786 $50,881 5 $50,881 $1,044 $790 $50,091 6 $50,0911 $1,044 $250 $794 $49,298 7 $49,298 $1,044 $246 $797 $48,500 8 $48,500 $1,044 $243 $801 $47,699 9 $47,699 $1,044 $238 $805 $46,893 10 $46,893 $1,044 $234 $810 $46,084 11 $46,084 $1,044 $230 $814 $45,270 12 $45,270 $1,044 $226 $818 $44,453 13 $44,453 $1,044 $222 $822 $43,631 14 $43,631 $1,044 $218 $826 $42,805 15 $42,805 $1,044 $214 $830 $41,975 16 $41,975 $1,044 $210 $834 $41,141 17 $41,141 $1,044 $206 $838 $40,303 18 $40,303 $1,044 $202 $842 $39,460 19 $39,460 $1,044 $197 $847 $38,614 20 $38,614 $1,044 $193 $851 $37,763 21 $37,763 $1,044 $189 $855 $36,908 22 $36,908 $1,044 $185 $859 $36,048 23 $36,048 $1,044 $180 $864 $35,184 24 $35,184 $1,044 $176 $868 $34,316 25 $34,316 $1,044 $172 $872 $33,444 26 $33,444 $1,044 $167 $877 $32,567 27 $32,5671 $1,044 $163 $881 $31,686 28 $31,686 $1,044 $158 $886 $30,801 29 $30,801 $1,044 $154 $890 $29,911 30 $29,911 $1,044 $150 $894 $29,016 31 $29,016 $1,044 $145 $899 $28,117 32 $28,117| $1,044 $141 $903 $27,214

33 34 35 36 37 38 $136 $132 $127 $122 $118 $113 $108 $104 $99 $94 $90 39 40 41 42 43 44 $85 45 46 $27,214 $26,306 $25,394 $24,477| $23,555 $22,629 $21,698 $20,762 $19,822 $18,877 $17,928 $16,974 $16,014 $15,051 $14,082 $13,108 $12,130 $11,147 $10,158 $9,165 $8,1671 $7,164 $6,156 $5,142 $4,124 $3,101 $2,072 $1,039 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $1,044 $908 $912 $917 $922| $926 $931 $935 $940 $945 $950 $954 $959 $964 $969 $974 $978 $983 $988 $993 $998 $1,003 $1,008 $1,013 $1,018 $1,023 $1,028 $1,034 $1,039 $26,306 $25,394 $24,477 $23,555 $22,629 $21,698 $20,762 $19,822 $18,877 $17,928 $16,974 $16,014 $15,051 $14,082 $13,108 $12,130 $11,147 $10,158 $9,165 $8,167 $7,164 $6,156 $5,142 $4,124 $3,101 $2,072 $1,039 50 47 48 49 50 $80 $75 $70 $66 $61 $56 $51 $46 $41 $36 $31 $26 $21 $16 $10 $5 51 52 53 54 55 56 57 58 59 60

Credit Journal entry Date General journal May 1 Note payable Interest expense Cash Debit $774 $270 $1,044 D K Cell H5 3 4 5 6 7 8 Loan amount Iperiod Interest rate EMI Formula H7 54000 60 months 10.06 --PMT(H7/12,60, H5) monthly paymer -PMT(H7/12,60, H5) 60 is 60 months divided by 12 for mont 9 10 11 12 13 Note: Interest at 6% is calc Principal repayment 14 15 16 Loan Amortization Schedule for the Chase Loan Period (Month) EMI Interest at 6% Principal Repayment Outstanding Balance end of each month 17 18 Loan amount Beginning of month =H5 =K18 =K19 =K20 =K21 1=K22 =H8 =H18 =H19 1 =F18+1 =F19+1 =F20+1 =F21+1 =F22+1 19 20 21 22 23 =G18*H$7/12 =G19"H$7/12 =G20ºH$7/12 =G21*H$7/12 =G22*H$7/12 =G23*H$7/12 =H18-118 =H19-119 =H20-120 =H21-121 =H22-122 =H23-123 =G18-J18 =G19-219 =G20-J20 =G21-J21 =G22-J22 =G23-123 =H20 =H21 =H22 D E F G H 1 K 22 23 24 25 26 27 28 =H21 =H22 =H23 =H24 =H25 =H26 =H27 =H28 =H29 =H30 =H31 29 30 31 =F21+1 =F22+1 =F23+1 =F24+1 =F25+1 =F26+1 =F27+1 =F28+1 =F29+1 =F30+1 =F31+1 =F32+1 =F33+1 =F34+1 =F35+1 =F36+1 =F37+1 =F38+1 =F39+1 =F40+1 =F41+1 =K21 =K22 =K23 I=K24 =K25 =K26 =K27 =K28 =K29 =K30 =K31 =K32 =K33 I=K34 =K35 =K36 =K37 =K38 =K39 32 33 =H32 =G22*H$7/12 =G23*H$7/12 =G24"H$7/12 =G25*H$7/12 =G26*H$7/12 =G27*H$7/12 =G28*H$7/12 =G29*H$7/12 =G30*H$7/12 =G31 "H$7/12 =G32*H$7/12 =G33*H$7/12 =G34*H$7/12 =G35*H$7/12 =G36*H$7/12 =G37*H$7/12 =G38*H$7/12 =G39*H$7/12 =G40*H$7/12 =G41*H$7/12 =G42*H$7/12 =G43*H$7/12 =G44*H$7/12 =G45*H$7/12 =H22-122 =H23-123 =H24-124 =H25-125 =H26-126 =H27-127 =H28-128 =H29-129 =H30-130 =H31-131 =H32-132 =H33-133 =H34-134 =H35-135 =H36-136 =H37-137 =H38-138 =H39-139 =H40-140 =H41-141 =H42-142 =H43-143 =H44-144 =H45-145 =G22-J22 =G23-J23 =G24-J24 =G25-J25 TEG26-J26 =G27-127 =G28-J28 =G29-29 =G30-J30 =G31-331 =G32-J32 | =G33-J33 =G34-J34 =G35-J35 =G36-J36 =G37-137 1=G38-138 =G39-J39 =G40-140 =G41-341 I=G42-342 =G43-143 =G44-344 1=G45-145 34 =H33 =H34 35 36 =H35 37 38 =H36 =H37 =H38 39 40 41 =K40 42 =H39 =H40 =H41 =H42 =H43 =H44 43 =K41 I=K42 =K43 I=K44 =F42+1 44 =F43+1 =F44+1 45 D E F H K 54 =K53 55 =K54 =K55 56 57 58 59 =K56 =K57 =K58 =K59 =K60 =K61 60 61 =H53 =H54 =H55 =H56 =H57 =H58 =H59 =H60 =H61 =H62 =H63 =H64 =H65 =H66 =H67 62 63 =F53+1 =F54+1 =F55+1 =F56+1 =F57+1 =F58+1 |=F59+1 =F60+1 =F61+1 =F62+1 |=F63+1 =F64+1 =F65+1 =F66+1 =F67+1 =F68+1 =F69+1 =F70+1 =F71+1 =F72+1 =F73+1 =F74+1 =F75+1 =F76+1 64 65 =K62 =K63 1=K64 =K65 =K66 =G54*H$7/12 =G55*H$7/12 1=G56*H$7/12 =G57*H$7/12 =G58*H$7/12 =G59*H$7/12 =G60*H$7/12 =G61*H$7/12 =G62*H$7/12 =G63*H$7/12 =G64*H$7/12 =G65*H$7/12 =G66*H$7/12 =G67*H$7/12 =G68*H$7/12 =G69*H$7/12 =G70*H$7/12 =G71H$7/12 =G72*H$7/12 =G73*H$7/12 =G74*H$7/12 =G75*H$7/12 =G76*H$7/12 1=G77*H$7/12 J =H54-154 =H55-155 =H56-156 =H57-157 =H58-158 =H59-159 =H60-160 =H61-161 =H62-162 =H63-163 =H64-164 =H65-165 =H66-166 |=H67-167 =H68-168 =H69-169 I=H70-170 I=H71-171 =H72-172 =H73-173 =H74-174 =H75-175 =H76-176 =H77-177 =G54-354 =G55-355 =G56-156 =G57-157 =G58-J58 =G59-359 =G60-360 =G61-361 =G62-J62 =G63-J63 =G64-J64 =G65-J65 =G66-J66 =G67-J67 =G68-J68 =G69-369 =G70-270 =G71-J71 =G72-172 =G73-J73 =G74-174 =G75-J75 =G76-176 =G77-177 66 67 68 69 =H68 70 =H69 =H70 71 72 =K67 =K68 =K69 I=K70 =K71 =K72 =K73 =K74 =K75 =H71 73 74 =H72 =H73 =H74 75 76 =H75 77 =K76 =H76