calculate loan interest paid, declining balance and months to payoff

looking for a good way to calculate loan interest paid, declining balance and months to payoff

i.e: $7453 loan at 5.25% with a $51.8 a month payment would provide a monthly interest payment of xxx and a monthly principle payment of xxx
number of monthly payments to payoff?

ok that works, thanks

I see the term is # of months to repay. Great
Thanks

Originally Posted by alansidman

Take a look at the template I built. I have only populated for a year. Copy down to complete

Your use of NPER is incorrect, resulting in an incorrect number of payments.

The pmt and pv parameters should have opposite signs, following Excel's signed cash flow paradigm. So:

=NPER(B2/12,B3,-B1)
or
=NPER(B2/12,-B3,B1)

The choice of cash flow signs is arbitrary. You can think of it as depending on your point of view: borrower or lender.

In this example, the correct NPER result is more than 227.

To demonstrate proof of concept, change A19 to =A18+1 and copy A19:F19 into A20:F235 (228 payments).

The end of the table is:

Please Login or Register  to view this content.

Made the correction
Thanks