How to calculate interest on balance transfer account - The Size of saving

griffster

I've recently got a barclaycard 6.5 % platinum card that's shown on the http://www.moneysavingexpert.com/cards/balance-transfer-credit-cards#cheapest page, however I wanted to try and build a spreadsheet so I could try and calculate how long it would take me to pay off the transferred balance. I won't be spending on the card just paying off the balance so the 0% deal on it won't have an effect I think. Just the 6.5% apr on the transferred balance. As Martin has put on the "The size of saving" section on the above page an example of how the balance would look after 6 months on a transferred balance of £5000 I thought I'd feed the same info in to my spreadsheet to see if they match, and they don't. I may be calculating this all wrong but I calculated the monthly interest rate as being 0.5262% (having worked it out from this site: http://www.stoozing.com/mon2yr.htm) converting from the apr of 6.5%. I've added £100 on as the transfer admin fee so the starting balance becomes £5100, multiply this by 0.5262, add this to the balance and subtract the £150 he's put on as the monthly payment. After six months this ends up as £4,351, with a total interest of £151.23. This differs from Martin's stats of £4,260 with a total interest of £160. Quite a difference so I'm worried I cannot genuinely be confident of how much I'm going to have to pay. Can someone point me in the right direction as where I'm going wrong please? In the terms and conditions on the card they say interest is calculated on a daily basis. Could this be the reason? If so, how do I calculate this please?

anotherpaul_2

Hi Griffster,

I just calculated it and got the same answers as you which don't agree with Martin's, so I did some digging.

*** I'm leaving this for completeness, but it's incorrect or not relevant ***
I suspect we're both calculating the APR incorrectly. I've always thought it was the compound interest fomula (so monthly would be 12th root of 1.065, less 1 - power(1.065,1/12) in excel).

However, it appears that isn't correct. Looking at this site, which happens to do the same calculation for a mortgage over 30 years with a 2% setup fee, they get 0.5572% monthly.
http://wiki.answers.com/Q/What_is_the_formula_for_calculating_APR

Using this number I get Martin's numbers.

However, when I try to use the formula they suggest, I only get that number if I calcuate it based on 30 years.
=RATE(30*12,PMT(0.065/12,30*12,5100),5000)

I thought credit cards were supposed to use 12 months, but when I put that into the equations I get completely different numbers.

So, either I or Martin doesn't know how to calculate interest from APR. If I were a betting man, I'd put money on Martin being correct, though I'm alarmed that I can't calculate it correctly!
*** end of incorrect bit ***

I now believe the formula above (monthly interest is 12th root of 1.065, less 1 or =power(1.065,1/12) in excel) is correct.

griffster

Sounds a bit more complicated than I thought! Weren't aware of those financial functions in excel before although don't understand much of the terminology used in the excel help screens so not sure which ones are of interest (pardon the pun!).

Tried out your formula there and see that it does match Martin's results, but I don't see why he's based it on paying off the balance in 30 years. Would want to pay it off far sooner than that to be honest! lol Using that formula I can also see that it clears the balance in the same number of months when I run it through month by month on the spreadsheet.

Is there any way we can get at how Martin's worked his calculations out or whether there's an industry standard to calculating this which matches the 30 year loan duration? As we're not able to contact Martin direct, are we able to log something with Moneysavingexpert to justify their figures at all?

anotherpaul_2

Actually, I think the APR bit is a red-herring. The 6.5% quoted isn't an APR figure so the APR equations aren't relevant.

I was going to wait an see if someone said "your numbers are wrong because...". If no one does tomorrow, I'll probably query it with the site.

griffster

Ok, thanks. Were you saying I ought to contact the site today, or were you going to as I don't know how to? If the latter, can you let me know how to do this please?

Clariman

griffster wrote: »

I've recently got a barclaycard 6.5 % platinum card that's shown on the http://www.moneysavingexpert.com/cards/balance-transfer-credit-cards#cheapest page, however I wanted to try and build a spreadsheet so I could try and calculate how long it would take me to pay off the transferred balance. .... I may be calculating this all wrong but I calculated the monthly interest rate as being 0.5262% (having worked it out from this site: http://www.stoozing.com/mon2yr.htm) converting from the apr of 6.5%. ....?

The stoozing website (which I am co-author/owner of ... site sponsored by MSE) has a sister website https://www.makesenseofcards.com . The monthly to APR interest calculator is there too, but we also have a Balance Transfer Calculator which does exactly what you want i.e. works out how much you save over a given period by transferring your balance to a new card.

We also have a Repayment Calculator which works out how long it would take to pay off a card given (a) only paying minimum payments or (b) a fixed monthly amount and comparing the two.

We also have other useful calculators there and are working on a Snowball calculator.

Clariman

anotherpaul_2

Hi Clariman,

Thanks for the links. I've tried with your calculator and it gives the same answers as my calculations (which is a relief!). I think mine are the same as the OP's calculations.

I think the issue was with a table on the MSE site that had errors in (I suspect it had had lots of partial updates and the information had become inconsistent). I queried it with them and they have replaced the table with some alternative calculations (whether as a result of my question or otherwise).

(In fact, I was looking in the wrong place and they haven't changed the table yet.)

Cheers,
Paul

griffster

Thanks for the links. Just tried the debt repayment calculator 2 with card interest rate of 6.5%, ard balance of £5000, minimum repayment of 2% or no less than £5 but will pay more than minimum paying £150 a month as Martin's example shows. Martin's link gives £160 interest and the repayment calculator says £512.12. Am I doing this wrong please?

anotherpaul_2

griffster wrote: »

Thanks for the links. Just tried the debt repayment calculator 2 with card interest rate of 6.5%, ard balance of £5000, minimum repayment of 2% or no less than £5 but will pay more than minimum paying £150 a month as Martin's example shows. Martin's link gives £160 interest and the repayment calculator says £512.12. Am I doing this wrong please?

Martin's link give £160 interest in the first 6 month and £520 over the 37 months it takes to pay it off. The calculator give you the number over the life of the 37 months and I agree with the £512.12 that it gives.

If you ignore the £100 transfer fee you should get £148.02 interest over the first 6 months (your monthly interest percentage is from your initial post is correct, so I suspect you'll get this if you remove the £100). Or, with the fee £151.22.

I'm now pretty sure that the MSE number are out of date. (I thought they'd been changed, but I must have been looking at the wrong place.)

Robert_Sterling_3

Errors.

First you need to know what interest rate is being charged and not what the APR is.
It may be the case that the interest rate being charged is the same as the APR in this particular case.

I will continue on the assumption that the APR and the interest rate being charged are both 6.5% per annum.

The notion of a monthly rate does not make much sense as months can have 28, 29, 30 or 31 days.
I regard the monthly rate as being a fictitious number based on the assumption that all months have the same number of days.

To get from the Annual Rate to the fictitious Monthly Rate we need to write the annual multiplier which in this case is 1.065 and then find its twelveth root

i.e. Fictious Monthly Rate = +1.065^(+1/+12)

This gives a Fictitious Monthly Multiplier of 1.005064835

The in turn gives a Fictitious Monthly Rate = 0.5064835%

The fact is that what really happens is that lenders use a Daily Rate.

If the annual multiplier is 1.065

Then the Daily Multiplier is +1.065^(+1/+365)

= 1.000166109

The Daily Interest Rate is = 0.01666109%

:easter:

anotherpaul_2

Robert_Sterling wrote: »

Errors.

First you need to know what interest rate is being charged and not what the APR is.
It may be the case that the interest rate being charged is the same as the APR in this particular case.

I will continue on the assumption that the APR and the interest rate being charged are both 6.5% per annum.

It's 6.5% p.a. (as I said in the fourth post).

Robert_Sterling wrote: »

The notion of a monthly rate does not make much sense as months can have 28, 29, 30 or 31 days.
I regard the monthly rate as being a fictitious number based on the assumption that all months have the same number of days.

Yes, but unless you know which months you're talking about this isn't helpful. And, you don't know exactly which day the payment goes in. It's an assumption which enables the calculation to be performed. Otherwise, I can give you as many different (but very similar) answer as you like based on varying the assumptions.

Robert_Sterling wrote: »

To get from the Annual Rate to the fictitious Monthly Rate we need to write the annual multiplier which in this case is 1.065 and then find its twelveth root

i.e. Fictious Monthly Rate = +1.065^(+1/+12)

This gives a Fictitious Monthly Multiplier of 1.005064835

The in turn gives a Fictitious Monthly Rate = 0.5064835%

I agree with the formula, but I don't agree with your calcuations. Did you use 1.0625 by mistake?

1.065^(1 / 12) = 1.00526169
http://www.google.co.uk/search?q=1.065^(1/12)

Robert_Sterling wrote: »

The fact is that what really happens is that lenders use a Daily Rate.

If the annual multiplier is 1.065

Then the Daily Multiplier is +1.065^(+1/+365)

= 1.000166109

The Daily Interest Rate is = 0.01666109%

Ditto,
1.065^(1 / 365) = 1.00017255
http://www.google.co.uk/search?q=1.065^(1/365)