There's a secret magical spreadsheet function that calculates mortgage payments
...and that function is PMT.
It is designed to calculate the amount required in a specific interval (e.g. a monthly payment) in order to pay off a given amount (e.g. a total mortgage) that has a recurring charge (e.g. an interest rate).
It is PHENOMENALLY helpful if you're looking at a new place and trying to estimate what you'll be paying every month1. It can also be used to calculate the value of an investment over a given period of time with a given interest rate too. The use and format appears to be consistent across Microsoft Excel, Google Sheets, and Apple Numbers.
The syntax is a little counter-intuitive but has some really useful optional extras. The full syntax runs:
PMT( interest_rate, intervals, current_value, [future_value], [payment_time])
Those arguments:
interest_rate: this is the interest rate, per period. If you're looking at mortgages at 5%, remember that you'll be paying that 5% over the course of a year. For mortgage-calculating purposes, we'd assume that we want to divide this value by 12 to signify once a month payments, so (0.05/12) in this example.
intervals: this is the number of times a payment will be made. In our fictional mortgage, let's say we're going to make a payment a month over 20 years. The number of intervals would therefore be 20 years times 12 months, or (20*12), or 240.
current_value: this is the present value of the loan. Remember: with a mortgage you OWE money, you have a debt. If you take out a £100,000 mortgage, the value here would be -100,000. It's a negative, to indicate debt.
future_value: this is the value you want to have after the number of intervals ends. Not useful for mortgage calculation, but helpful if you use the same function to calculate investments instead. If you started with a £100,000 investment2 and wanted it to be £200,000 after x intervals, you'd put this as 200,000. If left blank, it assumes 0. That's ideal for mortgage calculations.
payment_time: one that's slightly annoying. If set to 0, the function will assume that the payment is made at the end of the period. If set to 1, it assumes the payment is made at the start of the period. It's finnicky, and for mortgage payment calculation it doesn't make too much of a difference.
A simple example:
I want a mortgage for £100,000 at a 5% interest rate over 20 years. The syntax would be:
PMT(0.05/12, 20*12, -100,000)
That's your 5% interest rate split over twelve months, 20 years expressed in the number of months, and a debt of £100,000. It'll assume that the value after those 20 years will be zero (which is right because you'd like to pay off the debt). It calculates the output as £659.96.