Murphy’s other law: “ If mathematically you end up with the incorrect answer, try multiplying by the page number”.

Other Formulae - still in the Technical Bits

(Please move directly to Part II if it gets too heavy!)

Cash flow sign conventions
It is helpful to set a convention when dealing with positive and negative answers to financial formulae.  One convention is to assume that all payments in are negative and all payments out as positive.  You can put it the other way round but you just have to remember what a negative or positive result actually means.

Future Value
In a repayment mortgage, it is often useful to know what the capital owing will be after a set time period.  You have already met the somewhat trivial formula for calculating Future Value (fv) from Present Value (pv) assuming no repayments.  This simple calculation is: -

fv = pv x (1 + i) n

Remember x means "multiply by". But there are usually regular payments involved and then the following general formula is more useful: -

fv = pv x (1 + i) n - pmt x (((1 + i) n) - 1) / i  --------------------- (5)

As before, this formula requires the pmt frequency to be the same as the compounding frequency so i is the fractional rate per period, n is the number of periods and pmt is the repayment per period: pmt is negative if investing.

As an example, let us take a mortgage of £50,000 over 25 years at the rate of 6% pa compounding monthly.  The monthly rate, i is then 0.06/12 = 0.005 (0.5% pm).  The period, n is 25 x 12 = 300 months.

We must first work out the repayment, if not already known, using the earlier formula (2): -

pmt = pv x  i / (1 - (1 + i) -n )

This gives 50,000 x (0.005 / (1 - (1 + 0.005) –300) = £322.15 per month.

Now suppose we wish to calculate the capital owing in five years time or 60 months.  Using formula (5) gives: -

50,000 x (1+.005) 60 - 322.15 x (((1 + .005) 60 ) - 1) /  0.005 = £44,966

That is obviously a long-winded calculation.  Clearly the use of spreadsheets is a great advantage here, as you need only enter a general-purpose formula once.  You then simply enter the variables to produce an instant answer.

If pv is zero, formula (5) simplifies to: -

fv = - pmt x (((1 + i) n) - 1) / i

Why is the result negative?  The answer is because if there is a zero principal, any further payments will start to overpay the debt – the debt is negative.  One would normally use this formula as an investment rather than a loan.  In this case one would start with nothing (pv = 0) so: -

fv = pmt x (((1 + i) n) - 1) / i

If one invests say £100 per month at 1% per month for 12 months the resultant capital is given by: -

100 x (((1 + .01) 12) - 1) / .01 =   £1,268.25

Repayment vs Endowment
One of the false arguments often put forward to support endowment mortgages (interest-only plus an endowment policy) is to consider what happens when you move house in a few years time.  The argument goes as follows.  An endowment mortgage theoretically always finishes on the same date, which is whenever the endowment policy matures, regardless how often you move because you use the same endowment policy for each new mortgage.  So if you borrow the same amount, the monthly cost remains the same despite the new term of each new mortgage being shorter.

This argument is quite correct so far.  But then it goes

“With a repayment mortgage, so little of the loan has been repaid in the early years that you have to borrow virtually the same amount again when you move.  So to keep the cost to the same level as before, the mortgage term then has to be the same as it was, so projecting the mortgage end-date further into the future: any shorter term would increase the monthly repayment.  But the wonderful endowment mortgage does not need extending the term to keep the cost the same!”

This argument is quite wrong, and we can see why when we apply some basic mathematics to the repayment mortgage.

Suppose you have a £50,000 bank mortgage over 25 years @ 6% pa compounding monthly, as in the last example.  We worked out that it requires a payment of £322.15 per month.

If you then move house after say 5 years, we have already calculated the amount you have to pay to redeem the mortgage in five years time as being £44,966.  So, supposing you wanted to borrow £44,966 again but over 20 years this time to end on the same date as before – as you would with an endowment mortgage.  What is the monthly repayment?

Applying formula (2) we get: -

44,966 x (0.005 / (1 - (1 + 0.005) –240) = £322.15 per month

This is precisely the same figure you were paying before.  When you think about it is has to be so:  when you redeem, you pay back with a lump sum but an instant later, you borrow precisely the same amount again, so the schedule should not change in any way.  There is no payment difference over the new shorter term and so no term increase is required to maintain unchanged monthly costs.  What is different is that you need to borrow less each time you move with the repayment mortgage.  But now you can prove it!

Term
Given the payments and the interest rate it is possible to work back to the term (n) of the loan (pv).  The following formula uses logarithms to any base:

n = - log(1 - pv x i / pmt) / log(1 + i) ---------------------------- (6)

So reversing the earlier bank mortgage calculation where pv is £50,000, i is .005 and the pmt is £322.15, this formula produces n = 300 months (25 years) as expected.

Present Value
Occasionally one needs to calculate the Principal (Present Value, or pv) given certain other variables.  Fortunately by rearranging the formula (5),  pv can be readily derived.  Take formula (5).

fv = pv x (1 + i) n - pmt x (((1 + i) n) - 1) / i)

By rearranging

pv x (1 + i) n = fv + pmt x (((1 + i) n) - 1) / i)

So      pv = (fv + pmt x (((1 + i) n) - 1) / i) / (1 + i) n

You can tinker with this formula to improve the look but the result is the same however it looks.  If fv is zero, pv is equal to the Principal for a repayment mortgage.  The formula is then better arranged to: -

pv = (pmt x (1 - (1 + i) -n)) / i

An application might be to calculate what size loan one can afford given the monthly payment and the interest rate.  Say you could manage £500 per month over 25 years and the rate of interest is 6% pa throughout, compounding monthly as before.  The Future Value (fv) is zero as the loan amortises to zero.  So the Principal is given by: -

(500 x (1 – (1 + 0.005) -300)) / 0.005 = £77,603

This means that £500 per month could support a £77,603 mortgage given the 6% interest rate.  In practice, interest rates are seldom fixed for long and it is necessary to make a guess as to the future average interest rate.  This task is almost impossible but there is a way round it using flexible payment mortgages, as discussed in Part II.

Increasing payments
This is a more complex formula, and is used for any annuity calculations where the monthly payment increases annually by a fixed amount:  it was also used for the original Flexible Payment Mortgage in the eighties.

Assume the initial loan/investment was pv and the final debt was fv, ( ie zero if it was a repayment mortgage or an annuity.  Nom is the nominal interest rate per annum, t is the true interest rate per annum and j is the percentage payment increase rate every 12 months.  Rates are not expressed as fractions in this formula.

Pmt = (A x pv – B x fv) x (Nom x (t – j) / (1200 x t))

Where A and B are factors calculated as follows: -

A = 1 / (1 – (1 + (t – j) / (100 + j)) –y )  where y is the term in years

B = 1 / ((1 + t / 100) y – (1 + j / 100) y)

An example would be an annuity type mortgage of say £100,000 amortising to zero over 25 years at 7% pa nominal, compounding monthly, and with payments escalating at 5% per annum.  The true rate, t , comes out to 7.2290% pa, j is 5, A is 2.44774 and B is 0.42752.  Note that B is not actually required if fv is zero.

The initial payment is then £440.27 per month, escalating by 5% pa.

This calculation produces the same result as an investment of £100,000 into a 25 year temporary annuity, with monthly payments of £440.27 per month, escalating by 5% per annum.

Flat rate loans and the ‘Rule of 78’
For a flat rate loan L at f % pa over M months, the total interest payable, T is given by: -

T = Loan x f% x M / 12 and the monthly payments, P = (T + L) / M

The interest element in month n of a loan of M months max is given by

I = T x 2 x (M + 1 - n) / M x(M + 1)

The remaining interest R still due is T minus all the I’s for each month to date or from the following: -

R = T x (M + 1 –n) x (M – n) / M x(M + 1)

The redemption (early settlement) capital owing is P x(M – n).

So for a £1,000 loan over 12 months at 14% flat, T = £140

P = 1,140 / 12 = £95 per month.

In month 6 (n = 6) the interest element is 140 x 2 x 7/ (12 x 13) = £12.56.

The capital element is 95 – 12.56 = £82.44

The redemption figure is 95 x 6 = £570 which includes outstanding interest.

The true monthly interest in this example is 2.0757%, calculated by any of the methods described herein.

All the above formulae are made much easier by using spreadsheet functions and for the sake of completion, the main Excel functions are summarised below but using the same classical variable definitions we used before.

Excel is the spreadsheet application created by Microsoft and is used by around 80% of all spreadsheet users.  It is often bundled in free with new computer applications or included in the “Works” suites.  Lotus sells their 123 spreadsheet, which does read Excel spreadsheets except some of the more exotic functions (which sadly often include the financial functions). The layout can also need adjusting in some cases.

Functions use a series of parameters in brackets, some of which can be omitted.  As with the formulae, the assumptions are that the interest rate is fixed, that the periods are of equal time and the payments are also constant and coincide with the rest period.

Payment (pmt) is given by PMT (i, n, pv, fv, type)

where type is either 1 for payments in advance or 0 (or omitted) for the more normal payments in arrears.  Alternatively you can remember the simple rule that you can convert from advance payments to arrears by simply multiplying or dividing the result by (1 + i).

The fv variable is also optional but is useful where the loan does not amortise to zero but to a positive balloon payment.  It is also useful for a mixed mortgage, which is part repayment and part endowment where the future value is the endowment element.

It is important to remember the sign convention for positive or negative cashflows.  Substituting positive figures in PMT will always produce a negative result to indicate that you make the payment (outwards movement) as opposed to the pv which is the sum you receive (inwards movement) and so it is positive.

Excel defines n as nper, which means both the number of payments and the number of periods.  This forces the user to accept that these formulae assume the payment frequency to be the same as the compounding frequency.  We can still think of n as the number of periods provided we remember that the result is a payment per period, which is not necessarily per month.

If we are calculating a Building Society repayment where the interest is compounded annually, i must be the annual rate, n is then in years and PMT calculates the annual payment.  The monthly payment is then obtained by dividing the annual payment by 12.  We have seen earlier that this produces a true interest rate anomaly where the true rate differs every year.

Future Value is given by:         FV(i, n, pmt, pv, type)

Present Value is given by:        PV(i, n, pmt, fv, type)

Term (n) is given by:               NPER(i, pmt, pv, fv, type)

Deriving the interest rate
As stated earlier there is no way to rearrange any of the earlier formulae to obtain interest rate.  You are of course welcome to try!  But there are several approaches of which the easiest is to use a spreadsheet with a RATE function or similar.

i is given in Excel by RATE(n, pmt, pv, fv, type)

This looks so simple, but there is a lot of iterative computation going on underneath the bonnet.  Fortunately we do not need to open the bonnet to use the function. Remember that this function assumes fixed interest, fixed payments made at the same time as interest compounds, and constant time intervals.

The old Hewlett Packard financial calculators produced excellent results and I still, to this day, use a 15 year old HP12C.  It has the five main financial keys i, n, pmt, pv and fv.  You enter any four known ones (with zero if relevant) and press the fifth key for the calculation of the unknown variable.  Calculating the interest takes some seconds, as the calculator goes through its iterations (automatic trial and error) to eventually hone in to as accurate an answer as possible.

The Internal Rate of Return – IRR
All the formulae & functions up to now assume that the interest rate is fixed, that the periods are of equal time and the payments are also constant and coincide with the rest period.

But the IRR is the theoretical fixed interest rate that can apply to any cash flow schedule however caused (by varying interest rates, payments or additional fees or bonuses) and produces the same end result.  The only stipulation is that the time intervals are constant although you may have a zero entry for any time period.

The IRR is the most valuable financial tool we have to compare and evaluate loans and mortgages with differing interest rates, and it is used in Part II and in the example spreadsheets.

The spreadsheet function is IRR(cash-flow range, guess) where the range is a list of cells like A1 to A12 (written as A1:A12) that contain the numbers for which you wish to calculate the IRR.  This could be the repayment schedule for a loan, including interest rate changes and fees.

The guess is optional (assumed to be 10% if omitted) but since the process is a 20 stage iteration, sometimes the default guess will not produce a final answer, so it is best to start with as near a guess as possible, particularly for large or negative answers.

The cash flow range of figures must include at least one positive and one negative figure, and it must be listed in the correct chronological order.  The examples given earlier in Figures 12 and 13 were calculated using the IRR function and are replicated in the spreadsheet supplied.

APR – Annual Percentage Rate
The formula used to calculate the APR uses the same principles as the IRR although the assumptions about the term are forced by the legislation, as is the resulting 1 decimal place rounding and 0.1% variation.  The Consumer Credit Act lays down the fundamental formula for calculating APR, slightly simplified as follows:-

Loan = Initial Fees + P1 / (1 + i) a + P2 / (1 + i) b + P3 / (1 + i) c  + …

Where P1, P2  etc are the periodic payments and a, b etc are the periods, usually 1,2 etc up to say 36 for a three year loan.  The payments include fees and costs where relevant, as prescribed by the Act.

The trick is to find i, which is the APR per period, and an iterative solution is essential.  If you assume the periods are months, i will give you the monthly APR, which is then easily changed to the annual APR with formula

(1 + i) 12 – 1 where i is the monthly rate.

The iterative procedures used to calculate IRR rely on a formula similar to this same, fundamental formula.  The results for the IRR and the APR are identical, given the same assumptions and accuracy restrictions.

I would not recommend you use this ‘official’ formula to calculate the APR in practise, because the simpler, automatic spreadsheet functions come up with the same result.

Net Present Value - NPV
This function is similar to IRR and calculates the net present value of a range of future cash flows, given an interest rate.  In other words, what would those cashflows be worth today to you today if you had a lump sum, instead of the future stream of incomes and outgoings.  It is given by NPV(rate, cash-flow range) where you enter an assumed fixed rate and the NPV produces a value equating to the projected cash flows range.  If the range is the same as for the IRR above and the IRR is used as the rate in NPV, the result will be zero.

One can compare and evaluate different cash flows by comparing their Net Present Values.  In some ways, the NPV is easier to visualise and is particularly useful when evaluating the costs of switching mortgages as is necessary before making a re-mortgage decision.  It can be likened to a cashback calculation.  But you must be careful to use the correct, relevant interest rate assumption.

The next step
We now have at our disposal all the proper tools so we can investigate actual loan products in some depth.  In Part II we look at these real life situations where interest rates are variable, fees are charged, options are offered and most importantly, we might have to choose a product from literally thousands of alternatives.

We will also see how to create wealth by borrowing.

 Previous Page          Next Page