UK APR Calculation

[horatio_too]

Hi

I am in the process of adding the option to pay by Direct Debit (DD) to my existing Access 2003 line-of-business system.

The business decision has been taken to make a small charge for paying using the DD facility (perhaps 3% ?) and I would appreciate some guidance as to how to calculate the associated APR.

Kind regards

Horatio_too

 

[ypma]

Not sure what you are trying to acheive so am assuming you wish to charge an extra 3% on top of the overall cost of the item/service and spread this extra 3% over each payment, which in this case would .25% per month for 12 Months . or do you wish to add a straight 3% on the value of each DD payment ?

Some example figures and the result you would except would be helful
Regards

 

[horatio_too]

Hi ypma

Many thanks for your reply.

Apologies for the delay in responding, but thanks to the weather I have spent no time in front of a PC for the last couple of days !

The best thing is to illustrate what we are planning to do with an example.

Say the 'cash' price is £1,200.

Without any extra charge, this would be £100/month over 12 months

We are planning to add an extra £36 (3% of £1,200) for the DD facility, thus making the total £1,236 or £103/month

I am led to believe that 3% applied in this way equates to approx 6.9% APR, but cannot find anything to support that !

Also, although we will usually take 12 equal payments over the course of the year, we may take fewer, depending on circumstances.

As such, I'm really looking for a proper understanding of UK APR (formulas, worked examples, pseudo code etc)

Hope that that clarifies things.

Kind regards

Horatio_too

 

[ypma]

I have produce a demo to reflect you figures . This is very basic but, should give you some idea how you could acheive your goal. For Example you could have a look up table if you wished to changed the rate you charge for using the DD .This demo is created using ac2010.

 

[horatio_too]

Hi ypma

Many thanks for your further support.

I'm currently on a PC with Access 2007, but should be able to jump on a box with Access 2010 later in the day.

I'll post back once I have had a chance to look at your solution.

Kind regards

Horatio_too

 

[horatio_too]

Hi ypma

I have now had a chance to have a look at your sample database.


Unfortunately, we appear to have been talking at cross-purposes.


The Form that you have put together is absolutely fine, but the bit that I am struggling with is not actually shown on your form !


We will need to include wording along the lines of :-


"We make a charge for payment by Direct Debit of 3% of the total price, which is equivalent to an APR of x.yz %"


It is the "how do you calculate the x.yz%" bit that I am struggling with !


To be clear, I have found the following (section in red) a couple of times during the extensive Googling that I have done and I suppose what I am looking for (assuming that it is the correct formula !) is a way to solve this within VBA.


Current UK APR

For any credit agreement, the annual percentage rate (APR) is the correct value of i in the mathematical equation given below, expressed as a percentage:

K=m
∑
K=1​

AK
____
(1+i) tk​

=​

K'=m'
∑
K'=1​

A'K'
____
(1+i) tk'​

MEANING OF LETTERS AND SYMBOLS:
K is the number identifying a particular advance of credit;
K’ is the number identifying a particular instalment;
AK is the amount of advance K;
A’K’ is the amount of instalment K’;
Σ represents the sum of all the terms indicated;
m is the number of advances of credit;
m’ is the total number of instalments;
tK is the interval, expressed in years, between the relevant date and the
date of advance K;
tK’ is the interval, expressed in years, between the relevant date and the
date of instalment K’;
i is the APR, expressed as a decimal.


Hope that that clarifies things ?


Kind regards


Horatio_too

 

[ypma]

Sorry I did not pick up on what you were asking.
Cost of Item 1200
int 3%
Term in Months 12
Closing Costs 36 (charge for using DD)
giving you a APR Of 8.7% .

Do you agree with the APR ?
If so I will try and solve via the programme

 

[horatio_too]

Hi ypma

No problems, the fault is probably at my end.

Sorry, but I am afraid that I cannot agree the APR.

We have historically used a third party for our Direct Debit management/collection and have "always been told" that 3% equates to 6.9% APR - but they have been unable to provide any basis for this.

On-line UK calaculators (US APR is different) all come up with different figures (!), which is why I am keen to get back to first principles (ideally supported by worked examples) before trying to build it in VBA.

I nearly found a good source of worked examples on the Department for Business, Innovation and Skills website, (pages 85 onwards of the link below), but they appear to be when you know the APR and want to calculate the amount outstanding:-

double u double u double u dot
bis.gov
.uk/
assets/BISCore/consumer-issues/docs/C/10-1053-consumer-credit-directive-guidance.pdf

(Recreate for yourself - my post count too low to allow hyperlinks !)

Finally, you stated closing costs of £36. Not sure that that figure is a 'closing cost' ?

Kind regards

Hortatio_too

 

[jdraw]

I don't know if this helps, but here's the link
http://en.wikipedia.org/wiki/Annual_percentage_rate

 

[ypma]

I have also found a site and it gives me a different APR of 3.6 , the site is as follows;-
[FONT=&quot]http://banking.about.com/library/calculators/bl_APR_calculator_load.htm[/FONT]


As you use the calulator you will see why i entered the closing charge of 36
[FONT=&quot][/FONT][FONT=&quot][/FONT]
as that is the extra 36 per year at 3% The initial error i made was to enter only 1200 for the loan but , i beleive it should have been the new total 0f 1236. Once we agree a figure we should be able to use the formula and PMT function to solve the problem ,hopfully.

 

[horatio_too]

Hi jdraw

Many thanks for taking the time to reply too.

I was aware of the Wikipedia page, but believe that the cited examples are 'probably' for the US version of APR. However, without references being quoted on that page, I am just presuming.

Equally, the "European Union' section is a restatement of the formula that I posted on here earlier. Also, I believe, there are subtle differences between the EU and UK's FSA/OFT definition of APR...

Unfortunately, in the heavily-regulated financial services world, I need absolute certainity and the more research I have done, the less certain I have become !

Thanks once again.

Kind regards

Horatio_too

 

[horatio_too]

Hi ypma

Thanks once again for your most recent link.

I have looked at literally dozens of different on-line calculators in doing my research and the only consistency seems to be that they all give different answers !

It's why I am so keen to get back to first principles - if I can get a worked example that I can understand, then, with 18 years of building Access apps, building the VBA should be the easy bit !

I post here because, naively, I thought somebody MUST have done this for the UK in VBA before and I did not want to 'reinvent the wheel' - looks like I was wrong !

Kind regards

Horatio_too

 

[ypma]

Horatio_too, I have little knowledge of APR but, have tried to come up with a working example that was on the net .
[FONT=&quot]APR Example[/FONT]
[FONT=&quot]APR seems really easy, but it’s amazing to watch the numbers (and your costs!) change with different scenarios. [/FONT]
[FONT=&quot]Assume you will borrow $100,000, and the lender tells you you’ve got a 7% interest rate. You also have $1,000 in closing costs. The APR on a 30 year fixed rate mortgage would be 7.10%. [/FONT]
[FONT=&quot]To test this, try the math yourself. In Microsoft Excel, follow these steps: [/FONT]
[FONT=&quot]Find the monthly payment for loan and closing costs: [/FONT]
[FONT=&quot]=PMT(0.07/12,360,100000) [/FONT]
[FONT=&quot]The format is: PMT(rate,nper,pv,fv,type) [/FONT]
· [FONT=&quot].07 divided by 12 is the rate (you’re using a monthly rate to find monthly payments) [/FONT]
· [FONT=&quot]360 is the number of periods (payments or months – 30 years here) [/FONT]
· [FONT=&quot]100,000 is the present value of your loan (including additional costs) [/FONT]
[FONT=&quot]You should have a result of $665.30. [/FONT]
[FONT=&quot]Next, Solve for the APR: [/FONT]
[FONT=&quot]=RATE(360,-665.30,99000) [/FONT]
[FONT=&quot]The format is: RATE(nper,pmt,pv,fv,type,guess) [/FONT]
· [FONT=&quot]360 is the number of periods you pay on the loan (360 months or 30 years) [/FONT]
· [FONT=&quot]- 665.30 is your payment [/FONT]
· [FONT=&quot]99,000 is the present value of your loan (how much you’re actually borrowing) [/FONT]
[FONT=&quot]You should have a result of .592%. This is a monthly rate. Multiply by 12 to get 7.0999%.

Hope this is of some use to you
Regards
[/FONT]

 

[horatio_too]

Hi ypma

Many thanks for your further reply. Apologies for the delay in getting back to you, but a combination of a family illness and numerous non-IT issues at work mean that I have not had a chance to look at this issue at all this week.

I will download your sample database over the weekend and get back to you once I have had a look.

Many thanks for your continued support.

Kind regards

Horatio_too

 

[gemma-the-husky]

generally, a flat rate of simple interest added over a year, would effectively double the true APR - depending on the frequency and timing off the repayments. eg, with monthly repayments, effectively the AVERAGE debt is half the capital, so 3% flat becomes 6% true. I therefore think the suggested 6.9% is likely to be correct.

however i suspect there are complicated rules to calculate the APR, and I would hesitate to guess what they might be, for fear of them not being correct. Do you HAVE to quote an APR for a credit card surcharge?

 

[horatio_too]

Hi Dave/ypma

Dave - Many thanks for your input - we had "always been told" that 3% equated to 6.9% APR, so your doubling roughly accords. Unfortunately, "roughly" is not good enough - in the UK it is a legal requirement to state the equivalent APR for any financial product or offering (in theory to allow the consumer to be able to compare products).

Things are complicated further by the fact that the APR calculation sits somewhere between the Office of Fair Trading (OFT) and the Financial Services Authority (FSA) - and the FSA is in the midst of being abolished...so getting a "definitive" answer from them is proving challenging !

ypma - I have now had a chance to try out your new demo, but unfortunately, I get an "Error 5, Invalid procedure call or argument" when I click in the 'APR results' box. I had a quick go at debugging why by plugging numbers straight into the Rate function (rather than values from your form), but still ended up with the same error ?

However, the good news is that the feedback from our existing Direct Debit provider also suggests that Rate is the way to go. We are in the proces of 'spot sampling' the Excel-based tool that they have now provided.

[For 'Tool' read a spreadsheet formula of

Rate(NoOfPayments,MonthlyAmount,LoanBeforeInterest) * 12

! ]

We are comparing this against what they have actually printed on their own DD paperwork over the last few years.

The only caveat is that our existing DD provider is not based in the UK, so they may not actually be complying with the OFT/FSA requirements...

Kind regards

Horatio_too

 

[gemma-the-husky]

in that case - the true calcluation will obviously depend on the frequency and timing or repayments.

eg repayments mid month will give a different rate to repayments at the end of the month, since the capital outstanding changes differently.

the complicated functions already displayed no doubt work. it's all similar things that work out NPVs of a series of payments, or annuities. maybe internal rate of return will work. See if excel has the right formula to use, then see if access has a similar one.

i just googled IRR, and there seems to be a fair bit of info available.

 

[horatio_too]

Hi Dave

Thanks for the further feedback. I spent several weeks Googling around the subject, getting more and more confused, before deciding to post on here.

Hopefully, my Accounts colleagues will finish their spot-sampling of the APR rates stated on old DD paperwork sometime this week. Once they are done, if the figures used accords with the answer from the Rate function, then that is what I will use.

It may, or may not, be strictly 'right', but if we can at least demonstrate "best endeavours", then hopefully the FSA/OFT would show us some leniency - particularly as we are 'up front' with our customers that we are charging an extra 3% "real money" for the DD facility.

Kind regards

Horatio_too