Compound Monthly Interest Formula When Total Years Are Not Known

To paraphrase Spider-Man's Uncle Ben: "With great details come great answers".

For example, if the outstanding balance when payments stopped is $1000, and if late interest (*) compounds at 1% monthly (**), the interest accrued over 18 months is:

=1000 * (1+1%)^18 - 1000

or equivalently

=FV(1%, 18, 0, -1000) - 1000

formatted as Number, Currency, Accounting or a similar Custom format.

(Sometimes, Excel has a mind of its own, formatting results based on the types of data in the formula, regardless of the cell format that we set beforehand. )

Note: That assumes that you treat each month equally, which is common. OTOH, if you want to charge an exact daily rate each month: first, you need to decided if interest compounds daily or monthly; and second, you need to provide the due date of the first missed payment.


-----
(*) The late interest rate might be different from the interest rate for regular payments. Refer to the loan contract.

(**) If you provide an annual interest rate, you should also tell us how to convert it to a monthly (or daily) rate. There are 3 common methods, and perhaps others. For the US, a "conforming" loan will use the annualRate/12 monthly and annualRate/365 daily, although annualRate/366 is also permitted in leap years.