       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