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

Matt_6360  ....  Better answers come quicker when you provide a concrete example, ideally in an Excel file that you attach or upload to a file-sharing website.

Presumably, you are talking about a debt ("unpaid balance").  But it is unclear if it is a loan (type=0) or a lease (type=1), for example.  And there are many more details that you omit.

Suppose the original "normally-amortized" loan is $100,000, to be repaid with 180 equal and regularly-occurring payments (e.g. monthly) at 1% per payment period, and you want to know the remaining principal (balance) after 90 payments.

You might estimate that with the following formula:

=FV(1%, 90, payment, -100000)

The "payment" (combined principal and interest; not one or the other) might be calculated by:

=ROUND(PMT(1%, 180, -100000), 2)

Alternatively, "payment" might be any arbitrary amount.  When you wrote ``we know the principal and interest payment``, that is probably what you mean.  I misread that as "payments" (plural), as if the interest payment were a fixed amount, which would not be a "normally-amortized" loan.  Mea culpa!

Hi Matt_6360 

By using the CUMPRINC() function, you can calculate the Unpaid Principal Balance after an installment is paid. A sample file is also attached for more clarity and understanding. Simple input the number of installments paid in cell C8.

Please let me know if it works for you.

Thanks

Tauqeer

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.