Oct 1 2007

Calculate a Loan Payment in Excel

Whether you are trying to buy a new house, looking at buying a new car or applying for MORE student loans, you have no doubt wondered what your monthly payment would be. Excel provides an easy to use formula in which you can enter the loan information, and it will calculate the monthly payment for you.  Once you learn to use it, it’s actually very simple.  So let’s go ahead and take a look at the PMT Formula:


[ad#in-post-ad]

=PMT(rate, nper, pv, fv, type)

where rate is your interest rate of the loan, nper is your number of payments for the loan (a 10 year loan with monthly payments would be 120 for nper), pv is the present value of the loan, fv is the future value of the loan (usually omitted from the calculation), and type is 0 if payments are at the end of the period or 1 if payments are the beginning of the period (also usually omitted from the calculations).

So normally we will format our formula like this: =PMT(rate, nper, -pv).  We take the negative of the present value here so that our loan payment calculation will be a positive payment.

Examples
Let’s go ahead and take a look at a few examples to clear this formula up for everyone.

Example 1: Calculate the monthly payment on a 10 year $50,000 loan with an interest rate of 5%.

=PMT(.05/12,120,-50,000) will return $530.33.  Since the rate is the rate is 5% a year and calculated monthly, you must divide the interest rate by 12 as I did above. The number of payments is 10 years at 12 per year which is total of 120.  And to get a positive payment amount for your answer you must make the present value of the loan negative.

Example 2: Calculate a 5 year loan for $20,000 at 10% interest, with quarterly payments.

=PMT(.1/4, 4*5, -20000) which also equals PMT(.025,20,-20000) will return $1282.94.  This time instead of dividing by 12 for monthly payments, we divided by 4 for quarterly payments.  Also when calculated the number of payments involved, we took 5 years times 4 payments per year for 20 payments.

Example 3: Calculate a 30 year loan for $200,000 at 6.5% interest, with monthly payments.

=PMT(.065/12, 30*12, -200000) which also equals PMT(.005417,360,-200000) will return $1264.14.  This is what a mortgage payment may look like for you.

Conclusion
This can be a very useful formula when trying to figure out what you can afford or hopefully help ease the shock of a new bill coming to your house for a new purchase.  There can be many variations of the formula used which we will explore soon, but for now this is a good place to start.  As always, if you have any questions, please you the contact form, or leave a comment below.

** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

8 Comments on this post

Trackbacks

  1. Jessica said:

    How can I calculate my interest payment, principal payment and balance for each month using Excel functions. I would like to be able to use the fill box to drag my data for the full duration of my 30 year loan. It is driving me crazy! Please help!

    Thank you,
    Crazy

    February 6th, 2008 at 12:28 pm
  2. john said:

    Are you asking how to calculate how much of the monthly payment you are paying is going towards interest and principle, as well as the remaining balance? I can definitely help you out with the if that is what you are looking for. I might even do a follow up post to this one for anyone else with that question.

    Thanks,
    John

    August 14th, 2008 at 10:06 am
  3. henry collado said:

    pls i want to know the mathematical equation or formula to get pmt function. Because i am curious if i want to tried to calculate it manually, How?

    big tnx…
    henry

    October 30th, 2009 at 3:34 am
  4. Bill said:

    I have to say, as a fairly well versed user of Excel, the assistance offered by this site was very, very helpful. I will refer several of my business colleagues to this site when they are in need of particular help on various functions. Thank you!

    January 27th, 2012 at 7:10 am
  5. Brenda said:

    1. Can you please help on this problem on Chapter 4 Apply Loan Payment Calculator. it says Use the Data Table button in the What-If Analysis to define the range E4:H19 as a one-input data table. Use cell C7 (interest rate) as the column input cell.

    2. Use the Page Setup dialog box to select the Fit to and Black and white options. Select the range B2:C9 and then use the Set Print Area command to set a print area. Use the Print button in the Print gallery in the Backstage view to print the worksheet. Use the Clear Print Area command to clear the print area. Name the following ranges B2:C9-Calculator;E2:H19-Rate_Schedule; and B2:H19-All Selections. Print each range by selecting the name in the Name box and using the Print Slection option on the Print tab in the Backstage view.

    October 1st, 2012 at 7:37 pm
  6. sonam said:

    what does it mean =PMT(rate, nper, pv, fv, type) how do i need to calculate, would you give me an example please.

    June 10th, 2013 at 4:20 am
  7. Desmond said:

    Can I just say what a comfort to find an individual who really knows what they’re talking about on the web. You definitely understand how to bring an issue to light and make it important. More and more people must check this out and understand this side of the story. I was surprised that you aren’t more popular because you definitely possess the gift.

    June 12th, 2013 at 12:46 am
  8. loan said:

    Nice post. I used to be verifying consistently this kind of blog page with this particular empowered! Invaluable information and facts especially the greatest aspect :) I personally retain these information considerably. I’m trying to find this selected details for the period of time. Thank you and also associated with luck.

    December 14th, 2013 at 2:31 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives