How To Create a Loan Payment Spreadsheet or Calculator

Every lending organization uses Microsoft Excel to plot down and calculate the loan payment scheme of every loan applicant they encounter. By organizing it on a spreadsheet, they can automate the calculation process by plotting in the numbers and the totals for the monthly payments, and the interest rate will be computed and displayed automatically. Like the lending organizations, you can create your own loan payment spreadsheet to keep track of your loans and calculate the payments you will be required to make. A spreadsheet of this sort can help you to calculate payment plans for different lending organizations in order for you to compare and choose the best option for you.

Open the spreadsheet. Double-click the Microsoft Excel shortcut on your desktop to run the application. If you do not have the shortcut on your desktop, access the application by opening the “Start” menu and choosing Microsoft Excel from the programs menu. Once Excel loads, it will bring you to a brand new spreadsheet.

Create the labels. On the spreadsheet, you will need to label each row based on the information you will be plotting there. Since you will be creating a spreadsheet that will enable you to calculate and display the numbers for a particular loan, you will need to label each row as prescribed below:

  • Loan amount. This will represent the principal amount of the loan. Encode the label on the A1 cell.
  • Annual Interest Rate. Enter this label in cell A2. This is the annual interest rate that the lending organization will charge you for the loan.
  • Monthly Interest Rate. In cell A3, encode this label. This will serve as the calculated monthly interest rate that you will have to pay in addition to the payment plan.
  • Years to Pay. This label represents the number of years it will take you to pay back the principal amount. Enter this at cell A4.
  • Total Number of Payments. Enter this label at cell A5. This will represent the total number of payments based on the total number of years it will take you to pay the principal amount back.
  • Monthly Payment Amount. Label this at cell A6. This will represent the monthly payments you will be making. It will include a monthly payment against the principal amount including the interest rate for the month.
  • Total Payment. This cell will display the principal amount plus the total value of the interest rate for the whole loan period. Place this label at cell A7.

Enter the values. On column B, you will need to enter the values at each cell beside the labels on column A. Make sure that you follow and enter the details exactly as instructed.

  • Cell B1. This is the principal amount of the loan. Enter the exact loan amount. For example, if you are applying for a $10,000 loan, enter the amount in the cell.
  • Cell B2. Type the value pertaining to the annual interest rate. You can right-click the cell to format it to display the value as a percentage.
  • Cell B3. Type “=B2/12” in the cell and press “Enter”. The value should appear. Format the cell to display it as a percentage as well. This amount will signify the monthly interest rate.
  • Cell B4. Enter the number of years it will take you to pay the loan off.
  • Cell B5. Type this exactly, “=B4*12”. The value will display the total number of payments you will need to make.
  • Cell B6. Type this formula in the cell, “=PMT(B1,B3,B5)”. This will display the monthly payments you will need to make including interest.
  • Cell B7. Type “=B5*B6” to display the total amount you will need to pay for the loan. This will include the interest rate based on the total number of years it will take you to pay it back.

With this spreadsheet, you can enter other values at cells B1, B2, and B4 to calculate the other cells to reflect the appropriate values. Now, you have a fully automated spreadsheet that can assist you in evaluating and tracking your loans.


Share this article!

Follow us!

Find more helpful articles: