*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

*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.

**Spreadsheet
solutions
**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 +
P_{1} / (1 + i) ^{a} + P_{2} / (1 + i) ^{b} + P_{3}
/ (1 + i) ^{c }+ …

Where P_{1, }P_{2
}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

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.