On Jun 26, 2:27 pm, Formula Fred
wrote:
But in the UK all interest (savings and mortgage) is compound not simple,
so it is compounded daily and added monthly

Oh, that is useful information. Unfortunately, I am not familiar with
UK mortgages per se. If interest compounds daily, I wonder how the
amount of monthly payment and daily interest rate are computed. I
will look around for an online UK mortgage calculator.

(This exemplifies why you should post this inquiry in a new thread
with an appropriate subject. Then it is likely to be noticed by other
participants, some of whom I know are UK residents who might be
familiar with vagaries of UK mortgates.)

In the meantime, let me amend my previous formulas to reflect daily
compounding. They might still not agree with UK computations because
I am making a huge assumption about how the daily interest rate is
computed. But at least it might provide you with an appropriate
paradigm into which you can plug in the appropriate numbers, once you
learn what they are.

Consider a 15-year loan of $100,000 with a fixed (nominal) interest
rate of 6% and monthly payments. For now, assume the monthly payment
is $844.

Previously, I wrote:
Suppose payments are due on the 15-th of each month,
and the first period begins on 15 Feb 2006. If A1 contains
2/15/2006, A2 contains 3/15/2006, and E1 contains the initial
balance (100000), then the interest for the first payment is (in C2):
=E1*(A2-A1)*6%/365
and the new balance is (in E2):
=E1+C2-B2
where B2 is the payment (844).

For daily compounding, the formula in C2 might instead be:

=E1*(1+6%/365)^(A2-A1) - E1

The formula in E2 would remain the same.

Again, this presumes that 6% is a nominal interest rate. I wonder if
in the UK, interest rates are specified as "APRs" -- that is,
compounded ("effective") interest rates. If that is the case, replace
1+6%/365 with (1+6%)^(1/365), which can be computed once in an
auxilliary cell.

(But in that case, the payment could be reduced to $836 for 180
payments.)