How APR separates the cheap loans from the expensive ones
Most of us borrow money at some time in our lives and Annual Percentage Rate (APR) is a standard measure for comparing the costs of loans. Critics say that APR is confusing and due for an overhaul and it's true that APR is not perfect. Be that as it may, APR is a universally quoted figure. If you need to borrow money, you need to understand APR – where the values come from and their implications. A spreadsheet is the perfect exploration tool.
Warning: this article is written with reference to UK law which probably differs from your local legislation. However, the basic principle of APR holds true anywhere.
The formula for calculating APR values quoted in advertisements is specified in an Act of Parliament. This is a simplified version:
P = Present Amount (the sum borrowed)
A = Amount of a single repayment
R = Annual Percentage Rate
T = Elapsed time since the start of the loan at which this repayment is made.
Or should that be T R A P? Anyway, don't run off! The formula is not half as scary as it looks and even if you don't fully get your head round it, there are still things to be learned about APR. If you prefer to skip the formula, just move on to the next heading.
The sigma (Σ) indicates that you must add up the results of multiple copies of the expression that follows. What you do is take a repayment schedule and add a copy of the formula for each instalment. An example makes it easy to understand. Suppose you borrow £100 (or Rupees, Ringgits, Euros, Dollars...) repaid in four instalments of £26 each at monthly intervals. Clearly there is some interest built in because the total repayment comes to £104. The APR formula is:
Note how the (1+R/100) divisors are raised to the power T, where T is the elapsed time in years since the start of the loan. Dividing the T values by 12 converts months to years. UK legislation defines how the calculation may use weeks or days instead of months.
The only unknown is the APR, shown here as R. To work out the APR, we have to discover the value that makes the right-hand side of the equation add up to 100. The spreadsheet described later in this article shows how Excel will do that for you.
Which loan is cheapest?
The purpose of APR is to provide a value comparison between loans, taking into account charges such as set-up and survey fees and compulsory payment protection insurance (PPI). Exactly what must be included in the calculation is set down in the legislation.
Two loans with an annual interest rate of 7 per cent but differing set-up or other fees would cost different amounts to repay. The Annual Percentage Rates tells you which loan is better value.
Suppose you borrow £120, repayable in instalments of £40 at monthly intervals. The lender charges an up-front fee of £5 for his services. Note that the repayments add up to £120 so the annual interest rate is zero. Yes, an interest-free loan! There is a non-zero APR, though, due to the fee which makes the total repayment £125. That small up-front fee, when applied to a three-month loan, gives an APR of a whopping 29.2 per cent.
And yet, making the same total £125 repayment over a longer period reduces the APR. Even paying it over three months but handing over the £5 at the end of the loan rather than at the start will give a better APR and therefore a better value loan.
Confusing or what? Well don't back off now, because we have arrived at a crucial feature of APR that keeps the loan sharks at bay. Without APR, a loan company can easily manipulate figures to your disadvantage and make a deal look better than it really is.
A shield against sharks
The APR formula is based on the method for calculating compound interest. A consequence of this is that APR is sensitive not only to the amounts paid but also to their timing.
Consider two three-month loans of £120 whose total repayment is £125. One is advertised as interest-free but has a front-loaded £5 administration fee. The other loan has no fee but charges a rate of interest that pushes the total to £125 by the end of the loan.
The second one is genuinely better value in the sense that it costs you less. Its lower APR rate will register that fact. But how can this be if, for the same sum borrowed, loan A has the same total repayment cost as loan B. Isn't the total cost of servicing the loan the one and only bottom line?
No it is not!
Because of the inbuilt compound interest element, APR is able to show that you will enjoy less benefit from the borrowed capital because you paid back more of it sooner.
Imagine what would happen if you put the borrowed money into a savings account and how much interest it would earn over the life of the loan. The longer you hang on to the capital the more interest it earns. By paying back the bulk of it sooner, you earn less interest while the lender is able to lend out the money again sooner, thereby making a greater profit overall.
In the case of the no-interest loan, you immediately lose the use of £5. The with-interest loan takes the £5 off you further down the line, so you can invest it in the meantime. While you have paid out £125 in both cases, the second deal allows you to offset the charges slightly by investing as much of the £5 as you have left.
In this example the difference in true cost is tiny. Apply the same logic to a car or house loan spanning years and the sums are magnified into significant amounts of money.
APR cuts through such manipulations likely to be loaded against you. Even so, despite the legal framework and the intent behind it, some lenders try to find loopholes and construct misleading adverts that make their deals look better than they really are. APR is an aid to, not a substitute for, proper assessment of what you are signing up to.
Putting Excel to work
You should now be convinced that APR values translate into meaningful amounts of money that you can be suckered out of by a lender who juggles the figures. If you take away this core message then this article will have done its job.
You can calculate and explore APR with Excel. Download my example sheet here.
Let's look at a real-life example taken from the NFU (National Farmer's Union) Mutual Finance site: "For a personal loan of £6,400 repayable by direct debit over four years, your repayments would be one of £64 (administration fee) made two months after your loan cheque is issued, followed by 48 monthly repayments of £170. The total amount payable would be £8,224".
The APR is stated to be 12.6 per cent. We should be able to arrive at that figure by writing down the repayment schedule, applying the formula and solving it for the APR value. It looks pretty hairy:
The ellipsis (...) indicates that months 5 to 49 are there but not shown.
Fortunately, Excel will sort this out in a jiffy. Click the NFU example tab of the spreadsheet. Columns B and C show the advertised repayment schedule. Cell B1 holds the APR. Since we don't know it yet, we shove in a dummy value to keep dependant formulas happy. These are in B2 and columns D and E, and work out components of the equation. Rows 6 to 56 each correspond to one term on the right-hand side of the equation.
Down in E57 we have the sum of all these terms. With an APR of zero, the Present Value is calculated as £8,224. This is not the result we are looking for. The Present Value is, by definition, the amount borrowed. Therefore we must discover a value for B1 that produces 6,400 in E57.
The magic moment...
Click on cell E57 and go to Tools, Goal Seek. A dialog will appear containing three fields. This tool will find the value of a cell or range that produces the desired result in another cell or range. The cell in which the result is placed, E57, is already input into the first field. Enter 6400 into the 'To value' field. This is the result we are looking for. In 'By changing cell' enter B1. This is where the APR value is stored, and we want Excel to find what this has to be in order to produce 6400 in E57.
Click OK and Excel should tell you it has found a solution. Click OK again and go to B1. As if by magic, we see 12.6 plus insignificant decimal places. UK legislation states that the APR should be rounded to one decimal place, so the figure quoted in the NFU example is spot on, as you would expect.
When you use the Goal Seek tool, bear in mind that you can't make it find a solution where none exists, that there may be more than one solution, and the algorithm used by goal seeking can be tripped up by certain data patterns. This should not happen with APR.
Some loans have more complex repayment schemes, for instance if you have to include the cost of compulsory insurance. But whatever the deal, if you can write down a schedule of payments you can build a model and get Excel to find the APR. When future interest rates are not known, the APR will be billed as variable. The quoted figure will be an illustration based on current or known interest rates.
An instalment-by-instalment model used in conjunction with goal seeking is not the only way to calculate APR. It should always work, except in rare circumstances that make the goal seeking algorithm fall over. It is probably the most efficient approach for more complicated deals.
For simpler repayment schedules, algebraic rearrangement of the formula may leave you with a much simpler calculation. Unless you are a skilled mathematician, by the time you have done that, you could have built the model and found the answer.
The one alternative I shall mention is Excel's Internal Rate of Return function, IRR. This is explained in a booklet published by Britain's Office of Fair Trading (OFT). It is essential reading if you want to dig deeper into APR. Download a copy here. The leaflet also gives an algorithm so you can write APR program code in the language of your choice.
The NFU example in APR.xls has an IRR version of the APR calculation, starting in cell C60.
Elsewhere in the Excel file you will find:
Simple example shows the APR calculation for a loan of £100 repayable in four monthly installments of £26.
£100 over 3 months shows the 'no-interest' + £5 fee deal mentioned earlier.
Prudential example is another real-life example taken from company advertising. The commentary highlights an important point about insurance and comparing like with like.
Barclay's example is the final real example and includes compulsory payment protection insurance.
