9

Just for some example figures:

  • Original Term: 25 Years
  • Loan Amount: £100,000
  • Interest rate: 2%

I'm trying to make an excel table which keeps track of my mortgage payments including overpayments. When making an overpayment I was asked if I would like to use this payment to reduce my future payments or keep future payments the same and reduce the term.

I selected to reduce the term (this seemed smarter) but I can't work out how the bank has calculated my term reduction as a result of my over payment.

For example, I made an overpayment of £500 and they said "As a result of this payment, your term has been reduced by 1 month". What equation did they use to come to this figure please?

  • A handy equation to have in a table I would think! – nickson104 Nov 20 '15 at 8:42
  • 6
    "(this seemed smarter)" - yes, it was. – AakashM Nov 20 '15 at 10:44
  • 2
    @AakashM Isn't the choice really just how its presented on the next statement? Either way the payment reduces principle, right? In other words, I don't think that it makes any financial difference if you keep making the same payments. – JPhi1618 Nov 20 '15 at 21:01
  • Not sure about UK. In US, if you say the 'reduce future payments' option, what it really means is they don't credit the payment to your account until the next month - meaning you don't get the benefit of reduced principal. (Hopefully the UK has saner regulations there.) – Joe Nov 23 '15 at 18:55
  • Excel (at least the latest few versions) has built-in templates for mortgage calculators. Unless you're doing something that you need customized, I suggest browsing through these. – Wesley Marshall Nov 24 '15 at 20:51
14

Here is a quick way to estimate the reduction in your term. The numbers you need to start with are in bold.

  1. Take the amount of the extra principal payment. For example, £ 500.
  2. Take your interest rate. For example, 2 %/year (as an APR).
  3. Calculate your monthly interest rate, and convert to a decimal. For example, 2 %/year * (year/12 months) = 1/6 of 1 %/month ~ 0.001666666/month.
  4. Add 1 to the monthly interest rate. For example, 1.001666666.
  5. Find the number of months remaining on your loan. For example, 20 years * (12 months/year) = 240 months.
  6. Raise the value from step 4 to the power from step 5. For example, 1.001666666 ^ 240 ~ 1.4913.
  7. Multiply the value from step 6 by the extra principal payment from step 1. For example, 1.4913 * £ 500 ~ £ 745. This is the amount of money you will avoid paying at the end of the mortgage.
  8. Find the amount you are paying per month for principal and interest. Don't include taxes, insurance, or homeowners dues. For example, £ 750/month.
  9. Divide the amount from step 7 by the monthly amount from step 8. This is the number of months by which you have shortened your mortgage. In this example, you have shortened your mortgage by a little less than one month.

For low interest rates (like 2 %/year), you can ignore the compounding within each year. You can convert the APR to a decimal, add 1, and raise to the power of the number of years remaining. For example, 1.02 ^ 20 ~ 1.4859. Then continue with step 7. For example, £ 743 / (£ 750/month) is still a little less than one month. If your interest rate is positive, this approximation is conservative. It will slightly underestimate your savings (or overestimate how much time is left on your mortgage).

If you really need to be accurate, you can redo the calculation using the new length of the mortgage. For example, 239 months instead of 240 months. In this example, the difference is very small. But if your monthly interest rate times the number of months you shortened the mortgage is "big enough", then this correction might matter.

  • 2
    This method slightly overestimates the monthly interest in step 3 and 4. The correct calculation would be the the twelfth root of 1 plus the yearly interest rate. In your example, the monthly interest rate would be calculated as 1.02 ^(1/12) = 1.0016515813, equal to 0.16515813% per month. – Jacob Bundgaard Nov 20 '15 at 10:52
  • 2
    @JacobBundgaard: That depends on if the interest rate is nominal APR, and on which compounding period. In the Netherlands (I don't know about other countries) it is normal for banks to state mortgage interests in such a way that Jasper's calculation is exact. The percentage that they can advertise is smaller, which looks nicer... – user35113 Nov 20 '15 at 13:09
  • 1
    @Pakk. Oh, okay. In Denmark, the advertised rate is usually the effective interest rate. That just means you have to be careful to plug the nominal interest rate into the formula. (I now see that the answer actually does state to use the APR.) – Jacob Bundgaard Nov 20 '15 at 13:24
  • 1
    @JacobBundgaard - On my UK mortgage (no idea if it applies to all UK mortgages, though it seems likely), compounding only happens annually. If we take the year as running from January to December, this means that any interest charged in 2015 only starts causing interest on itself in 2016. This makes Jasper's calculation correct. – AndyT Nov 20 '15 at 14:25
  • 1
    @JacobBundgaard -- The Annual Percentage Yield (APY) includes the effect of compounding within the year. If it is easier for you to look up the APY, just skip to the "low interest" shortcut. If you use the APY (instead of the APR) in the shortcut, you will fix the approximation that I mention in the shortcut. – Jasper Nov 20 '15 at 16:17
6

If you've got your basic mortgage payment formula in place already (see https://en.wikipedia.org/wiki/Mortgage_calculator#Monthly_payment_formula ) then you can simply re-base the calculations from the date you make the overpayment.

Take the new current balance (as of the day after the overpayment) as the starting date, plug in the same interest rate and the old monthly payment, and calculate after how many months the total owing reaches 0.

Alternatively you could use the Money Saving Expert one which lets you work it out for yourself: http://www.moneysavingexpert.com/mortgages/mortgage-overpayment-calculator

2

When you are paying a mortgage with Z% APR interest, money given to the mortgage in 1 year is worth 1/(1+Z%) times as much. Ie, with 100% APR interest and a 100$ debt, paying 100$ today or 200$ in one year wipes out the debt.

You can translate all of your mortgage payments to "present value" using this technique: in effect, you owe 100,000$ today, and all of your future mortgage payments can be treated as time-deferred payments against that debt.

From this we can develop a mortgage equation. We start with:

1+x+x^2+...+x^n=(1-x^(n+1))/(1-x)

A mortgage payment is a regular set of payments, each with a discount factor. If the per-time-period discount factor is x, a set of n payments equally spaced ends up being worth 1+x+x^2+...+x^n times as much as a single payment.

If we have 0.1% monthly interest and our payments are 750$ and our remaining debt is 100,000$, we get:

(1-x^(n+1))/(1-0.999)

Using high school algebra algebra, we can solve for n. I'll skip the intermediate steps:

0.999^(n+1) = 0.4/3

(n+1) ln 0.999 = ln 0.866666

(n+1) = 143.02928128781714462504957962223...

So 142 months, plus a smidgen. (This assumes there is a payment due immediately)

If we pay down principle, that simply changes one part of the equation.

Note that 0.999 is only an approximation of the discount factor if you are charged 0.1% interest per month. The correct value is (1-(1/(1.001)) = 0.999099909990999.

However, there will be variations based on local mortgage laws and what the interest numbers mean. In the end, the local mortgage laws and your contract describe how mortgage is calculated, when it is compounded, and what the rate advertised means.

Because of this, and the issues with rounding etc, the right way to do this is not with an equation. Instead, simulate.

You start with the current amount you owe.

Then you deposit the money, reducing that amount.

Then, you simulate the payment schedule, accumulating interest and making regular payments. This simulation will have a point where you owe nothing. That is the resulting term.

Equations like the above let you approach the problem analytically, in that I can calculate useful approximations like the derivative of mortgage length with respect to downpayment, or interest rates, or payment amount, or payment frequency, in a continuous manner.

In the end, a 30 year mortgage consists of a mere 360 payments. Asking a computer to do that simulation, where your interest can even vary by the number of days in each month, is easy; and calculating the result this way is as viable as using a fancy equation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.