Excel Loan Payment Calculator

image Can you afford that new car? Or maybe you loaned money to one of your kids, and you want to calculate a repayment schedule. (Oh yes, they will stick to the plan, without fail.)

To help you figure out the payment amounts, here is a nifty Excel loan payment calculator. (The kids will think you’re cool when you say “nifty”.)

paymentcalculator01

It uses the PMT function to calculate the payment amount, and you can enter the variables:

  • Loan Amount
  • Payment Frequency
  • Term (Years)
  • Annual Interest Rate

How the Loan Payment Calculator Works

Of course, if you are the Bank of Dad, you might offer a lower interest rate, so you can adjust any, or all, or the green cells.

Type in any cell, except Payment Frequency, where you can select from a drop down list of options.

paymentcalculator02

The Lists sheet has a lookup table of frequencies and number of payments.

paymentcalculator04

Based on the frequency that you select, a number of payments per year is calculated in cell E5, using a VLOOKUP formula.

=IFERROR(VLOOKUP(C5,FreqLU,2,0),””)

paymentcalculator05

The payment amount is calculated with the PMT function:

=IFERROR(PMT(C7/E5,E6,-C4),””)

I added a minus sign before the present value variable, so the monthly payment is shown as a positive number. You can omit the minus sign, to show the payment as a negative number.

paymentcalculator06

Download the Excel Loan Payment Calculator

To see the formulas, and experiment with the calculator, you can download the Excel Loan Payment Calculator sample workbook. The file is in Excel 2007 format, and zipped. The calculator uses the IFERROR function, so you’ll need to change that if using an earlier version of Excel.

If you have any comments, or suggestions for enhancing the calculator, please let me know in the comments. Thanks!

______

You may also like...

4 Responses

  1. Contextures Blog » Excel Loan Payment Calculator « firlife.com says:

    […] the original post here: Contextures Blog » Excel Loan Payment Calculator […]

  2. Patrick M says:

    Great stuff as always, Debra!

    To spice things up a bit, I made a calculator a while back that will allow extra payments to principal, allowing for various combinations of one-time and/or recurring extra payments. In addition to summary info, it creates an entire amortization schedule:

    http://rdsrc.us/0tvlkw

  3. Carlos says:

    Excellent tips for those of us who are not very proficient with finances.

  4. Star says:

    Good site and v useful for student

Leave a Reply

Your email address will not be published. Required fields are marked *