Calculating the Principal paid on an Installment in excel

On typical loan we make a part of our payment to the principal and a part to the interest. In the first payment period we pay the maximum Interest and minimum Principal, and in the last payment period we pay the minimum Interest and maximum Principal. So this ratio varies from payment to payment. To calculate this we can use Microsoft Excel’s formulas easily.

  1. Loan principal: The amount borrowed.
  2. Annual Interest rate: The rate that a borrower pays to the lender. Usually it expressed as a percent(of the principal) per year.
  3. Number of payment periods How many times you have to pay
  4. Number of the questioned period Number of the actual period for which you want to calculate the interest

Here we are going to use the IPMT() function

IPMT(rate,per,nper,pv,fv,type)
  • Rate is the interest rate per period.
  • Per is the period for which you want to find the interest and must be in the range 1 to nper.
  • Nper is the total number of payment periods in an annuity.
  • Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.
  • Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
  • Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

Make a Excel sheet like the example below
Calculate the Interest and the Principal paid on each Installment

And put this code in cell “B7”

=PPMT(B3/12,B5,B4,B2)

where B3 is the annual interest rate, B5 is the period you want to calculate, B4 total numbers of periods/installment have to pay and B2 loan principal.
And you will have the amount of the Principal you have to pay the quired period.

Add a Comment

Your email address will not be published. Required fields are marked *

Notify me of followup comments via e-mail. You can also subscribe without commenting.