Calculate a Monthly Payment in Excel

Revision as of 01:28, 9 January 2016 by Kipkis (Kipkis | contribs) (importing article from wikihow)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Excel is the spreadsheet application component of the Microsoft Office suite of programs. Using Microsoft Excel, you can calculate a monthly payment for any type of loan or credit card. This will allow you to be more accurate in your personal budgeting and to allocate adequate funds for your monthly payments. The best way to calculate a monthly payment in Excel is by using the "functions" feature.

Steps

  1. Launch Microsoft Excel and open a new workbook.
  2. Save the workbook file with an appropriate and descriptive name.
    • This will help you find your work later on if you need to refer to it or make changes to the information.
  3. Create labels in cells A1 down to A4 for the variables and result of your monthly payment calculation.
    • Type "Balance" in cell A1, "Interest rate" in cell A2 and "Periods" in cell A3.
    • Type "Monthly Payment" in cell A4.
  4. Enter the variables for your loan or credit card account in the cells from B1 down to B3 to create your Excel formula.
    • The outstanding balance due will be entered in cell B1.
    • The annual interest rate, divided by the number of accrual periods in a year, will be entered in cell B2. You can use an Excel formula here, such as "=.06/12" to represent 6 percent annual interest that is accrued monthly.
    • The number of periods for your loan will be entered in cell B3. If you are calculating the monthly payment for a credit card, enter the number of periods as the difference in months between today and the date you would like to have your account paid in full.
    • For example, if you would like to have your credit card account paid off 3 years from today, enter the number of periods as "36." Three years multiplied by 12 months per year is equal to 36.
  5. Select cell B4 by clicking on it.
  6. Click the function shortcut button at the left edge of the formula bar. It will be labeled "fx."
  7. Search for the "PMT" Excel formula if it is not shown in the list.
  8. Highlight the "PMT" function and then click the "OK" button.
  9. Create references to the cells in which your details have been entered for each field in the "Function Arguments" window.
    • Click inside the "Rate" field window and then click cell B2. The "Rate" field will now pull the information from this cell.
    • Repeat for the "Nper" field by clicking inside this field and then clicking cell B3 to force the number of periods to be pulled.
    • Repeat once more for the "PV" field by clicking inside the field and then clicking cell B1. This will force the balance of your loan or credit card account to be pulled for the function.
  10. Leave the "FV" and "Type" fields blank in the "Function Arguments" window.
  11. Complete the process by clicking the "OK" button.
    • Your calculated monthly payment will be shown in cell B4, next to the "Monthly Payment" label.
  12. Finished.

Video

Tips

  • Copy the cells from A1 down and over to B4, and then paste this in cells D1 through E4. This will allow you to edit the details in this second calculation to review alternate variables while preserving your initial calculation.

Warnings

  • Make sure you have properly converted your interest rate to a decimal and you divide the annual interest rate by the number of periods within a year that the interest is accrued. If your interest is accrued quarterly, you would divide the interest rate by 4. Semiannual interest rates are divided by 2.

Things You'll Need

  • Computer
  • Microsoft Excel
  • Account details

Related Articles

Sources and Citations