Solving for Principal Balance (X). when we know the principal and interest payment, term and rate

Matt_6360  .... tauqeeracma  wrote:  use ``the CUMPRINC() function``

The primary problem with the CUMPRINC function is:  the periodic payment is calculated internally using the unrounded result from the PMT function.

That might or might not be right for your problem.  You wrote that the "principal and interest payment" (i.e. the regular payment) is  given.  It is necessarily a rounded value; and it might be rounded to any precision.

For my example, PMT(1%,180,-100000) returns 1200.16806209151.  That might be rounded to 1200.17 or 1200 or even 1201 (i.e. rounding up so that the last payment is less).

And more to the point, the given regular payment might any arbitrary smaller value; for example, 1000.  In a normally-amortized loan, the effect is to have a non-zero final balance, which is added to the last regular payment, creating a "balloon payment".

That is another problem with using CUMPRINC:  it does not have an "fv" parameter, as the PMT, IPMT and PPMT functions do.  So, CUMPRINC cannot be used with normally-amortized loans that have a "balloon payment".

Finally, beware that CUMPRINC does not give the correct result for type=1 normally-amortized loans (payment at the beginning of the period; aka "payment in advance").  It is a design flaw that is common to the related functions:  CUMIPMT, IPMT and PPMT.