View Full Version : Mortgage payoff formula

2016-07-28, 07:17 PM

I was hoping I could receive some help with the formula to calculate the payoff of a mortgage. So I have done the amortization table using the PPM, PPMT and IPMT for the payment, principal and interest payments respectively. No trouble there.

Now, here's the challenge: The original Loan amount is of $104697 for 145 months at a fixed rate of 10.11 % (yep it's high but I'm in Ecuador).
I am down to payment 23 (July 2016, loan started September 2014).

CHALLENGE: I want to add a one time payment of $15,000 to the principal on payment no.25 , normally, this would reduce the loan number of payments but it leaves the payment amount unchanged. What I am looking is to reduce the Payment amount without changing the total number of payments. One would ask why? Well, this office is being rented by someone who basically is paying part of the loan for me, but, since the $$ amount of the rent is less than the payment amount for the loan, I am paying some extra $$. What I am looking is to reduce that monthly payment (with this extra $15,000 to the principal) so that the rent covers my monthly loan payment.

Your help would be GREATLY appreciated.


2016-07-28, 07:35 PM
perhaps a sample sheet would help?

2016-07-28, 07:46 PM
Adjust your formula for principal to include an extra column for your extra payment (reducing the principal amount).
You may to need to adjust your spreadsheet to accomodate a different formula either the whole way down the column or just from this point forward.
I created just such a table for a mortgage where the family was making extra payments on principal each month.

Hope this helps, but if you need additional help getting started, just ask.

2016-07-28, 10:38 PM
Thanks for your answer, I have found some templates online were you can add the extra payment column. However, when doing this, those templates also reduce the number of payments, which I want to maintain. Do you have a formula or template for what I am looking for?? Many Thanks!

2016-07-29, 12:05 PM
Although we could work out the formulas in Excel, normally your lending institution's mortgage contract specifies an EXACT monthly payment amount. When you make a payment on principal, it does reduce the number of payments since you are still required to pay the original monthly amount.

Are you saying your mortgage does allow smaller monthly payments without penalty?