Question

In: Accounting

Create an Excel spreadsheet that can do the calculations for the credit card below. Using formulas...

Create an Excel spreadsheet that can do the calculations for the credit card below. Using formulas and the drag function of Excel (see the notes), find the values for 1 year of charges. You can start this by taking the finance charge in month 1.

With a beginning balance of $2500 and no additional charges, there is no grace-period, you will just pay the minimum payment each month for the next year. The APR is 24.99% and the minimum payment each month is 8% of the balance. Determine the finance charge, new balance, and minimum payment required for each of the next year, and the starting balance for month 13:

a. What is the total of the minimum payments?
b. What is the total of the finance charges?
c. How much does the starting balance decrease after one year of payments?
d. The total of your finance charges is what percent of your total payments?
e. Explain why making the minimum payment all the time is great for the credit card company, but not so great for the user.

Solutions

Expert Solution

Answer is as under:

Month
A.
Opening Balance
B.
Minimum Due
(8% of Outstanding)
C. (8% od B)
Payment
(Equal to minimum Due)
D.
Remaining Balance
E. = B - D
Finance cost @24.99% annual
F = E*24.99%/12
New Outstanding
G = E + F
Month 1                   2,500.00                 200.00                       200.00                     2,300.00                           47.90                     2,347.90
Month 2                   2,347.90                 187.83                       187.83                     2,160.07                           44.98                     2,205.05
Month 3                   2,205.05                 176.40                       176.40                     2,028.65                           42.25                     2,070.89
Month 4                   2,070.89                 165.67                       165.67                     1,905.22                           39.68                     1,944.90
Month 5                   1,944.90                 155.59                       155.59                     1,789.30                           37.26                     1,826.57
Month 6                   1,826.57                 146.13                       146.13                     1,680.44                           35.00                     1,715.44
Month 7                   1,715.44                 137.23                       137.23                     1,578.20                           32.87                     1,611.07
Month 8                   1,611.07                 128.89                       128.89                     1,482.18                           30.87                     1,513.05
Month 9                   1,513.05                 121.04                       121.04                     1,392.01                           28.99                     1,420.99
Month 10                   1,420.99                 113.68                       113.68                     1,307.31                           27.22                     1,334.54
Month 11                   1,334.54                 106.76                       106.76                     1,227.78                           25.57                     1,253.34
Month 12                   1,253.34                 100.27                       100.27                     1,153.08                           24.01                     1,177.09
Month 13                   1,177.09
Total              1,739.50                   1,739.50                        416.59                  20,420.82

a. Total of minimum payment is $ 1,739.50

b. Total of Finance Charges is $ 416.59

c. Starting balance after 1 year is $ 1,777.09 (Decrease $ 1,322.91 (Original $ 2,500.00 less after one year $ 1,177.09)

d.23.95% ( Total finance charges divided by total payment; 416.59 divided by 1739.50)

e. Credit card company gets a high rate of Interest on outstanding amount as well as on payment of minimum due they assume that customer is live and will pay remaining balance so no need to create provision for bad-debts.

It is not good for user as user has to pay high interest cost.


Related Solutions

Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of the preceding requirements. Show how the solution will change if the following data change: the April 1 work-in-process costs were $66,000 for direct material and $18,000 for conversion. Work in process, April 1—10,000 units: Direct material: 100% complete, cost of .........................................................................................................$ 22,000 Conversion: 20% complete, cost of ................................................................................................................. 4,500 Balance in work in process, April 1 ..................................................................................................................$  26,500 Units started during April ......................................................................................................................................100,000 Units...
USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for...
USING EXCEL FORMULAS SOLVE THE PROBLEM. MUST USE EXCEL CALCULATIONS AND FORMULAS.!!! Find the data for the problem in the first worksheet named LightbulbLife of the data table down below It gives the data on the lifetime in hours of a sample of 50 lightbulbs. The company manufacturing these bulbs wants to know whether it can claim that its lightbulbs typically last more than 1000 burning hours. So it did a study. Identify the null and the alternate hypotheses for...
Calculations must be done in Excel – You must create your own spreadsheet do not copy...
Calculations must be done in Excel – You must create your own spreadsheet do not copy and paste someone else’s. Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three year contract. The new machine will cost $1.9 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $120,000. The contract will not continue beyond three years and...
Project 2 Calculations must be done in Excel – You must create your own spreadsheet (do...
Project 2 Calculations must be done in Excel – You must create your own spreadsheet (do not copy and paste someone else’s). This question should be done using Method 1 as outlined in lecture 6 (i.e. Tax Effects, then Cash Flows then NPV) As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: - The project has a useful life of 12 years. Land costs $6m and is estimated to have...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create a new data set that is a subset of this larger provided data set. The new data set should include the dependent variable (VALUE) and the following independent variables (AGE, LOTSIZE, RMS, MOD KITCH, MOD BATH, AIRCON and FIREPL). b. For this new data set, use the Correlation option of Data Analysis in Excel and produce the correlation matrix associated with the dependent variable...
An Excel spreadsheet using formulas and cell referencing is required for this assignment. Careen owns a...
An Excel spreadsheet using formulas and cell referencing is required for this assignment. Careen owns a condominium near Hershey, PA that she rents out part of the year. This year, she incurs the following expenses in connection with her rental condo: Mortgage Interest $9,490 Property Taxes 6,570 Insurance 3,220 Repairs and maintenance 1,610 Utilities 3,703 Depreciation 11,270    During the year, Careen rented the condo for 112 days, receiving $17,600 of gross income. She personally used the condo for 49...
How do you display all the formulas in an Excel spreadsheet? There is a toggle switch...
How do you display all the formulas in an Excel spreadsheet? There is a toggle switch and you start by pressing CTRL and then another character. What is the other character to allow you to display all the formulas in your spreadsheet at once?
, Excel allows us to create our own formulas and functions to perform calculations and solve...
, Excel allows us to create our own formulas and functions to perform calculations and solve problems within the spreadsheet. Which of the functions or formulas that you experimented with in Presentation 1 did you find the most valuable? How do you think this particular function will save time in using Excel spreadsheets? What other functions do you think you will use on a regular basis? Why? Explain. Was there a function that is new to you?
For this problem, you must use Excel to perform the necessary calculations. Below are the formulas...
For this problem, you must use Excel to perform the necessary calculations. Below are the formulas and equations you will need to use. Click on the image below to download a power point version if the text is blurry or too small. If it is appropirate to use the binomial distribution, use the formula=binom.dist(number_s,trails,probability,cumulative) to calculate the probability. In this formula, number_s is the number of successful trails, trails is the total number of trails, probability is the probability for...
Make Excel do all calculations, using cell addresses. Don’t type numbers in your formulas. Report the...
Make Excel do all calculations, using cell addresses. Don’t type numbers in your formulas. Report the answers to the questions in your worksheet using the appropriate symbols (µ σ) and notation p(x>=5), p(X<3) etc...             Use Insert/Symbol to find mu and sigma for mean and std dev.                                     x P(x) 1. An auditor for Health Maintenance Services of Georgia reports 30 percent of policyholders 55 years or older submit a claim during the year. Twelve policyholders are randomly selected for...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT