Excel Financial Functions Beware!

  Cesar 10:04 20 Jul 2003

There is an inbuilt flaw in the Interest Part of Excel Financial functions,The American way is to divide the interest by 12 the British way is to take the twelth root, as an example
American Way
A1 = Loan = £20,000.00
A2 = Int = 9.7%
A3 = Term = 120 (Months)
A4 = Pmt = £260.88

British Way
A1 = Loan = £20,000.00
A2 = Int = 9.7%
A3 = Term = 120 (Months)
A4 = Pmt = £256.44

A leading Finance House Offers a £20,000.00 Loan over 120 Months with a Monthly Payment of £256.48 and an APR of 9.7%, you will notice that the British Method is fourpence out, this is because the Office of Fair Trading allows rounding up or down by 1%, to get precise figures you will have to use Solver.

If anyone is interested in the British Method of Interest Calculation I will post it.

  VoG II 10:09 20 Jul 2003

Thanks for that.

As with all things in Excel, you have to know what you are doing (as you obviously do). As an example there are all sorts of statistical functions in Excel's Analysis Toolpak which in "the wrong hands" can be used to generate absolute nonsense.

  daba 10:19 20 Jul 2003

I thought ALL statistics were 'absolute nonsense' when I did it in maths at school and at poly.
Two examples :

1. "Real Cause of Road Accidents", is Underwear ! .... because 99.7% of people involved in road accidents are wearing underwear at the time of the accident.

2. "In 30% of traffic accidents, the driver had been drinking" .... surely therefore we should ban the ones who hadn't !!

I was once told you could prove anything you like with statistics, one reason perhaps why the government keeps changing the way it calculates things like unemployment figures etc.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?