# How to calculate principal and future payments on a historically underpaid loan amortization?

My sister has a seller-financed personal mortgage loan. Unbeknownst to her (and with no word from the seller) she has been mistakenly underpaying that mortgage for some time (e.g. instead of payments of \$850 she has been paying \$825). She has received no statements regarding mortgage principal balance. All outstanding principal and interest is due with the final payment (several years away still).

We'd like to get on a firm footing again with the loan, without changing the loan terms.

How can we calculate what the outstanding principal is, so as to calculate the proper payments going forward so that principal and interest are paid off at the termination of the loan period?

• Have you considered reaching out to the lender? They'll need to be in agreement with whatever numbers you come up with anyway. Commented Aug 3, 2023 at 22:15
• Yes. My assessment is that the lender isn't very savvy. I want to honestly help them to do the math and propose the path forward. I'm just looking for the right mathematical approach to calculate future payments to propose. Commented Aug 3, 2023 at 22:17
• I suggest a licensed CPA. Commented Aug 3, 2023 at 22:20

A rough way to "settle up" would be to just calculate the total shortfall (\$25 * N payments) and give that in a lump sum. The only thing the lender would be out on is the accrued interest of those shortfalls, which may not be enough to make it worth the time and effort to calculate (and explain).

The exact way would be to create an amortization schedule in your favorite spreadsheet app, and for each month, calculate the accrued interest, which would be the principal balance after the previous payment times monthly interest rate (annual rate/12). Subtract that from the payment amount to calculate the amount applied to principal. Repeat that for each month up to the present. That will tell you how much principal is still due. From there you can either true up in one lump payment or re-amortize for the remainder of the original loan period.

The math is not hard, but just tedious, so a spreadsheet will allow you to set up one or two months, verify the math, and copy the formulas to the remaining months.

As I said, it may just be easier to true up the missed principal (\$25 * N months) and don't sweat the accrued interest that was missed.

I took a \$160K loan at 5% amortized over 30 years and changed the payment from \$850 to \$825. Over 2 years the amount of interest lost was only \$29 (assuming you paid \$600 to make up for the 24 payments of \$25), so it's not enough to worry about calculating if it's not clear exactly how to calculate it.

In the end I took a combination approach and proposed two solutions to the mortgage lender. One solution was suggested by D Stanley above (Thank you!): offer a lump sum to bring principal to the value it should have been at this time, and then resume proper payments going forward. The second proposal was to increase payments going forward to bring the balance to zero at the original date of loan termination. I used an amortization spreadsheet which was designed to handle extra principal payments, with negative "extra" principal applied to previous payments (to reflect the underpayments). This allowed me to determine the difference in principal balance from expected. That difference was the lump sum for the first proposal. Then for proposal two I created a new amortization schedule starting from the current principal balance and a loan termination date which matched the original date. This gave me the payments necessary for the second proposal. We agreed to waive the additional interest already paid to the lender due to underpayment of principal to date, since this was due to the borrower's error. Both proposals were submitted to the mortgage lender, and they chose the lump sum approach. I used this public Google Sheets Template:

Example

Taking a 10,000 loan over 3 years with 10% annual interest compounded monthly, the monthly repayment `d` by standard formula is

``````s = 10000
r = 0.1/12
n = 36

d = r s/(1 - (1 + r)^-n) = 322.67
``````

Suppose you discover that since the start of the loan you have only been repaying 300 per month. If the next repayment is the 12th here's how you can reset the payments:-

The expected balance on month 12 is

``````n = 12
expected = (d + (1 + r)^n (r s - d))/r = 6992.58
``````

but the actual balance on month 12 would be

``````n = 12
d = 300
actual = (d + (1 + r)^n (r s - d))/r = 7277.46
``````

So the shortfall would be `7277.46 - 6992.58 = 284.88`

To rectify, on the 12th month pay `300 + 284.88 = 584.88` and thereafter pay 322.67 per month. Then the loan will be repaid on month 36 as expected.

Confirming with long-hand schedule, balance C36 & F36 ≈ 0.

• The expected balance can also be calculated using the Excel formula `=-CUMPRINC(0.1/12,36,10000,12+1,36,0)` which yields 6992.58 Commented Aug 4, 2023 at 14:23