How to calculate rental cover for buy-to-let mortgages, with Excel formulas

The amount you can borrow on a buy-to-let mortgage will currently be restricted by two main criteria (possibly more for "portfolio landlords"). These are Loan To Value (LTV) and Rental Cover (or just Rent Cover).

Don't want to know the calculations? You can quickly check if your next purchase satisfies the rental cover with our buy-to-let calculator.

What is Rental Cover?

Rental Cover is a relatively new restriction that’s now being placed on all buy-to-let mortgages. This value is the percentage of the mortgage (at a stress rate) that the rent must be. A fairly standard value for it is 150% but it varies between lenders and also on your own situation. Scroll down for some examples.

There are three useful sums related to rental cover, depending on what you know and which you're trying to calculate. The sections below each detail one of the calculations, complete with an excel formula for it.

Calculate minimum required rent from mortgage and rental cover

For this you need to know how much you'll be borrowing in your mortgage and what their rental cover requirements are (including the stress rate they use).

Here's the calculation (followed by an example):

(([mortgage] * [stress rate]) / 12) * [rental cover] = [required rent]

Here's an example: £150,000 mortgage, 5.5% stress rate, 150% rental cover.

This makes the calculation:

((150000 * 0.055) / 12) * 1.5 = 1031.25

We can split it up and explain the calculation in two parts:

First, calculating what the mortgage payments would be at the "stress" interest rate:

150000 * 0.055 / 12 = 687.50

Then multiply that up to provide the required rent for 150% cover:

687.5 * 1.5 = £1,031.25

So to get the mortgage you'll need to prove the property can fetch a rent of £1,031.25 per month.

You can copy (and adapt) this Excel formula for use in your own spreadsheets:

=((A1*B1)/12)*C1

Where: A1 is your mortgage borrowing, B1 is the stress rate (as a fraction / with percentage formatting) and C1 is the rental cover required (also as a fraction / percentage).

A fully working Excel example is also available for download.

Calculate maximum mortgage from rent and rental cover

Here you know the rent you're expecting and the mortgage rental cover requirements, so want to calculate the maximum you'll be able to borrow.

You can use this calculation:

(([monthly rent] * 12) / [stress rate]) / [rental cover] = [max mortgage]

Let's look at an example: £900 monthly rent, 5.5% stress rate and 150% rental cover.

Making the calculation as follows:

((900 * 12) / 0.055) / 1.5 = 130909.09

Meaning the maximum mortgage you'll be able to borrow in this situation is £130,909.09 (assuming you also satisfy LTV and other requirements).

Here's an Excel formula version for use in your own spreadsheets:

=((A1*12)/B1)/C1

Where: A1 is your monthly rent, B1 is the stress rate (as a fraction / with percentage formatting) and C1 is the rental cover required (also as a fraction / percentage).

A fully working Excel example is also available for download.

Calculate rental cover from mortgage and rent

In this final scenario you know how much you need to borrow as a mortgage and you know what rent you'll be able to get, but what rental cover does that equate to (and hence will it satisfy your mortgage company). For this you will already need to know the stress rate that's being applied though.

This is the calculation:

[monthly rent] / (([mortgage] * [stress rate]) / 12) = [rental cover]

This spits out the rental cover as a fraction so you'll need to multiply it by 100 to get back to a percentage.

Here's another example: £900 monthly rent, £180,000 mortgage required, 5.5% stress rate.

Which works out like this:

900 / ((180000 * 0.055) / 12) = 1.09

Hence our rent in this scenario only provides 109% rental cover - looks like we'll need to find a different property.

The final Excel formula for you to use and adapt in your own spreadsheets:

=A1/((B1*C1)/12)

Where: A1 is your monthly rent, B1 is the mortgage you need and C1 is the stress rate (as a fraction / with percentage formatting).

A fully working Excel example is also available for download.

Want an easier option?

Do you just want to know if you can afford the house you're looking at?

Or just want to know the maximum mortgage you could get on it?

Then take a look at our buy-to-let prospecting tool to quickly track all the properties you're considering. Complete with all the calculations and comparisons done for you.

Read more from PaTMa