How to Use Excel to Calculate Amortization

Written by carrieanne larmore
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Use Excel to Calculate Amortization
Calculate your monthly loan or mortgage payments in Excel. (Hemera Technologies/ Images)

Calculating the amortisation of a mortgage or personal loan can be done quickly through Excel by using a simple formula. An amortisation calculation is needed to determine the monthly amount required in order to payoff the loan, as most of the payments at the beginning go toward paying the interest. This Excel amortisation formula calculates the payment amount based on the original loan amount, years to payoff, annual interest rate and number of payments per year.

Skill level:

Things you need

  • Microsoft Excel
  • Loan principal amount
  • Loan number of years to payoff
  • Loan annual interest rate
  • Number of payments per year

Show MoreHide


  1. 1

    Collect the numbers needed in order to calculate the amortisation, which is the original loan amount, years to payoff, annual interest rate and number of payments per year.

  2. 2

    Open Microsoft Excel with a new, blank spreadsheet.

  3. 3

    Enter a title in the cell A1, so that you can recognise what this chart was for when referring to it later. For example, title it "Loan Amortization" or "Monthly Loan Payment Amount."

  4. 4

    Type "Original Loan Amount" in cell A2.

  5. 5

    Enter the amount of the original loan in cell B2. This should be the amount the loan was issued for, not including interest. This is often also referred to as the principal amount.

  6. 6

    Type "Loan Duration (Years)" in cell A3.

  7. 7

    Enter the number of years the loan was issued for in cell B3. This should be the length of time of the loan's duration. For example, if this is for a 30-year mortgage, then type "30" in this cell.

  8. 8

    Type "Annual Interest Rate" in the cell B4.

  9. 9

    Enter the annual interest rate in the cell B4. For example, if the loan was issued at 6.5 per cent annual interest, then type "6.5%" or "0.065" into the cell.

  10. 10

    Type "Number of Payments" in cell A5.

  11. 11

    Enter the total number of payments that will be made each year in the cell B5. For example, if you must pay monthly payments, then enter "12" in this cell.

  12. 12

    Type "Monthly Payment" in cell A6.

  13. 13

    Enter the following formula in cell B6: =PMT(B4/B5,B3*B5-B2). Be sure to include the equal sign and not include the period at the end of that sentence.

  14. 14

    Click "Enter" after entering the formula to see the number that results in the cell B6. This is the monthly payment for your 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.