LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default How to do UK mortgage math?

Consider a 15-year UK loan of 100,000 (British pounds) at 6% with
monthly payments. How is the monthly payment amount computed? How is
the monthly interest amount computed in an amortization schedule?

(And can someone point me to the UK (or High Street Bank) equivalent
of the US Truth in Lending regulations/guidelines online? I thought I
found them for myself some time ago. But I cannot find them now.)

One online source [1] computes the monthly payment by PMT(6%, 180,
-100000)/12, which is about 858.02 (must be rounded). That agrees
with an independent online UK mortgage calculator [2].

Based on that, I might think that the monthly interest rate is
RATE(180, 858.02, -100000), which is about 0.521746%. Thus, I might
compute monthly interest by B1*RATE(...), where B1 is the previous
period's ending balance (initially 100000). That works out to about
521.75 for the first month.

I have couple problems with those conclusions.

First, the same online UK mortgage calculator [2] computes a monthly
payment of 500 for an interest-only mortgage for the same loan terms.
That would suggest that the monthly rate is simply 6%/12, or
0.500000%.

Second, in another thread, the OP says that UK mortgage interest is
"calculated daily and added monthly", and that the daily interest is
compounded. Is that correct?

If that is correct, how is the daily rate computed?

Ostensibly, it might be r/d (simple interest) or (1+r)^(1/d)-1
(compound interest), where "r" is the monthly rate and "d" is the
number of days in the month (assuming on-time payments). But that
would mean the daily rate is non-uniform. Is it really?

Alternatively, the daily rate might be 6%/365. (Or some other uniform
rate?)

Either way, that seems inconsistent with the way the monthly payment
is computed.


-----
Endnotes

[1] "Mortgage repayment tables" link on http://www.cml.org.uk/cml/consumers/calculator
..

[2] http://www.bbc.co.uk/homes/property/...lculator.shtml .

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mortgage Calculator tom Excel Discussion (Misc queries) 4 August 1st 06 03:39 PM
Calculating APR on a Mortgage Sean B Excel Discussion (Misc queries) 12 June 12th 06 05:07 AM
Mortgage Formulas Bryan New Users to Excel 1 September 10th 05 03:22 AM
Mortgage functions Cynthia A via OfficeKB.com Excel Worksheet Functions 0 August 3rd 05 04:16 AM
mortgage eddie New Users to Excel 2 June 24th 05 10:41 PM


All times are GMT +1. The time now is 06:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"