Haresoftware Home Page

The Bradford Factor
Search Engine
About Us


Excel UDF to calculate UK Repayment Mortgages

In England, there are essentially two ways to repay a large debt (in our case a mortgage) back to the Lender.  With the interest only option, the monthly payments go towards the interest on the loan, but none of the capital. You then have to set up a savings or investment vehicle to repay the capital at the end of the mortgage term.

With a repayment mortgage your monthly payments go towards paying off both the capital you have borrowed and the interest that is accruing on the debt.

The maths required to calculate the interest only option are fairly straightforward and a simple cell formula will suffice.  The Repayment option, due to its nature, is more complex and requires us to develop an Excel User-Defined Function (or UDF) to help us with the calculation.

Lets take a look at the maths involved first, then we can write our function.

When you buy a house or a car, you borrow the purchase price from a bank, and agree to pay the money back in regular equal instalments over a certain time. If the amount of the loan is , the interest rate is and the number of repayments is , what is the amount of the repayment ?

The key is to look at the problem from the point of view of the bank: their loan must be balanced by the present value of the repayments.

The present value of the first repayment is

the present value of the second repayment is

and, in general therefore:

the present value of the nth repayment is

The total present value of all the repayments is therefore

which is a geometric series of terms with first term and common ratio . The sum of the series is


When this is equated to , we obtain

For example, a loan of 100 000 is to be repaid over 25 years in equal monthly instalments. If the annual interest rate is 7%, what is the monthly repayment?

Here and (since the repayments are monthly). So


The repayments are made up of two components: interest on the outstanding balance, and an amount of capital repayment. The table below shows the breakdown of the first three repayments.

Payment   Interest   Capital Repayment   Outstanding Capital
1   583.33   123.45   99876.55
2   582.61   124.17   99752.38
3   581.89   124.89   99627.49

Initially, the monthly repayment is largely interest, with only 17.46% of capital repayment. The interest component falls and the capital repayment rises as the payments are made.

Now we have the mathematics sorted, we can now go ahead and write the UDF:

This is one method of converting the above formula to a UDF.  You may be able to condense this down to make it smarter or quicker or both.  Please do let me know if you do.


Public Function CAPITALANDINTEREST(Term As Integer, InterestRate As Double, LoanAmount As CurrencyAs Currency
  Dim First As SingleSecond As Single, intTerm As Integer, LoanPercent As Double

  On Error GoTo Err_CAI

  LoanPercent = InterestRate / 100
  intTerm = Term * 12

  First = LoanAmount * (LoanPercent / 12) * (1 + (LoanPercent / 12)) ^ intTerm
  Second = ((1 + (LoanPercent / 12)) ^ intTerm) - 1


  Exit Function


End Function

Generated using PrettyCode.Encoder

Send mail to the Webmaster Chap with questions or comments about this web site.
Copyright 1999-2008 Haresoftware.  All Rights Reserved.  Best viewed at 1024x768.