(THERE IS NO ADDITIONAL INFORMATION PROVIDED OR NEEDED, SIMPLYNEED THIS CREATED IN EXCEL)

Credit Card Amortization Schedule (MUST BE IN EXCEL)

Starter File Name: None

Difficulty: Level 3

Credit cards can offer people considerable flexibility when itcomes to purchasing power. However, this flexibility usually comesat a cost through a high APR on any unpaid balances. If only theminimum payment is made on a credit card balance, this debt couldbe carried for many years and cost thousands of dollars in interestexpense. The purpose of this exercise is to design an amortizationschedule for a credit card where only the minimum payment is made.Assume the bank charges an APR of 16% and that the current balanceon the credit card is $5,000. The minimum payment is established ona fourteen year repayment period and payments are made at the endof each month. The design of your amortization table must includethe following:

1. Design is limited to one worksheet.

2. Assume credit card statements are issued on the first day ofeach month. Show the statement date for each payment and thestatement month on the amortization schedule. The date for thefirst statement is 9/1/2020.

3. For each payment, show the total payment, how much of thepayment is interest expense, and how much is used to reduce thebalance of the credit card. The schedule should also show thecredit card balance at the beginning of every month.

4. Once the amortization table is complete, use the data to createa PivotTable on a second worksheet. The PivotTable should show howmuch total interest is paid each year and the total interest paidon the $5,000 balance after fourteen years. Also show the totalamount of principal paid each year. The row heading should be theyear sorted in chronological order beginning with 2020. Finally,show the number of payments made each year. Since the firststatement is 9/1/2020, the year 2020 will have four payments.

5. Use column and/or row headings, add titles to your worksheets,and rename the worksheet tabs with appropriate labels. Also, useappropriate number formats on both the amortization worksheet andthe PivotTable.

Answer rating: 100% (QA)

To accomplish the task of creating an amortization schedule for a credit card in Excel follow these instructions The focus will be on constructing the amortization table as described in your question ... View the full answer