Spreadsheet Summary

Note: only Excel spreadsheets are now available, some of which are Excel 2010 .xlsx files.

You can download each spreadsheet individually and run it by selecting from the list below. The file sizes range from 25k to 232k so the download time should be minimal. Alternatively, you can download the whole set as a self-extracting file (1,129k) or a normal zip file (1,006k).

Spreadsheets to calculate residential mortgage and loan figures:-

Buy or Rent
(69k) UPDATED DEC 2014.
Choose first whether to buy or rent a house by entering your own ideas of the future. An immediate graph indicates when the optimum breakeven point occurs in terms of how long you stay in the property.

Stamp Duty Calculator
(39k) UPDATED March 2017.
Stamp Duty Land Tax rates as from 4th December 2014 for residential & non-residential property.

Mortgage Scheme Select Wizard See also on-line tools
(54k) Answer three simple questions to see which type of mortgage scheme suits you, and an explanation of each scheme.

Mortgage
(28k) A simple spreadsheet to illustrate the use of formulae in basic mortgage calculations using terms in years.

Loan
(27k) A simple spreadsheet to illustrate the use of formulae in basic loan calculations using terms in months.

Mortgage Affordability
(30k) A simple spreadsheet to calculate the mortgage advance to match what you can afford each month and an indication of the income you need.

Loan Comparator
(118k) Use this to accurately compare any two mortgage or loan products using the IRR (Internal Rate of Return) method. Calculates APR as well and supports up to two interest rate changes, illustrating the monthly payments. Probably the most useful spreadsheet of all for serious mortgage seekers. Also calculates ‘cash now’ equivalent to compare the best buy.

Equity Release
(23k) Check the equity remaining each year after a roll-up lifetime equity release mortgage.

Mortgage Illustration and Comparator See also on-line tools
(189k) Ideal for calculating and illustrating the key variables of a mortgage, including a payment schedule over the life of the loan, and a comparison tool.

Remortgage Check See also on-line tools
(46k) Determine whether it is worthwhile switching to another mortgage scheme by calculating the potential savings as a lump sum “Present Value”.

Spreadsheets to calculate more complex mortgage figures:-

Offset Mortgage NEW OCTOBER 2012
(109k) Understand how this very flexible mortgage operates in detail, using linked accounts. Choose any monthly payment, offset monthly credits and debits, and graph the results. See how you can shorten a mortgage life, save interest and acheive high net returns on your savings. Similar to Open Plan or Current Account mortgages.

Flexible Mortgage
(90k) Enter your own mortgage payment schedule and future interest rates: see some examples of how to use a flexible mortgage.

Investment Calculator for interest-only Mortgage
(55k) Illustrating the effect of using a savings scheme alongside an interest-only mortgage, and calculating the optimum savings rate from a given performance estimate: includes two examples.

Spreadsheets to illustrate house prices:-

Regional Property Values
(238k) Using regional indices since 1973, see how house prices have changed region-by-region, and estimate your own property value: updated quarterly and showing the effect of inflation. Need full Excel 2010 or better to view graphs.

Retail Prices Index (including Consumer Prices Index tab)
(60k) Using Retail Prices Indices since 1915, see how inflation has affected prices.

Spreadsheets for property investments:-

Investment Property Returns See also on-line tools
(47k) A very useful spreadsheet for those interested in buy-to-let or commercial property as an investment. Estimate the return on capital for almost any scenario. Determine when it pays to borrow and use gearing to enhance your return. Now includes two sheets with after-tax figures for each year of ten.

Commercial Investment Property Returns
(26k) Investment Property Returns (as above) but biased towards commercial property rather than buy-to-let.

Commercial Property Investment IRR Calculations
(64k) Enter details for any typical commercial property with quarterly rents and mortgage payments and calculate the IRR over any term assuming any quarterly change to rates or rents.

Property Portfolio
(52k) A dynamic example of how to build up a portfolio of 16 commercial properties over twenty years using mortgages and re-invested rental income and, given accurate initial assumptions, immediately calculate the estimated overall outcome. BEWARE CURRENT MARKET RATES MAY DIFFER FROM THOSE ILLUSTRATED

Spreadsheets for miscellaneous purposes:-

Monthly Mortgage Schedule
(57k) List and graph any monthly mortgage payment schedule against property equity with any interest rate or growth rate in each year: ideal for calculating new payments when changing from interest-only to capital repayment after a period.

Mortgage Product Design
(76k) Useful for a mortgage broker/agent to design a product to yield the lender's desired return, but choosing various discount schedules and cashbacks. Macros must be enabled.

Mortgage & Loan Product Design
(79k) Useful for a lender to help determine the margin required on any loan product. Then using “Goal Seek” macros, it will calculate backwards to determine say the cashback or discount that can be offered to match the required margin. Macros must be enabled.

APR Calculator
(90k) A general-purpose APR (Annual Percentage Rate) calculator, illustrating the use of “Goal Seek”. With two tabs, it is designed for both long term mortgages and shorter term loans where there may be an interest rate change every year, and some regular or one-off monthly or annual fees or charges. The Macro button automates the process for the mortgage cacluations - Macros must be enabled (disable protected mode) - not required for the loan calculations.

Project IRR
(49k) Take any project with monthly cashflows and calculate the IRR.

Quarterly Mortgage Schedule
(47k) List and graph any quarterly mortgage payment schedule against property equity with any interest rate or growth rate in each year.  Useful in planning commercial mortgage payment schedules, particularly for calculating payments when changing from interest-only to capital repayment after a period.

Mortgage Exposed Part I
(156k) A summary of the figures used in the tables in Part I of “Mortgages Exposed” in simple, unprotected spreadsheets so that the beginner can get some idea how the sheets are constructed.

Spreadsheets for classical products:-

Flexible Payment Mortgage
(83k) The product of the nineteen eighties, which included a low-start option and flexible payments. This sheet is more of historical interest rather than practical value.

Index Linked Mortgage
(49k) The first residential mortgage linked to inflation. As with the flexible payment mortgage, it is of historical andmathematical interest only.

Reverse Mortgages
(43k) Illustrating an unusual scheme to compete with home reversions for those over fifty and who are house rich but cash poor.

Rule of 78
(34k) Illustrating how to calculate the early settlement of a loan using this (now little used) method.