Spreadsheet Summary (Updated 29th May 2009)
Note: Only Excel spreadsheets are now periodically updated.
You can download each spreadsheet individually and run them immediately by selecting one from the alphabetical list below. The sizes range from 19k to 252k so the download time should be minimal. Alternatively, you can download the whole set as a self-extracting file, Excel (538 KB) or Lotus (406 KB). If you have a choice, the Excel files include better formatting, conditional formatting and data validation in some cases.
Spreadsheets
to calculate residential mortgage figures:-
Buy or
Rent (Excel (49k)
or Lotus)
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.
Mortgage
Scheme Select Wizard (Excel (55k) or Lotus) See
also on-line tools
Answer
three simple questions to see which type of mortgage scheme suits you, and an
explanation of each scheme.
Mortgage
(Excel (31k) or Lotus)
A
simple spreadsheet to illustrate the use of formulae in basic mortgage
calculations. Amended 24 Oct 07.
Mortgage
Affordability (Excel (19k) or Lotus 123)
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 (Excel (112k ) or Lotus)
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.
Mortgage
Illustration and Comparator (Excel (174k ) or Lotus) See
also on-line tools
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 (Excel (30k)
or Lotus) See
also on-line tools
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:-
Current
Account Mortgage (Excel (253k) or Lotus)
Understand
how this very flexible mortgage operates. Choose any monthly payment,
credit or debit and graph the results.
Flexible
Mortgage (Excel (65k) or Lotus)
Enter
your own mortgage payment schedule and future interest rates: see some examples of how to use a flexible
mortgage.
Investment
and Mortgage Calculator Excel (38k) or Lotus
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 (Excel (139k) or Lotus) Excel
updated quarterly
Using
regional indices since 1973, see how house prices have changed
region-by-region, and estimate your own property value.
Spreadsheets for
property investments:-
Investment
Property Returns (Excel (38k) or Lotus) See
also on-line tools
A
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.
Commercial
Investment Property Returns (Excel only 40k)
As
Investment Property Returns (above) but biased towards commercial property
rather than buy-to-let.
Commercial
Property Investment IRR Calculations (Excel only 53k)
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 (Excel (39k) or Lotus)
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 initial
assumptions, immediately calculate the surprising outcome.
Spreadsheets for
miscellaneous purposes:-
Monthly
Mortgage Schedule (Excel Only (38k))
List
and graph any monthly mortgage payment schedule against property equity with
any interest rate or growth rate in each year. Ideal for calculating
payments when changing from interest-only to capital repayment after a period.
Mortgage
Product Design (Excel (70k))
Useful
for a mortgage broker/agent to design his own product to yield the lender's
desired return, but choosing various discount schedules and cashbacks. Macros must be enabled in Excel.
Mortgage
& Loan Product Design (Excel (66k) or Lotus)
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 (Excel (77k)
or Lotus)
TCC Bug fixed 29 May 09
A
general-purpose APR (Annual Percentage Rate) calculator, illustrating the use
of “Goal Seek” (or Backsolver in Lotus).
It is designed for long 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. Macros must be enabled in Excel.
Project
IRR (Excel (36k) or Lotus)
Take
any project with monthly cashflows and calculate the IRR
Quarterly
Mortgage Schedule (Excel Only (91k))
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 (Excel (153k) or Lotus)
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 (Excel (59k)or Lotus)
The
product of the eighties, which included a low-start option and flexible
payments. This sheet is more of
historical interest rather than practical value.
Index
Linked (Excel (34k) or Lotus)
The
first residential mortgage linked to inflation.
As with the flexible payment mortgage, it is of historical and
mathematical interest only.
Reverse
Mortgages (Excel (28k) or Lotus)
Illustrating
an unusual scheme to compete with home reversions for those over fifty who are
house rich but cash poor.
Rule of
78 (Excel (26k)
or Lotus)
Illustrating
how to calculate the early settlement of a loan using this method.