Want To Use Excel To Calculate Your Mortgage Repayments?

If you borrow a lump sum over a number of years at a certain rate of interest then Excel has a financial function to help you calculate the monthly repayments. You might want to just calculate your monthly mortgage repayments or maybe you need to check how your mortgage repayments are affected by interest rate changes or by changes to the loan period? You can use Excel's PMT function to carry out these calculations.

The PMT function can actually be used in two different ways. Firstly it can be used to calculate the value of future savings based on regular monthly payments over a number of years. Secondly it can be used to calculate the monthly loan repayments for a lump sum borrowed over a number of years. For this article we'll look at the second way, a monthly loan repayment calculation based on borrowing a sum of money to be repaid over a fixed number of years at a specified annual interest rate.

We'll describe how to do this using Excel 2003, but the process is very similar in all versions of Excel. We're going to use the PMT function which contains three elements separated by commas and looks like this =PMT(interest, term, loan amount).

Because we want to calculate monthly repayments, in the actual formula we'll show the monthly interest rate for the first element, and the timescale in months for the second element. The third element contains the total sum borrowed.

So in an Excel spreadsheet create these headings, without the quotes. In Cell D4 type "Rate", in cell E4 type "Years", in cell F4 type "Amount", in cell G4 type "Payments" and in cell H4 type "Total".

Now we'll add some figures under the headings, so in cell D5 type in "3%" to represent the annual interest rate charged, in cell E5 type "25" to represent a 25 years loan period, and in cell F5 type "£100,000" to represent the amount borrowed.

Now we'll add the PMT function. In cell G5 type the function like this, without the quotes "=-PMT(D5/12, E5*12, F5)". Note there is a minus sign immediately after the equals sign. We need this to show our monthly outgoings as a positive number.

We show the monthly interest as D5/12 in the first element, and we show the period in months as E5*12 in the second element. The third element contains F5 which refers to the total amount borrowed.

Cell G5 now shows the value for the monthly repayment, for a loan of £100,000 borrowed over 25 years at an annual interest rate of 3%. This should be £344.27 for our example.

Now for the shocking bit. In cell H5 we want to calculate how much we actually pay over the 25 years in order to borrow £100,000. Type in the calculation, without the quotes " =G5*12*E5" and press Enter. This calculates the monthly repayment in cell G5 times 12 to give annual payments, times the period in years in cell E5 to give the total repayments. In our example it should show you pay £142,263.39 to borrow £100,000 over the 25 years.

You can try typing in different values in either cells D5 or E5 or F6 and Excel will show you the new monthly repayment value. And it doesn't have to be just for a mortgage. Interested in taking out a car loan? Decide how much you want to borrow, check out the annual interest rate and the timescale for the loan, type in the numbers, and you'll see the minimum monthly repayment.

We'll now add to our table to show different repayment calculations for different loan periods. Under the column heading "Years" extend the numbers from 25 down to 15, so cell E5 shows 25, E6 shows 24 and so on down to cell E15 which shows 15.

Edit the payment formula in cell G5 to look like this =-PMT(D$5/12,E5*12,F$5) and press enter to complete. We have added a couple of dollar symbols, one between the D and 5, the other between the F and 5. This has the effect of always using the interest rate from cell D5 and the loan amount in cell F5 as we change the value for the loan years. Now find the fill point by hovering over the formula in cell G5. The fill point is at the lower right hand corner of the cell (Hover, don't click) - the cursor becomes a small black cross - then press and hold down the left button, and drag down the cell to row 15, then let the mouse go.

You'll now see a column of figures showing the monthly mortgage repayments for each of the loan terms from 24 to 15 years.

Hopefully this article has shown you a glimpse of the power of Excel financial functions. Attending a training course would be a really good way to learn more about these, and help you fast track learning more about Excel.