Calculate Mortgage Payments Formula
1. Mortgage repayment calculations are achievable is Excel using the PMT function. The PMT function has the following arguments:
– this is interest rate on the mortgage loan divided by 12
– this is the term of the mortgage or the number of monthly repayments you will make. For example with a 25 year mortgage you would make 12 multipled by 25 monthly repayments.
(present value) – is the mortage amount – the quantity you have borrowed, expressed as a negative value.
- you can leave blank. FV stands for future value. As the future of the loan when it has be repaid will be zero and zero is the default for this argument it can be left empty.
– here you state whether you will make the payment at the beginning or at the finish of each month, type 1 if at the starting or if at the finish. Sorry no alternative for halfway by way of the month.
2. An example. John takes out a £250,000 mortgage more than 25 years with an annual interest rate of four.five%. He will make his mortgage payment on the 1st of each month.
Rate would be four.5%/12
Nper would be 25*12
PV would be -250000
Type would be 1
3. To practice this example in Excel, in a blank spreadsheet enter the following data beginning in A1.
i) Rate goes in A1, four.five% goes in B1 and so on for each row.
Rate four.five%
Term 25
Mortgage 250000
Repayment
ii) Click into cell B4 – this is exactly where we will calculate the monthly repayment
iii) Now click on the fx button on the Excel formula bar just above the spreadsheet’s column headers. This will open the Insert Function dialogue box. In the search box kind PMT and then click Choose PMT from the results list below and then click
In the Rate box sort
In the Nper box type
In the Pv box kind
Leave the FV box empty
In the Type box sort
iv) Click Your answer should be 1384.39