Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most loans, whether between businesses or made by businesses to individual borrowers, are fully amortized. Fully amortized means that the monthly payments made over the term of the loan pay off the principal and all accrued interest. You can calculate the monthly payments required to pay off a fully amortized loan using the PMT or Payment function. The PMT function has five arguments. The first three arguments, rate, nper, and pv, are all required. Rate is the annual percentage rate divided by the number of payments made each year.
That's usually 12, representing the 12 months. nper is the number of payments you will make on the loan, and present value is the loan principal. The other two optional arguments are fv and type. fv is the future value of the loan, which is 0 in a fully amortized loan, because you pay off the entire amount, and type is zero if your payment is due at the end of a period, that is in arrears or at the beginning of the period, in advance. Most loans are made in arrears and result in a zero balance at the end of the loan's term.
So you will usually leave the fv and type arguments out of the formula. So just create the formula here. We have payment, our rate is in cell B5, which is 7% divided by 12, again, the number of months in a year, and interest we assume will be compounded monthly. nper is the number of payments you will make on the loan. So that is the value of B7, multiplied by 12, again for months in the year, the number of payments you will make in a year, and the pv or present value of the loan is the principal and that is in cell B3.
So you can see that on a $30,000,000 loan at 7% rate, and paid off monthly over 12 years, you will be making monthly payments of $308,514. As always, make sure you setup your worksheet, so the inputs are on a separate and easily identified area, and use cell references to create your formula. It's important to remember that the PMT function only calculates the monthly payment to cover principal and interest. Many loans have other charges included, such as property taxes, and perhaps private mortgage insurance for home loans.
So be sure to include those other fees and charges when you calculate your total loan payment.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90643 Viewers
80 Video lessons · 137957 Viewers
59 Video lessons · 56720 Viewers
52 Video lessons · 70329 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.