Question

(THERE IS NO ADDITIONAL INFORMATION PROVIDED OR NEEDED, SIMPLY NEED 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 it comes to purchasing power. However, this flexibility usually comes at a cost through a high APR on any unpaid balances. If only the minimum payment is made on a credit card balance, this debt could be carried for many years and cost thousands of dollars in interest expense. The purpose of this exercise is to design an amortization schedule for a credit card where only the minimum payment is made. Assume the bank charges an APR of 16% and that the current balance on the credit card is $5,000. The minimum payment is established on a fourteen year repayment period and payments are made at the end of each month. The design of your amortization table must include the following:

1. Design is limited to one worksheet.

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

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

4. Once the amortization table is complete, use the data to create a PivotTable on a second worksheet. The PivotTable should show how much total interest is paid each year and the total interest paid on the $5,000 balance after fourteen years. Also show the total amount of principal paid each year. The row heading should be the year sorted in chronological order beginning with 2020. Finally, show the number of payments made each year. Since the first statement 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, use appropriate number formats on both the amortization worksheet and the PivotTable.

Answer 1

Pivot Table: