Question

Amortization Table On April 1, 2018, Clyde Barrow Company bought a delivery truck for $54,000 on a 6%, 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, 2018. Prepare a monthly amortization table for the loan showing the payment date, payment, principal part of the payment, the interest portion of the payment, and the remaining balance. At the top of the table, be sure to show the calculation of the monthly payment and, below the table or to the side, show the monthly journal entry to record the first payment on May 1st. Everything should be set up so that if the interest rate or the principal of the loan were changed, the rest of the table will update appropriately. For an example of how to do this, google search amortization table in Excel, and you will find a lot of information on this. A link you can use is is listed below. Link To Amortization Table Video Be sure that the amortization table has a header, describing the report, and that it prints out in a clear readable format, one page wide. The table may be longer than one page so make sure that the report header will print on all pages. All numbers must be calculated using a formula in each cell. Except for the amount of the loan, interest rate, and number of payments, every number shown on the table is a product of a formula calculation. When submitting the file, be sure to put your name in the file name.

Answer 1

Book1 - Microsoft Excel - 2 X 2 Review View Developer - x = = VD General File Home Insert Page Layout Formulas Data * Cut Calibri 10 - A A = E Copy Paste Format Painter B I U S - A- Clipboard Font Num_Pmt_Per_Year fx 12 Wrap Text Merge & Center % 8.09 Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format AutoSumA Fill - Sort & 2 Clear Filter Editing Find & Select Alignment Number Cells E F G H I K L M в i Loan Amortization Schedule mo Enter values 54,000.00 6.00 % 5 Loan summary 1,043.97 60 60 Loan amount $ Annual interest rate Loan period in years Number of payments per year Start date of loan Optional extra payments Scheduled payment $ Scheduled number of payments Actual number of payments Total early payments $ Total interest $ 12! 01-04-2018 8,638.28 Lender name: pmt. Payment Date Beginning Balance Scheduled Payment Extra Payment Total Payment Principal Interest Ending Balance Cumulative Interest No. 18 $ $ $ $ 214 22 5 236 01-05-2018 $ 01-06-2018 $ 01-07-2018 $ 01-08-2018 $ 01-09-2018 $ 01-10-2018 $ 01-11-2018 $ 01-12-2018 $ 01-01-2019 $ 01-02-2019 $ Loan Amortization Schedule 2 54,000.00 $ 53,226.03 $ 52,448.19 $ 51,666.46 $ 50,880.82 50,091.25 $ 49,297.74 $ 48,500.25 $ 47,698.78 $ 46,893.31 $ Sheet4 Sheets 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 $ 1,043.97 $ 1,043.97 $ 1,043.97 $ 1,043.97 $ 1,043.97 1,043.97 1,043.97 $ 1,043.97 $ 1,043.97 $ 773.97 $ 777.84 $ 781.73 $ 785.64 $ 789.57 $ 793.52 $ 797.48 $ 801.47 $ 805.48 $ 809.50 $ 270.00 $ 266.13 $ 262.24 $ 258.33 $ 254.40 250.46 246.49 $ 242.50 $ 238.49 $ 234.47 $ 53,226.03 $ 52,448.19 $ 51,666.46 $ 50,880.82 $ 50,091.25 $ 49,297.74 $ 48,500.25 $ 47,698.78 $ 46,893.31 $ 46,083.80 $ 270.00 536.13 798.37 1,056.70 1,311.11 1,561.56 1,808.05 2,050.55 2,289.05 2,523.51 24 7 26 9 $ $ $ 27 10 Ready Type here to search o 000 100% 0 + - 10:35 Ana x ENG 16-11-2019 F2 W OP @ 92

Book1 - Microsoft Excel - 2 X 2 Review View Developer - x = = VD General File Home Insert Page Layout Formulas Data * Cut Calibri 10 - A A = E Copy Paste Format Painter B I U - A- Clipboard Font Num_Pmt_Per_Year fx 12 Wrap Text Merge & Center - 9 - % % 48.00 0.8 Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format AutoSumA Fill - Sort & 2 Clear Filter Editing Find & Select Alignment Number Cells E F G H I K L M - в i Loan Amortization Schedule mo Enter values 54,000.00 6.00 % 5 Loan summary 1,043.97 60 60 Loan amount $ Annual interest rate Loan period in years Number of payments per year Start date of loan Optional extra payments Scheduled payment $ Scheduled number of payments Actual number of payments Total early payments $ Total interest $ 12! 01-04-2018 8,638.28 Lender name: Pmt. Payment Date Beginning Balance Scheduled Payment Extra Payment Total Payment Principal Interest Ending Balance Cumulative Interest 16 No. 27 10 28 11 29 12 30 13 31 14 32 15 $ $ $ $ 01-02-2019 $ 01-03-2019 $ 01-04-2019 $ 01-05-2019 $ 01-06-2019 $ 01-07-2019 $ 01-08-2019 $ 01-09-2019 $ 01-10-2019 $ 01-11-2019 $ Loan Amortization Schedule 2 46,893.31 $ 46,083.80 $ 45,270.25 $ 44,452.63 43,630.92 42,805.10 $ 41,975.16 $ 41,141.06 $ 40,302.80 $ 39,460.34 $ Sheet4 Sheets 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 $ 1,043.97 $ 1,043.97 $ 1,043.97 $ 1,043.97 $ 1,043.97 1,043.97 1,043.97 $ 1,043.97 $ 1,043.97 $ 809.50 $ 813.55 $ 817.62 $ 821.71 $ 825.82 $ 829.95 $ 834.10 $ 838.27 $ 842.46 $ 846.67 $ 234.47 $ 230.42 $ 226.35 $ 222.26 $ 218.15 $ 214.03 209.88 $ 205.71 $ 201.51 $ 197.30 $ 46,083.80 $ 45,270.25 $ 44,452.63 $ 43,630.92 $ 42,805.10 $ 41,975.16 $ 41,141.06 $ 40,302.80 $ 39,460.34 $ 38,613.67 $ 2,523.51 2,753.93 2,980.28 3,202.55 3,420.70 3,634.73 3,844.60 4,050.31 4,251.82 4,449.12 . 34 17 35 18 36 19 $ $ $ Ready o 000 100% 0 - 10:36 ^ a (x ENG 16-11-2019 Type here to search W * O P e 92 + E 52

Book1 - Microsoft Excel - 2 X 2 Review View Developer - x = = VD General File Home Insert Page Layout Formulas Data * Cut Calibri 10 - A A = E Copy Paste Format Painter B I U - A- Clipboard Font Num_Pmt_Per_Year fx 12 Wrap Text Merge & Center - 9 - % % 48.00 0.8 Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format AutoSumA Fill - Sort & 2 Clear Filter Editing Find & Select Alignment Number Cells E F G H I K L M в i Loan Amortization Schedule mo Enter values 54,000.00 6.00 % 5 Loan summary 1,043.97 60 60 Loan amount $ Annual interest rate Loan period in years Number of payments per year Start date of loan Optional extra payments Scheduled payment $ Scheduled number of payments Actual number of payments Total early payments $ Total interest $ 12! 01-04-2018 8,638.28 Lender name: Pmt. Payment Date Beginning Balance Extra Payment Total Payment Principal Interest Ending Balance Cumulative Interest 16 No. 69 52 $ $ $ $ 70 53 71 54 72 55 73 56 74 57 01-08-2022 $ 01-09-2022 $ 01-10-2022 $ 01-11-2022 $ 01-12-2022 $ 01-01-2023 $ 01-02-2023 $ 01-03-2023 $ 01-04-2023 $ 9,165.09 8,166.94 7,163.81 6,155.66 5,142.46 4,124.20 3,100.85 2,072.39 1,038.78 Scheduled Payment 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 $ $ $ 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,043.97 1,038.78 $ $ $ $ $ $ $ $ $ 998.15 $ 1,003.14 $ 1,008.15 $ 1,013.19 $ 1,018.26 $ 1,023.35 $ 1,028.47 $ 1,033.61 $ 1,033.58 $ 45.83 $ 40.83 $ 35.82 $ 30.78 $ 25.71 $ 20.62 $ 15.50 $ 10.36 $ 5.19 $ 8,166.94 $ 7,163.81 $ 6,155.66 $ 5,142.46 $ 4,124.20 $ 3,100.85 $ 2,072.39 $ 1,038.78 $ - $ 8,453.45 8,494.29 8,530.11 8,560.88 8,586.60 8,607.22 8,622.72 8,633.08 8,638.28 76 59 $ $ $ $ $ $ $ Loan Amortization Schedule Sheet4 Sheets Ready 00) 100% uc 10:36 ^ a 4x ENG 16-11-2019 + E 52 Type here to search

Insert Loan Amortization from Spreadsheet Solutions in Excel as below:

Book1 - Microsoft Excel File Home Insert Page Layout Formulas Data Review View Developer - x % Cut = = D General Calibri E Copy Paste Format Painter B I Clipboard B24 - - 11 - A À 3- A Wrap Text Merge & Center - U. * 9 % Insert Delete Format 88 AutoSum A Fill - Sort & Find & 2 Clear Filter Select Editing Formatting as Table Styles Styles Alignment Number Cells Font for A B I c D E F G H I J K L M N O P Q R S T Insert General Spreadsheet Solutions Preview Billing Statement Blood Pressure Tracker Expense Report Loan Amortization i Loan Amortization Sched Personal Sales Report Time Card Analista Numro parte per Startalom Templates on Office.com OK Cancel Loan Amortization Schedule Sheet4 Sheets KT N Ready BO 100% 0 Type here to search ^ 10:38 (x ENG 16-11-2019 F2

Journal Entry for recording First Payment will be as follows :

01-05-2018 Truck Loan Dr. $773.97

Interest Expense Dr. $270

To Bank $1043.97