Negative Amortization Payoff

I am posting this as an example on how an amortization can be programed to find the final payoff with unusual circumstances.

I was asked by a reader for help with determining the final loan payment on a special situation loan. For most of you, your inhouse system would probably handle this.

Anyway, using an amortization schedule with an extra payment option can help for this type of situation.

The problem looked something like this:

A borrower takes out a $200,000 loan at 5.00% for 30 years, with a 5-year balloon. The monthly payment was $1,073.64. The penalty, if the loan is not refinanced or paid off at the end of 5 years, is a new rate of 15%.

The new payment at 15% would be $2,352.34, calculated using the remaining term of the loan (300 months).

Instead, the borrower continues to make the original payment ($1,073.64) on the loan. This obviously results in hefty negative amortization starting at payment 61, since the difference between the old payment and the new payment is $1,278.70.

The borrower continues to make the original payment through payment 80. Then on payment 81, starts making the payment he should have been making all along of $2,352.34. This is where the Extra Payment column comes in handy. Instead of making the payment change at payment 81, we just add on the difference between the two payments ($1,278.70) as an extra payment each month.

Finally, at payment 175, the borrower decides to refinance. What is the final payoff? The principal balance at payment 175 is $262,266.49 and Interest is $3,278.33 for a total of $265,544.82.

Although I doubt a situation just like this will happen very often, it shows that amortization schedules can be programed to solved for negative amortization.

Download:

http://pistulka.com/Excel_Shared/Negative_Am.xlsx

 

Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

5 Comments

  1. Nice website with good templates but I don’t see the download link for the negative amortization spreadsheet.

    1. Joaquin,
      Thank you for the compliment. You were a former bond guy? Did our paths cross at any time in the past? As far as projects, if I can’t complete the task in an hour or less, it feels is too much like work.

      Thanks again
      Don

Leave a Reply

Your email address will not be published. Required fields are marked *