Haresoftware - How to calculate UK Repayment Mortgage in Excel

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 Currency) As Currency
  Dim First As Single, Second 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

  CAPITALANDINTEREST = First / Second

  Exit Function

  Err_CAI:
  CAPITALANDINTEREST = 0.00

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.

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.