# Create a Loan payment with payoff date

formulas NPER DATE PMT

6 replies to this topic

Newbie

• Members
• 8 posts

Posted 21 March 2014 - 05:40 AM

I'm trying to create a formula for a truck loan using =PMT but I can't seem to get it to work correctly.   I want the formula to use the Loan amount with the monthly interest rate (beginning of month), a regular payment, and and extra payment on the principal, and figure the number of months left on the loan and the payoff date.

I am fairly new to Excell and am studying the Excel lessons which are great and a little slow with this stuff but I created a Payoff schedule file which is working great except for these new enhancements I've just added.   Anybody has any ideas I'm sure listening......Thanks!!!

LENDER BEGINNING SCHEDULED DATE ACCELERATED   Months: PayOff Date: NAME BALANCE PAYMENT PAID PAYMENT       Centris CFU - Ford Truck              21,084.08  \$                388.92                    111.08           3701 August 16, 2322

Formula for months left on loan (Months) =PMT(2.53/12,6*12,21255.29,0,1)

Formula for figuring the PayOff Date                                           =DATE(2014,3-M7,16)

### #2 Jonathan

Jonathan

• 887 posts

Posted 21 March 2014 - 12:17 PM

The PMT function is used to calculate the payment amount for a loan, but it seems that in this case you know the payment amount and want to calculate the number of periods.

To do this, you need to use the NPER function instead of the PMT function.

The NPER function is almost exactly the same as the PMT function, but it extracts the number of periods instead of the payment amount.  You can also use the PV and RATE functions to calculate the present value and interest rate of a loan if you have the other values.

The PMT function is covered in the PDF and video lesson: Expert Skills Lesson 3-3: Use the formula palette and the PMT function.

Each of the four functions works in a very similar way, so I would recommend starting here and making sure that you completely understand the PMT function.

I have attached an example spreadsheet.  Your example data was a little hard to read, so I'm not sure if I have it exactly right, but hopefully this will offer a good starting point and example of how to use the NPER function:

The formula to return the number of months is: =NPER((E2/12),C2,-B2)

If you need more assistance, please feel free to attach your spreadsheet and I will take another look.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

Newbie

• Members
• 8 posts

Posted 22 March 2014 - 03:24 AM

Thank you Jonathan very much.......How do I upload my spreadsheet here, I don't see anything that allows me to do this.

### #4 Jonathan

Jonathan

• 887 posts

Posted 22 March 2014 - 10:09 AM

If you click More Reply Options below the box where you type your message, the Attach Files options will appear.

All you need to do then is select the file that you want to upload and click Attach This File.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

Newbie

• Members
• 8 posts

Posted 23 March 2014 - 01:47 AM

Thank you very much this helps a great deal.    Below is part of the spreadsheet I'm working on, here is a little history of it.     Ok, B5 is the amount owed, C5 is the payment with E5 as the extra payment on the principal, G5 is balance of loan after all payments are made, H5 of course is interest Rate (APR), I5 Months left to pay and J5 should read the actual date the loan will be paidoff.  (Note:  F5 will read the same as B5 unless there is a Date in D5 then C5 & E5 will be subtracted from B5 then I5 should change to current months left and J5 as payoff date calculated from the date in D5 or the current date shown in D2.....)

#### Attached Files

Newbie

• Members
• 8 posts

Posted 23 March 2014 - 03:28 AM

Wow...I got it figured out,   Lesson 3-11 in Expert Skills Section explained the =DATE(Year),(Month)+months left to pay,(Day) function and it works perfect

=DATE(YEAR(D7),MONTH(D7)+58,DAY(D7))   Note:   Using the DatePaid field plus the months left to payoff loan, just what I was looking for.   Absolutely outstanding!!!!  Thanks for your help..

Newbie

• Members
• 8 posts

Posted 23 March 2014 - 12:51 PM

Jonathan,  here is my PayOff Schedule spreadsheet working as intended and all columns now calculated.....Thanks a bunch!!!

### Also tagged with one or more of these keywords: formulas, NPER, DATE, PMT

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users