How to calculate monthly payments in Excel

Written by contributing writer
  • Share
  • Tweet
  • Share
  • Email

Calculating monthly payments is one of the most powerful functions in Excel. The payments calculator allows you to determine what your monthly payment will be for a given loan and allows you to compare monthly payments for different interest rates and repayment periods. Additionally, the payment functionality allows you to build a payment schedule and lets you easily calculate how much interest you can save by paying off a loan early. Using Excel to calculate monthly payments is relatively easy once you have the necessary information about your loans.

Skill level:

Things you need

  • Master promissory note from your lender
  • Microsoft Excel

Show MoreHide


  1. 1

    Collect the relevant information from your master promissory note, including the total amount of principal you owe, the interest rate on the loan and the number of years you have to repay the loan. If you did not save the original master promissory note you signed, you will have to request a copy from your lender.

  2. 2

    Use the payment formula in Excel to calculate your monthly payment. The payment formula is as follows: =PMT(rate,nper,pv) where "rate" is the interest rate on the loan, "nper" is the total number of payments you will make and "pv" is the amount of principal that you owe. For example, suppose you have a £16,250 loan at a 6 per cent annual interest rate that requires you to make monthly payments for 10 years. To calculate the monthly payment in Excel, enter =PMT(.5%,120,25000). Note that .5% = 6%/12 since 6 per cent is the annual interest rate and you are making monthly payments. Also, you need to enter 120 for "nper" since you will be making 12 monthly payments each year for 10 years. This calculation tells you that your monthly payment equals £180.

  3. 3

    You can decompose the monthly payment into principal vs. interest. Multiply the monthly interest rate (in this case .5 per cent) by the amount of principal outstanding to determine the amount of the payment used to pay interest. Subtract the amount of interest from the monthly payment to determine the amount of the payment used to pay principal. In this example, the amount of the payment used to pay interest equals 0.5% x £16,250 = £81 and the amount of the payment used to pay principal equals £180 - £81 = £99. The amount of principal outstanding after the first payment equals £16,250 - £99 = £16,150.

Tips and warnings

  • You can use the method outlined in Step 3 to calculate a repayment schedule for your loan. You will note that early on in the repayment period, a large portion of your monthly payment will be used to pay interest rather than principal. As you begin to pay the principal down, less of the payment will be used for interest, and you will pay the principal down faster.
  • Be wary of any prepayment penalties, which some lenders impose if you decide to pay off a loan early. If the penalties are large enough, they may offset any interest savings you will realise from paying the loan off early. Prepayment penalties will be spelt out clearly in your promissory note, and you should try and negotiate out of prepayment penalties when signing up for the loan.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.