Generate an Amortization Table

You need to use the loan information to generate an amortization table for the life of the loan.

1. Add the reference to the initial Beginning Balance in B8.
2. Add the reference to the Monthly Payment in C8.
3. Insert a formula in D8 to calculate the Interest Paid.
4. Insert a formula in E8 to calculate the Principal Payment.
5. Insert a formula in F8 to calculate the Ending Balance.
6. Insert a formula in B9 to populate the new Beginning Balance.
7. Use the fill handle to copy the formulas to the remainder of the table.

AutoSave af! OF AA v GF ... 2 Draw Page Layout Formulas Home Insert Data Review View Developer Tell me Share Comments R! Queries & Connections V 4 A BE Data from Picture = 21 . Y 21 Sart Filter Clear a Reapply Advanced Properties Edit Links of Group Ungroup PE H2 What if Analysis Analysis Tools + Data Analysis 2 Salver Refresh All Stocks Currencies Geography Automatis Get Data (Power Query] C11 Text to E Columns Subtotal A B C D F G K L M N 0 o P Q R Input Output 1 2 Payment: 3 APR: 4 Years: 5 Pmts per Year: 6 $3,000.00 3.75% 5 12 Loan Amount Periodic Rate: # of Payments: $163,899.33 0.3125% 60 Monthly Payment Principal Payment Ending Balance Interest Pald 7 Payment 49 50 51 52 53 54 Beginning Balance 421 43 44 45 46 47 48 49 50 51 52 53 54 55 56 55 56 57 58 59 60 I Show Detal 61 62 63 64 65 66 0888 67 68 69 70 71 72 73 74 Inventory Loan Scenario Summary 2 + Ready 126% AutoSave OF: AA OF... Home Insert Draw Review Share Comments Page Layout Formulas Data - R! Queries & Connections V View Developer Tell me 19 4 Currencies Geography Automatis 21 Y Y Clea RE H2 A BE Data from Picture of Group Ungroup UTI Get Data (Power Query] C11 Properties Edit Links Refresh All Analysis Tools + Data Analysis 2 Salver Stocks & Reapply Advanced 21 Sort Filter Text to E Columns What if Analysis Subtotal A B C D F G I K L M N 0 o P a R Input Output 1 2 Payment: 3 APR: 4 Years: 5 Pmts per Year: 6 $3,000.00 3.75% 5 12 Loan Amount Periodic Rate: # of Payments: $163,899.33 0.3125% 60 7 Payment 8 9 10 Beginning Monthly Principal Ending Balance Payment Interest Pald Payment Balance 1 $163,899.33 $3,000.00 $512.19 $2,487.81 $161,411.52 2 $161,411.52 $3,000.00 $504.41 $2,495.59 $158,915.93 3 3 $158,915.93 $3,000.00 4 51 5 6 7 7 3 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Loan Scenario Summary 2 + Inventory 1 Ready 125% AutoSave af! OF AA v GF ... 2 Draw Page Layout Formulas Home Insert Data Review View Developer Tell me Share Comments R! Queries & Connections V 4 A BE Data from Picture = 21 . Y 21 Sart Filter Clear a Reapply Advanced Properties Edit Links of Group Ungroup PE H2 What if Analysis Analysis Tools + Data Analysis 2 Salver Refresh All Stocks Currencies Geography Automatis Get Data (Power Query] C11 Text to E Columns Subtotal A B C D F G K L M N 0 o P Q R Input Output 1 2 Payment: 3 APR: 4 Years: 5 Pmts per Year: 6 $3,000.00 3.75% 5 12 Loan Amount Periodic Rate: # of Payments: $163,899.33 0.3125% 60 Monthly Payment Principal Payment Ending Balance Interest Pald 7 Payment 49 50 51 52 53 54 Beginning Balance 421 43 44 45 46 47 48 49 50 51 52 53 54 55 56 55 56 57 58 59 60 I Show Detal 61 62 63 64 65 66 0888 67 68 69 70 71 72 73 74 Inventory Loan Scenario Summary 2 + Ready 126% AutoSave OF: AA OF... Home Insert Draw Review Share Comments Page Layout Formulas Data - R! Queries & Connections V View Developer Tell me 19 4 Currencies Geography Automatis 21 Y Y Clea RE H2 A BE Data from Picture of Group Ungroup UTI Get Data (Power Query] C11 Properties Edit Links Refresh All Analysis Tools + Data Analysis 2 Salver Stocks & Reapply Advanced 21 Sort Filter Text to E Columns What if Analysis Subtotal A B C D F G I K L M N 0 o P a R Input Output 1 2 Payment: 3 APR: 4 Years: 5 Pmts per Year: 6 $3,000.00 3.75% 5 12 Loan Amount Periodic Rate: # of Payments: $163,899.33 0.3125% 60 7 Payment 8 9 10 Beginning Monthly Principal Ending Balance Payment Interest Pald Payment Balance 1 $163,899.33 $3,000.00 $512.19 $2,487.81 $161,411.52 2 $161,411.52 $3,000.00 $504.41 $2,495.59 $158,915.93 3 3 $158,915.93 $3,000.00 4 51 5 6 7 7 3 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Loan Scenario Summary 2 + Inventory 1 Ready 125%