Hi David!
Apart from the definition problem in the UK, the other one when I was
there was that Banks and Building Societies were exempt (or maybe it
was just larger loans). With many Building Societies the effective
interest was significantly higher than the declared rate because of
their calculation method:
Calculate loan repayment at the declared rate on an annual in arrears
basis
Divide by 12
So with a declared rate of say 6% on a loan of 100000 over 10 years:
=(1+RATE(120,PMT(6%,10,100000,0,0)/12,100000,0,0,0))^12-1
Returns: 6.62942996860085%
Then they charged (say) 1% of the loan for establishment fees, so you
really get:
=(1+RATE(120,PMT(6%,10,100000,0,0)/12,99000,0,0,0))^12-1
Returns: 6.86777310742561%
And the legislation there, and elsewhere, doesn't catch the "interest
free " trick where they charge no interest but the cash price has been
inflated. An especial favorite of used car retailers where it's
difficult to get the equivalent cash price. They have to be careful
not to offer a discount for cash but one approach used to circumvent
this was to use a pair of companies which switched from deep
discounting to interest free credit every couple of months. "Interest
free" credit of over 40% on consumer goods such as carpets was not
unusual. The difficulty of those calculations is finding the price you
would pay elsewhere for a cash payment.
So for a new hifi with endless knobs and buttons I might pay 5000 with
interest free credit over 12 months but down the road (at a subsidiary
company) I might get it for 4000:
=(1+RATE(12,-5000/12,4000,0,0,0))^12-1
Returns: 53.0791748695815%
Not bad for "Interest Free"!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
David said:
Hello Norman
I've admired your work from a distance so it is good to speak with you directly.
I have no experience other than the UK where my background is
extensive use of Hewlett Packard's superb HP12c calculator with
Reverse Polish Notation and I struggle to translate this into Excel.
For the record, until 2000 the UK truncated the APR to one decimal
place. From 2000 an EU directive took effect which introduced the
present rounding method. Agree on politicians - the only calculation
they know is how to get around the next corner.
I started work on an underwriting database for my business in
December 2003 using ACT! and Excel. At that time I didn't know
anything in Excel other than adding up columns and doing simple
arithmetic on them. Reading sites like this has given me the tools to
be very near to completing my project.
