Payment schedule (PMT) repayment vs interest only mortgage

Hi Gregor, the PMT is fine for a repayment loan, but things get more complicated for an interest-only loan and requires a bit of code to do so. A full explanation of this is here:

http://www.haresoftware.com/xlent_udf_repaymentmortgage.htm

Hope this helps,

Anthony

Hi Gregor

Thank you for your question

I think the simple answer is that the PMT function is not really suitable for calculating mortgage repayments in general. This is because the function requires interest rates to be fixed for the period of the loan, and this seldom applies to mortgages.

It might be possible to use a PMT function to calculate repayments if the interest was fixed for a period, BUT only for that period. So if the interest was fixed for two years then you might be able to calculate monthly payments for those 2 years. In this case I imagine that a repayment mortgage would use the total mortgage amount for the present value and the interest only mortgage would use the total interest payable at that interest rate as the present value.

However, I suspect that there would be additional factors that the formula could not account for and the answer would therefore be unreliable

Regards

Stephen