Help projecting actual credit card costs

Justustinin

Hi,
I've just had the lovely surprise of my wife confessing to having two credit cards she didn't want to tell me about and running us up a lovely debt of £6000. I've never used credit cards in my life and am now sitting down to build an excel sheet to work out a repayment plan. I know credit cards can by tricky things so what I could really do with is someone telling me if this is the correct way to project balances and interest over a repayment period.

What I figured was you've got your initial sum on each card: £3000
Note the APR Interest charge: 18.9%
Then you set your repayment period goal: 36 months

Then you calculate the balance on the 1st year as:
(((initial sum / 100) * APR) + initial sum) = Total debt including interest
Then to work out how much I'll paid off and get the final balance for yr 1:
Total including interest - (monthly payment *12) = outstanding balance at end of year 1.

Then calculate year two and three of repayment in the same way, except substituting initial sum for outstanding balance.


My Question is this: Is this how to work out interest and repayment on credit cards? If not, what is the correct way to project these figures. I'm not using an online calculator because I want to see the mechanics and work out how it plays into our wider financial picture and also, look at other methods of paying it off quickly.

While I appreciate I can move the debt to other cards, in this instance it's not possible so I need to get a firm handle on the debt in it's present state and then work form there.

Thanks for taking the time to read this. Helpful advice on the Maths is appreciated!

SeduLOUs

Try this http://www.bankrate.com/calculators/credit-cards/credit-card-payoff-calculator.aspx?MSA=

Oops, saw you don't want to use an online. But if you use this as a starting point you can multiply back up the monthly payments and see how much the £6k costs overall?

Justustinin

Thanks SeduLOUs,

That's helpful. The calculator seems to produce results similar to mine, so I can't be too far off.

Cheers!

grumbler

Justustinin wrote: »

What I figured was you've got your initial sum on each card: £3000
Note the APR Interest charge: 18.9%

Monthly rate is =(1+18.9%)^(1/12)-1
1.453%

Then you set your repayment period goal: 36 months

Monthly payment is =PMT(1.453%,36,3000)
£107.61

Then you calculate the balance on the 1st year as:
(((initial sum / 100) * APR) + initial sum) = Total debt including interest
My Question is this: Is this how to work out interest and repayment on credit cards? If not, what is the correct way to project these figures. I'm not using an online calculator because I want to see the mechanics and work out how it plays into our wider financial picture and also, look at other methods of paying it off quickly.

While I appreciate I can move the debt to other cards, in this instance it's not possible so I need to get a firm handle on the debt in it's present state and then work form there.

Thanks for taking the time to read this. Helpful advice on the Maths is appreciated!

[FONT=Courier New][SIZE=1]
                1.453%    
1    3000.00    43.59    -107.61
2    2935.98    42.66    -107.61
3    2871.03    41.72    -107.61
4    2805.14    40.76    -107.61
5    2738.28    39.79    -107.61
6    2670.46    38.80    -107.61
7    2601.65    37.80    -107.61
8    2531.85    36.79    -107.61
9    2461.02    35.76    -107.61
10    2389.17    34.71    -107.61
11    2316.28    33.66    -107.61
12    2242.32    32.58    -107.61
13    2167.29    31.49    -107.61
14    2091.17    30.38    -107.61
15    2013.95    29.26    -107.61
16    1935.60    28.12    -107.61
17    1856.12    26.97    -107.61
18    1775.47    25.80    -107.61
19    1693.66    24.61    -107.61
20    1610.66    23.40    -107.61
21    1526.45    22.18    -107.61
22    1441.02    20.94    -107.61
23    1354.35    19.68    -107.61
24    1266.42    18.40    -107.61
25    1177.21    17.10    -107.61
26    1086.71    15.79    -107.61
27    994.89    14.46    -107.61
28    901.73    13.10    -107.61
29    807.22    11.73    -107.61
30    711.34    10.34    -107.61
31    614.07    8.92    -107.61
32    515.38    7.49    -107.61
33    415.26    6.03    -107.61
34    313.68    4.56    -107.61
35    210.63    3.06    -107.61
36    106.08    1.54    -107.61
37    0.01[/SIZE][/FONT]

Justustinin

Thanks Grumbler!

Can I just ask for a bit of clarification on some of things you said.

When you say:

Monthly rate is =(1+18.9%)^(1/12)
1.453%

Are you using ^ to mean "raised to the power of"?

Also in:

Monthly payment is =PMT(1.453%,36,3000)
£107.61

What does PMT stand for? a figure followed by direct brackets normally means that figure multiplied by the result of whats inside. Am I right?

Sorry to have to ask. I'm just trying to make sure I translate it into a spreadsheet correctly.

Thanks for doing the working out for me! It's good to have an idea of what I should be paying to clear it in 36 months!

grumbler

Both are Excel formulas. Just copy and paste them to any empty cell (with the leading '=').

PMT is a function (one of 'financial' functions). Figures in the brackets are its arguments.

Description
Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax
PMT(rate, nper, pv, [fv], [type]) Note For a more complete description of the arguments in PMT, see the PV function.
The PMT function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• Rate Required. The interest rate for the loan.
• Nper Required. The total number of payments for the loan.
• Pv Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
• Fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• Type Optional. The number 0 (zero) or 1 and indicates when payments are due.

And if you are "sitting down to build an excel sheet", start with learning Excel basic notation . Yes, ^ is "raised to the power of".
I edited the formula: =(1+18.9%)^(1/12)-1
I subtracted 1 in my head and forgot to correct the formula accordingly when copying it.

Justustinin

Thanks for clarifying, that's really useful and helpful!

Cheers!