Introducing the LemonFools Personal Finance Calculators
Working out minimum payment

 Site Admin
 Posts: 742
 Joined: November 3rd, 2016, 11:03 pm
 Has thanked: 3 times
 Been thanked: 167 times
Working out minimum payment
I am getting frustrated with my credit card, can someone help me with my spreadhseet?
I keep a sheet that works out the next minimum payment but I just can't get the logic right. I have done a transfer offer of 4.9% to hold some money I am moving around.
so  I have a balance of £2952.12 in A1
the minimum payment is 1% (B1) + interest (C1). The interest is 4.9%
I have tried =SUM(A1*B1*c1) but thats way off at £1.45  I have tried numerous options but can't get close to what I am being charged last month, which is
£42.42 last month and the proposed this month is £41.22
can anyone help?
I keep a sheet that works out the next minimum payment but I just can't get the logic right. I have done a transfer offer of 4.9% to hold some money I am moving around.
so  I have a balance of £2952.12 in A1
the minimum payment is 1% (B1) + interest (C1). The interest is 4.9%
I have tried =SUM(A1*B1*c1) but thats way off at £1.45  I have tried numerous options but can't get close to what I am being charged last month, which is
£42.42 last month and the proposed this month is £41.22
can anyone help?

 Lemon Slice
 Posts: 749
 Joined: November 4th, 2016, 4:20 pm
 Has thanked: 390 times
 Been thanked: 138 times
Re: Working out minimum payment
Surely the rate of interest doesn't come into the equation when calculating the minimum payment?
I would think it should simply be balance x percentage required, i.e. A1 x B1
Having said that, given the repayments you quote, they are actually taking somewhere in the region of 1.4% so something doesn't add up.
Staffordian
I would think it should simply be balance x percentage required, i.e. A1 x B1
Having said that, given the repayments you quote, they are actually taking somewhere in the region of 1.4% so something doesn't add up.
Staffordian

 Lemon Quarter
 Posts: 2286
 Joined: November 5th, 2016, 9:05 am
 Has thanked: 4 times
 Been thanked: 456 times
Re: Working out minimum payment
stooz wrote:
can anyone help?
The declared rate of 4.9% is an annualised equivalent. You need to find out what the monthly rate is.
It isn't either .049/12 or (1.049)^(1/12) 1 as neither quite reproduce the quoted interest.
They may even be using a daily rate, but I couldn't quite make that work either.
A daily rate might be (1.049)^(1/365)1, again that doesn't quite fit.
Anyway, what you are doing wrong is treating the 4.9% as a monthly rate. Once you fix that, there are any number of possible formulae.

 Lemon Slice
 Posts: 749
 Joined: November 4th, 2016, 4:20 pm
 Has thanked: 390 times
 Been thanked: 138 times
Re: Working out minimum payment
Second thoughts...
Alaric is, of course correct that calculating the actual monthly interest is not necessarily straightforward. Having said that, it is possible to get close to the figure required, but your formula appears not to be correct.
The repayment required is (A1*C1)+(A1*B1) in other words, the sum of balance x rate and balance x minimum percentage.
You will probably need a different calculation for the A1*C1 to get close to how they actually calculate the interest.
Staffordian
Alaric is, of course correct that calculating the actual monthly interest is not necessarily straightforward. Having said that, it is possible to get close to the figure required, but your formula appears not to be correct.
The repayment required is (A1*C1)+(A1*B1) in other words, the sum of balance x rate and balance x minimum percentage.
You will probably need a different calculation for the A1*C1 to get close to how they actually calculate the interest.
Staffordian

 Lemon Quarter
 Posts: 2864
 Joined: November 4th, 2016, 11:35 am
 Has thanked: 562 times
 Been thanked: 585 times
Re: Working out minimum payment
The calculation method should be buried in the card T&Cs somewhere I assume ?

 Lemon Quarter
 Posts: 2286
 Joined: November 5th, 2016, 9:05 am
 Has thanked: 4 times
 Been thanked: 456 times
Re: Working out minimum payment
staffordian wrote:
The repayment required is (A1*C1)+(A1*B1) in other words, the sum of balance x rate and balance x minimum percentage.
You will probably need a different calculation for the A1*C1 to get close to how they actually calculate the interest.
You need to establish C1. What it isn't is the quoted annual rate. 4.9% a month would be Provident Financial subprime territory.
Try a bit of reverse engineering. 1% of the sum outstanding is 29.52. Subtract that from the amount they ask for (41.22) gives 11.70. Expressed as a % of the sum outstanding (2952.12) is 0.396% which annualises to 4.9% if you round to 1 decimal place in the quoted percentage.
You need an investment paying at least 5% to want to borrow at 4.9% unless it's just spending.

 Site Admin
 Posts: 742
 Joined: November 3rd, 2016, 11:03 pm
 Has thanked: 3 times
 Been thanked: 167 times
Re: Working out minimum payment
Ahh of course, thanks so much I hadn't taken into account that was the annual rate! Obvious when you say that.
Its just a short term borrowing to buy a new car while I wait for some money to come in to pay it straight off again. So I just put it on the credit card.
Its just a short term borrowing to buy a new car while I wait for some money to come in to pay it straight off again. So I just put it on the credit card.

 Site Admin
 Posts: 742
 Joined: November 3rd, 2016, 11:03 pm
 Has thanked: 3 times
 Been thanked: 167 times
Re: Working out minimum payment
AleisterCrowley wrote:The calculation method should be buried in the card T&Cs somewhere I assume ?
Its a bit ambiguous:
The minimum monthly payment on your credit card is 1% of the amount borrowed plus interest and any late, overlimit or annual fees in most cases.
Here’s how it works:
If you’ve got a balance of £25 or less, you’ll have to clear the balance on your statement.
If your balance is more than £25, your payment will be £25 or 1% of the balance on your statement, whichever is the greater. We’ll also add an amount equal to the default charges, card fees and interest for that month to the payment due.

 Lemon Slice
 Posts: 845
 Joined: November 4th, 2016, 10:25 am
 Has thanked: 67 times
 Been thanked: 98 times
Re: Working out minimum payment
I get pretty close doing this:
1% of £2952.12 = £29.52
Interest due on £2952.12 @ 4.9% p.a divided by 365 (daily amount) multiplied by 30 = £11.89
Total £41.41
Does that help at all?
The chances of getting the calculation exactly "right" are probably small as you have make assumptions about the bank's rules for leap years, days in the month, what day is the first day of interest etc. Within 50p is probably close enough for most purposes??
1% of £2952.12 = £29.52
Interest due on £2952.12 @ 4.9% p.a divided by 365 (daily amount) multiplied by 30 = £11.89
Total £41.41
Does that help at all?
The chances of getting the calculation exactly "right" are probably small as you have make assumptions about the bank's rules for leap years, days in the month, what day is the first day of interest etc. Within 50p is probably close enough for most purposes??

 Lemon Quarter
 Posts: 1854
 Joined: November 4th, 2016, 6:11 pm
 Has thanked: 101 times
 Been thanked: 447 times
Re: Working out minimum payment
stooz wrote:Its just a short term borrowing to buy a new car while I wait for some money to come in to pay it straight off again. So I just put it on the credit card.
Can't you get a 0% interest rate card, and juggle about balances so you don't pay any interest?
There was a name for that ruse, but I can't remember it now...
Scott.

 Lemon Quarter
 Posts: 2826
 Joined: November 4th, 2016, 11:18 am
 Has thanked: 1012 times
 Been thanked: 571 times
Re: Working out minimum payment
swill453 wrote:stooz wrote:Its just a short term borrowing to buy a new car while I wait for some money to come in to pay it straight off again. So I just put it on the credit card.
Can't you get a 0% interest rate card, and juggle about balances so you don't pay any interest?
There was a name for that ruse, but I can't remember it now...
Scott.
Can you get a 0% interest card....just to make the maths easier!?
Mel

 Site Admin
 Posts: 742
 Joined: November 3rd, 2016, 11:03 pm
 Has thanked: 3 times
 Been thanked: 167 times
Re: Working out minimum payment
The issue with most 0% cards is the BT fee. Fine if you use it for the length of the balance but this is going to be short term. Hopefully I spend less in interest than just paying a fee!
I converted the apr to an annual amount as suggested using my own stoozing.com Apr convertor and it was spot on this month.
I converted the apr to an annual amount as suggested using my own stoozing.com Apr convertor and it was spot on this month.
Re: Working out minimum payment
There are a few 0% fee 0% balance transfer cards. Halifax currently has one for 29 months. Bank of Scotland and Lloyds also have 25 months. Have a look at uswitch.com for 0% balance transfer cards, and sort them on the fee. You have to do the balance transfer in the first 90 days.
They won't transfer a balance from an American Express card (no idea why, but I have tried Lloyds and Halifax and neither can manage it).
They won't transfer a balance from an American Express card (no idea why, but I have tried Lloyds and Halifax and neither can manage it).

 Lemon Slice
 Posts: 822
 Joined: November 5th, 2016, 10:30 am
 Has thanked: 4 times
 Been thanked: 149 times
Re: Working out minimum payment
Loup321 wrote:There are a few 0% fee 0% balance transfer cards. Halifax currently has one for 29 months. Bank of Scotland and Lloyds also have 25 months. Have a look at uswitch.com for 0% balance transfer cards, and sort them on the fee. You have to do the balance transfer in the first 90 days.
They won't transfer a balance from an American Express card (no idea why, but I have tried Lloyds and Halifax and neither can manage it).
I have a 0%apr, 0% fee from both Tesco and Bank of Scotland. I struggled to transfer an Amex balance so stoozed via a MBNA card.
Return to “Credit Cards and Loans”
Who is online
Users browsing this forum: No registered users and 1 guest