Analyse - Loan and Rate Analyser© Brian Stewart, 2005 |
|
|
PurposeAnalyse is a BBC BASIC for Windows program which carries out various calculations for more straightforward credit agreements and parts of agreements. It provides a sort of 'what if?' calculator where you can fill in the known information and ask Analyse to calculate an unknown. Analyse is written to conform to the protocols for an add-in to the Office of Fair Trading's DualCalc program but can be run as a separate program if you download the stand-alone version. Analyse is the first 'serious' Windows based add-in for DualCalc (the previous add-ins either being adaptations of DOS-based programs or the small test-bed Windows based add-in 'Period'). Analyse duplicates or enhances the functions of 'Multical' (one of the DOS add-ins) and will also duplicate the functions of Period so, although they remain available, you may want to consider removing these add-ins when you download and install Analyse.
|
Annexes: 
|
|
Installation A program called 'AnalyseInstall.exe' will install the Analyse add-in in your copy of DualCalc. It is a self-extracting archive containing: this HTML help file 'Analyse.htm', a number of image files, and the program itself 'Analyse.bbc'. If you don't have a copy you can download it here: Direct the installer to the folder where you keep DualCalc and everything should be put in the appropriate folders. You also need to tell DualCalc that the add-in is there so that it will list it among it's tools. This add-in comes with an .add file which, from version 1.01a of DualCalc, will automatically install it in DualCalc's tools the next time DualCalc is started. This is a one-shot process as the .add file is deleted after it has been read. If this does not work for any reason, you can still install an add-in manually. There should be a folder called 'Resources' in the folder where you keep DualCalc and you need to create or edit a file in there called 'Toolset.txt'. If there is no Toolset.txt file in there ... Start Windows Notepad (you should find this in All programs > Accessories on the Start-button Menu) or your preferred text editor and type (or cut and paste) the following three lines: DualCalc Tools (You can use your own text in place of 'Loan and Rate Analyser', but use something which describes the program appropriately.) Now save the file from Notepad or your text editor into the Resources folder as Toolset.txt - note: if you use a wordprocessor rather than a text editor, the file must be saved as plain DOS/ASCII text. If there is already a Toolset.txt file in there ... Right-click on it and choose 'Open' (or perhaps 'Edit') from the context menu. NotePad or your alternative text editor should open the file. Add the last two lines given above to the file (it should already start with the first line). The easiest thing is to just put them on the end of the existing lines and the program will be last in the list of add-ins, but you can put them higher up - make sure you don't break up the existing pairs of 'Tool=', 'File=' lines. Now save the file and Analyse should appear in the add-ins panel when you click on the 'Tools' button in DualCalc. The stand-alone version ... A stand-alone version of the program is also available: To install this version, direct the installer to save the files it contains somewhere convenient on your hard drive (eg C:\Programs Files) and it will make a folder in there called 'Analyse'. Then make a shortcut to the program file 'Analyse.exe' in the Analyse folder and put it wherever you like to keep shortcuts (eg on your Desktop). 
Running the Analyse Program To run the program from DualCalc, click on the 'Tools' button in DualCalc dialogue. If you have followed the installation instructions correctly, you should see 'Loan and Rate Analyser' (or your chosen alternative text) listed in the Add-ins (the third column in the tools dialogue). Click on the text to select it from the list and then click the 'Run Add-in' button underneath. There will be a brief delay while DualCalc saves the current calculation (if any) etc. and DualCalc will then close to be replaced by Analyse's dialogue:
To run the stand-alone version, simply use the shortcut you created when installing and the above dialogue should appear.
Using the Analyse Program Analyse will calculate a number of things associated with a series of regular repayments: the rate, the repayment amount, the number of repayments, the opening balance, the final balance, and so on. It acts as a sort of 'what if?' calculator where you fill in the things you know and the program then calculates the missing element you indicate.
Example Calculations
The column of buttons down the centre of the dialogue is used to tell Analyse what you want to calculate. When you click the button next to an item of information it is greyed out (to indicate that you can't enter a value into it) and all the other values are available. So, to calculate a loan amount or initial balance, click the radio button next to those entries - you will need to be in 'Enter' mode and start from scratch so, if you've been experimenting and the button at the bottom of the dialogue says 'Enter', click it first to change it to 'Calculate' and then click 'Reset' and answer 'Okay'.
Periods in a year ... The Periods in a year (also known as 'periods per annum' or 'PPA') for a calculation can be selected from a combo (drop-down) box which contains the three UK statutory alternatives: Weeks (52 periods in a year), Months (12 periods in a year) or Years&days (1 period in a year). You can also use this selector to enter other common values: Half-years (2 pa), 4 months (3 pa), Quarters (4 pa), 2 months (6 pa), 4 weeks (13 pa) and 2 Weeks (26 pa) which will still, technically, provide a statutory calculation (because these are simply multiples of weeks and months). For greater flexibility, the program also allows you to select 365 days pa and 3651/4 (356.25 pa) or enter other numerical values directly into the combo box, eg '90' or '360' but remember that these will not necessarily be statutory calculations. You cannot ask the program to calculate the periods in a year. If you think about it, a loan will repaid by the same set of repayments at a given period rate, however long the individual periods are. The PPA is only significant for converting between period and annual rates.
A rate calculation: Analyse initially assumes that you want to do a rate calculation and opens in this mode, so let's use this as our first example, then we'll work down the entries in the dialogue. Assume you have the 'standard' loan I normally use as an initial example for checking programs or calculations: an advance of £100, repaid by 12 equal monthly instalments of £10 (later on these notes refer repeatedly to 'the standard loan' and mean this one, as modified in the particular example). To carry out this calculation, working down the dialogue, you would:
Assuming all the inputs are okay (see 'Checking input ...' below), the program will then carry out the rate calculation and display the results for the period, nominal annual and effective annual rates and the statutory annual percentage rate (APR). The total charge for credit (TCC) and the total amount payable (TAP) are also updated for each calculation. When you enter rates for other types of calculation, you are given a choice of three methods of entry: a period, nominal annual or effective annual rate (see Annex 2 for an explanation of these rates). Use the further radio buttons on the left of the rate entry boxes to indicate which rate you want to enter and the other rates will be calculated from that one.
Loan / Initial balance ... This is the amount advanced or the balance outstanding at the start of a series of repayments. We already know the amount of our 'standard' loan is £100, so click the 'Enter' button and try altering the number of repayments, say to 16, and click the 'Calculate' button again. The program calculates that 16 repayments of £10 at the same rate will pay off a higher balance of £126.36 - note that the calculator also now displays the TAP, TCC and APR for this loan and repayments.
Initial payment / Deposit ... This is a sum paid by the borrower at the start of the loan or at the time the initial balance is outstanding. Now let's assume that the borrower has our 'standard' loan but only makes 10 repayments and has to pay something extra at the start of the loan to make up the difference:
The program calculates that to pay off the loan at the same rate and only 10 repayments of the same amount, the borrower would also have to make an initial payment of £14.36.
First repayment/adjustment ... This is an additional amount paid at the same time as the first regular repayment. Now let's assume that the borrower will pay the same 10 payments and, instead of paying something extra up-front, pays it at the time of the first regular repayment (so in effect, the first payment isn't 'regular' it's larger than the others):
This time the program calculates that to pay off the loan at the same rate and only 10 repayments of the same amount, the borrower would also have to pay an additional £14.78 at the time of the first repayment (remember this is in addition to the regular £ payment, so the total first repayment is £24.78).
Final repayment/balance ... This is an additional amount paid at the same time as the last regular repayment, or the balance which remains outstanding after it has been paid. Now let's assume that the borrower will pay the same 10 payments and, instead of paying something extra at the time of the first regular repayment, pays it at the time of the last regular repayment (so now it's the last payment which isn't 'regular'), or looked at another way, the borrower pays nothing extra so there is a balance which remains outstanding after the last regular repayment has been made:
This time the program calculates that to pay off the loan at the same rate and only 10 repayments of the same amount, the borrower would also have to pay an additional £19.16 at the time of the last repayment (so the last repayment is in effect £29.16), or if only £10 is paid then, £19.16 remains outstanding.
Regular repayment ... This is fixed repayment paid every period through the number of payments in question, before any adjustment by the first or last repayment. Now let's assume that the borrower will pay 10 payments and nothing extra. How much should be paid each month to clear the loan? To calculate this:
The program calculates that to pay off the standard loan in only 10 repayments the borrower would have to pay £11.68 a month.
Time to first repayment ... This is the length of the interval (in 'periods in a year') from the time the opening balance is outstanding to the time of the first regular repayment. The default value is one period, and should be entered as a whole number. Okay, now lets assume that the borrower is going to make 16 regular repayments but the loan, repayment amounts and rate stay as standard. This will obviously pay off more than the standard loan if the borrower starts paying after one month, but a delay in starting would allow interest to accumulate until the 16 payments would pay off the loan. So, when would the 16 repayments have to start so that they only pay off £100?
The result the program comes up with us 9.1197298788 periods. This means that 9 repayments won't quite clear the balance and 10 will more than clear it. In this calculation, if the agreement is zero rate (eg because all the repayments add up to the opening balance) then the time to the first repayment could be any length. The program cannot calculate the first interval and tries to identify this and warn you accordingly.
Number of repayments ... This is, obviously, the number of regular repayments from (and including) the first to the final, and should also be entered as a whole number. Let's assume for this final example that the borrower again has a standard loan but only want to pay £8 a month. How many payments should he made to pay off the loan?
The program calculates the number of repayments as 15.7827021669. This means that 15 repayments aren't quite enough to pay off the loan and 16 repayments would slightly overpay it. In this calculation, if the repayment amounts are only paying the interest accruing each month (eg because the opening balance after adjustment by the initial payment and the first payment is the same as the final balance) then the loan or series of repayments could go on forever just maintaining the same balance. The program cannot calculate the number of repayments and tries to identify this and warn you accordingly.
Precision and validity of the results Sometimes, as in the last two examples, the results the program gives are rather too precise for the purpose in question and not necessarily valid - ie you can't actually make 15.78... repayments. In such cases the onus rests with you to interpret the results as approprite to the calulation you are doing. Remember that you can always use the program to examine this type of answer further - for example, when it tells you that something requires, say, 12.3 repayments, you can always adjust that value to 12 (or 13) and ask the program to re-calculate the loan amount, final repayment, or whatever, for that number of repayments. As far as the statutory results are concerned (TCC, TAP and APR), please remember that their validity depends on you having done a calculation which conforms to the statutory requirements. The range of loans Analyse will deal with (a single advance repaid by basically regular, equal repayments, but where the first and last can be a different amount and the time to the first repayment can be different from that between repayments) is quite broad and hopefully covers many 'real-world' situations. But if the loan you are looking at is more complex than that (eg muliple advances at different times or several different repayment amounts over it's length) you cannot 'finesse' the calculation (eg by adding the advances together or averaging out the repayments) and expect to get a statutory result. By default, the program rounds it's monetary results to two decimal places and this may produce some small anomolies. If you want accurate rate, TAP and TCC results (eg for a stautory calculation under the UK Consumer Credit Act) you should always ensure that the financial entries contain two decimal places and calculate the rate from the repayments, rather than the other way around. The '[Finance]' button can be toggled to show '[Exact]' in which case monetary values won't be rounded. This can give you a clearer picture of what's going on. In relation to UK statutory calculations, also note that the '[Round]' button can be toggled to show '[Truncate]', in which case the APR will be truncted in accordance with pre-2000 statutory requirements. Despite their precision, computers are also never entirely accurate with this type of calculation. For example, if you carry out the rate calculation for our standard loan and then re-calculate the 'Initial payment / Deposit' for the same loan, you will see that the program comes up with a value of '-0.00'. This is because, despite all the decimal places shown, the correct rate still isn't exactly that given by the program. However, if you toggle to 'Exact' mode and re-do the 'Initial payment / Deposit' calculation you will now find that the result is '-2.02504679692E-11' and the 'E-11' on the end is computer-speak for 'move the decimal point 11 digits to the left', so the result is really '-0.0000000000202504679692' - which is actually rather small. In practice these types of minor discrepancies are unlikely to be very important.
Checking input Hopefully, all of the above calculations will have gone okay with no errors in what you entered. In real use, you may sometimes put the wrong thing into the calculator. When you hit the 'Calculate' button, the program looks at the entries in the dialogue to make sure they make sense. Some nonsense is simply ignored - eg if you enter the loan amount as '100wibble' or even '1wib0ble0' the numbers will be picked out and it will just be taken as '100'. If it finds information which it does not think is valid, the program will flag this by including 'INVALID=>' at the start of the entry and display an alert box saying 'Invalid entries detected, please correct them and try again.' If you see this you should scan the dialogue for any entries marked 'INVALID=>'. The sort of things which the program will find invalid are:
When you see an 'INVALID=>' entry you need to correct it before the program will do a calculation. What you entered is retained to the right of the arrow so you can alter it. You don't actually need to remove 'INVALID=>' from most boxes because the program will do that for you, provided the rest of the revised entry is valid. However, in the PPA 'combo' (drop-down) box, you will find that Windows won't scroll the text across and you need to delete 'INVALID=>' to get at what you've entered (or you can simply double-click on the text and key 'Delete' or Ctrl-X to get rid of it all). On the other hand, if what you enter appears to be a calculation (eg the inital payment is made up of several sums, so you've entered it as '50+12.50+2.75'), the program will carry it out for you and use the result (65.25). You can also use more complex calculations in the sort of format you would use in a BASIC program or spreadsheet, using the following symbols:
- subtract * multiply / divide ^ raise to the power (eg 12^2 is 12 squared or 144) E times 10 to the power (eg 1.25E6 is 1,250,000) Also, round brackets can be used to control the order of calculation, for example: 100*((1+21.4/100)^(1/12)1) Could be used to convert an effective annual rate of 21.4 to a monthly period effective rate - but why bother when the program will do it for you anyway (see 'Tools' below). Finally, the program checks that the calculation or loan makes sense - things like there being no loan or repayments - and warns you accordingly.
Additional Tools The program provides two additional tools to help with analysis: Interval calculator ... It can be handy to have some way to convert two dates to the interval between them in periods, the interval calculator does this.
The periods used are those you have chosen in the main dialogue, so set these to the value you want first. Then simply click on the 'Intervals' button, enter the two dates in the first two text boxes of the above dialogue and click the buttons at the bottom to calculate the interval in periods, which will appear in the third text box. The interval calculator works by calculating the time in years and part-years from the start date to the end date, and then multiplying by the periods in a year to convert the interval to periods. As this will often give a result which is not a whole number, alternative buttons are provided to truncate the interval to whole periods or round it to the nearest whole number of periods as required. Use cut and paste to transfer the result from the tool to Analyse's main dialogue or another program. Set Rates ... Analyse does rate conversions as part of it's calculation process and therefore contains all the same code as the rate converter add-in program 'Period'. It seemed sensible therefore to provide a tool so that Analyse could carry out rate conversions too. The procedure is as follows:
The 'Set Rates' button acts rather like another 'Calculate' button. When you click it all the other rates (including the APR) will be set to match the rate and PPA you have entered, and the dialogue will display them in a 'results' mode as above, with only the rate results un-greyed. If you log/spool/print a rate result (see below), only the PPA and rate information will be included. To carry out further calculations, click on the 'Enter' button in the normal way.
Using the Results To copy an individual result to another program (eg DualCalc or a wordprocessor) the usual Windows copy/paste facilities are available. Simply double-click on the result to select it, and then either key 'Ctrl-C' (for copy) or right-click on it and select 'Copy' from the context menu. In the other program put the cursor where you want to paste the result and either key 'Ctrl-V' or right-click and select 'Paste' from the context menu. When a calculation's results are displayed, the program also enables buttons which will allow you to 'Log', 'Spool' or 'Print' the result. 'Log' by default generates a similar web page to printing (see below), but with a couple of differences: first, it doesn't automatically start to print and, second, a web log can also contain several calculations, in this format: Analyse - Loan and Rate Analyser© Brian Stewart, 2005, v1.01Calculation Log File
'Spool' is a simpler logged output which just adds the details of the calculation to a text file in this format:
A spooled log can contain several calculations and each result is contained in a single line of text terminated by a carriage return (although the above text may have wrapped onto two or more lines it is in fact a single unbroken line in the original text). Each item of information is labelled in the format: Item=value, and is (apart from the last on the line) comma and space delimited. You may find this type of output useful for loading into other programs such as wordprocessors so you can modify the style, or for further processing in a text editor or by a bespoke program or script. Notes:
'Print' is probably the most obvious option but works in a slightly odd way (which you may already be familiar with if you use DualCalc) because, rather than send information direct to your printer, it uses your web browser. Essentially, because it's much easier to format and modify the output this way, this option generates and opens a web page which contains a 'print' command to tell your browser to start printing. Unfortunately, this means you will probably have to click on 'Okay' or 'Print' in the browser's print dialogue too. The format of a printout is similar to a log but contains only a single calculation. Printouts are therefore quite wasteful of paper and, if you are doing several calculations, you may prefer to log them and then view the log and print it from your browser.
Closing the Program To close the program either key 'Esc' (escape) or click on the close (X) icon top right of the program's dialogue. If installed and run as an add-in to DualCalc as described above, DualCalc should then restart and re-load the information it contained before you ran Analyse.
Version Log (most recent first): Version 1.01 - the first release The 'Set rates' facility has been added so the program can convert between period and effective rates. Several minor improvements and corrections which were unearthed while writing these notes. Version 1.00: The original version - not released.
Annex 1: The formulae I thought it might be useful to provide some information about the formulae used in the program and their derivation. This is a cut-down derivation which just gives the resulting formuale without all the intermediate steps. Unfortunately the formulae are not presented in the same order as the program's input boxes. 1. Introduction 1.1. These notes should be read in conjunction with the paper `A MICROCOMPUTER / CALCULATOR BASED SOLUTION TO THE CALCULATION OF APR', as the formulae here are derived from some of those in that paper. The paper 'REPAYMENT FORMULAE' which derives slightly simpler versions of the formulae may also be helpful in filling in some of the intermediate steps in the derivations. 1.2. The purpose of the formulae in these notes is to provide a number of tools which may be helpful in analysing more straightforward credit agreements or trying to examine their effect. They are based on the same actuarial principles used to calculate APR - ie the assumption that all agreements operate like period rate transactions with interest accruing and being added to the sum outstanding at the end of each repayment period. 2. Use of Symbols 2.1. The following symbols have the meanings shown below:
3. Existing Formulae 3.1. These formulae are take from the earlier paper, the first equation calculates the sum of the present values (PVs - see the note "What is APR?" if you are not sure what this means) of a series of regular repayments (equation (6) in the earlier paper), b and e are the times of the beginning and end of the level, the first repayment being made one time period after the beginning ie at b+1:
... we can introduce the terms s and n into this fairly easily as the time of the last repayment e is simply s+n-1, and as b+1 is the time of the first repayment, it is the same as s:
3.2. The formula for the PV of a single repayment (formula (1) in the earlier paper, where t is the time of the payment, in repayment periods):
... so to find the present values of our f and P terms we can just use:
... and:
3.3. The equation to calculate the APR from x and the repayment frequency (formula (2) in the earlier paper):
3.4. The formula to calculate the APR from the period rate and the repayment frequency (from regulation 7 of the Consumer Credit (Total Charge for Credit) Regulations 1980 with the terms changed to conform to 2.1. above):
4. Converting the Period Rate to 'x' 4.1. Equations (4) and (5) above are quite similar and you can see that:
... so:
4.2. This equation can be used to calculate x from the period rate before applying the equations described below. 5. The General Principle 5.1. All the formulae here are based on the idea that, applying actuarial principles, paying off the amount outstanding on a loan at any point results in the same APR as allowing it to continue for its full course. So, you can use the Present Value Rule to write the following formula for any part of a loan agreement:
5.2. What this is saying is that the amount outstanding at the start of a loan is equal to the sum of the PVs of the initial payment (which is just its actual amount), the first sum (using formula (2)), all the regular repayments made from time s up to time s+n -1(using formula (1)), plus the PV of the amount still outstanding at point s+n (using formula (3)). And this doesn't just apply at the start of a loan or to a complete loan - the amount outstanding at any point in a loan can be equated in this way to the PVs of the payments made over the following period and the amount outstanding at its end. So, depending on what we want to find, all we have to do is rearrange (7) to make our unknown the subject of the equation. 6. How Much was the Loan? 6.1. As it stands (7) will tell you how much was outstanding at the beginning of a period if you know the period rate, the amount and number of all the repayments and the amount outstanding at the end. 7. How Much are the Repayments? 7.1. To find this we need to make A the subject of the equation, which can be done just by rearranging (7):
8. How Much is Outstanding at the End? 8.1. This time (7) needs to be rearranged to make P the subject:
9. How Many Repayments are There? 9.1. In this case more than simple rearrangement is needed to make n the subject of our formula. Starting again with (7) we can re-arrange it to get x to the power of n on the left-hand side:
... now we can use logarithms to isolate n:
10. What was the Initial Payment? 10.1. Again a simple re-arrangement, this time to make D the subject:
11. What was the First Sum? 11.1. Also a simple re-arrangement, this time to make f the subject:
12. What was the First Time? 12.1. As with n we first have to make x to the power of s the subject and then use logarithms:
Annex 2: Effective rates Just to provide a bit of background to the 'Set Rates' facility, an 'effective' annual rate is the one you get if you compound a period rate for each period throughout a year. Sometimes people think in 'nominal' rates where the period rate is just multipiled by the number of periods in a year to get an annual rate, so that they'll assume, for example, that 2% a month is 24% a year, but the effective or compound annual rate, which assumes interest charged on interest, is actually 26.8241795% pa. Take as a simple example a loan where £100 is borrowed and interest is charged at 1% a month for 12 months, interest will compound as follows:
As you can see the balance at the end of 12 months is £112.68 and the interest is therefore £12.68, the effective rate is 12.68% and the (rounded) APR is 12.7. People sometimes look at this and say 'Hang on, what if I pay the interest every month? Then the balance stays at £100, there is no interest charged on interest and I only pay £12 interest in total, so in that case, the rate must be 12%, right?' Well, no. It's true that the interest is only 12% of the capital, but an interest rate should reflect time as well as how much you pay. If you pay the interest every month, you may have only paid £12 in interest, but you have also had to pay most of it sooner than the end of the year to avoid further interest being charged on it - so the effective annual rate at which you're paying interest is still the compound rate. You've just traded time for money ... and the 'exchange rate' is the effective rate of the agreement. The APR would only be 12.0 if the agreement required you to pay the £12 interest in one amount at the end of the year. The formulae used in the conversions are quite straightforward: If i is the period rate, r is the annual rate and m is the number of periods in a year, then:
... and ...
|
