How To Calculate a Principal Payment Using Microsoft Excel

The great thing about Microsoft Excel is that it is jam packed with features and functions that actually make it very easy for even the most novice user to create certain financial spreadsheets.  Take loans for example.  To make the computations for loan payments on the spreadsheet a breeze, Microsoft Excel has given a user the formulas to make every single computation easier for as long as the figures are encoded on specific cells on the spreadsheet.  For instance, if you are computing for the principal payment of a particular loan, then you can use the integrated PPMT formula in the application to do so.  This formula allows you to calculate the principal payment of a loan depending on factors such as interest rates, duration of payment, and the total number of payments all in relation with the total value of the loan.  It may sound confusing for you so to make it simpler; here are some steps you can use to help you compute using the PPMT formula.

  • Understand the gist.  Before using the function, make sure that you understand how it gets done first.  The gist should be enough.  Basically, the PPMT formula is based on the actual formula to calculate the principal payments of loans.  The formula is stated as pmt = pv * rate /(1-1/(1 + rate)^nper).  Alright, if you are new to this then you will need to know what each term in the equations represents. 
    • PMT – is the representation for the total payment.
    • PV – refers to the loan value.
    • Rate – refers to the current interest rates being used.
    • Nper – refers to the total number of payments for the duration of the loan.
  • To provide you a better understanding, the calculation process starts out by computing the interest payment which is represented by “PV*rate”.  The figure derived will then be deducted from the total payment to get the initial principal amount for the first scheduled payment.  Now this figure is then deducted to the loan value to get the remaining principal amount owed.  The process is cyclical until you reach the desired number of payments or the specific timeframe.
  • Start using Excel.  Enter the formula in a cell on the spreadsheet.  Basically, the formula should look like this, =PPMT(rate,per,nper,pv,[fv],[type]).  This formula should easily be detected by Excel as soon as you type in “=PMMT” and will offer to complete the formula for you.  Press enter and the function will be completed.  The next step will be to indicate the value for each representation.
  • Input the values.  Now is the time to input the values of each representation in the formula.  Start with the interest rate, the period of payment, the number of payments, and the loan value.  You can do this by either replacing the representations with the figures or by selecting certain cells where each representation can refer to.  Obviously, you will need to have certain figures on the spreadsheet to complete the process such as the remaining balance of the loan from the previous payment.  Make sure the data is ready before completing the formula. 

Now that you know how to compute for the principal payment of a loan using Microsoft Excel, you can now start tracking your payments and loan maturity.  This will be a huge help in managing your finances when it comes to debt repayment.


Share this article!

Follow us!

Find more helpful articles: