In Excel, you can calculate the monthly payments required to pay off a fully amortized loan using the PMT function.
- [Instructor] 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, payoff the principal, and all accrued interest. In Excel, you can calculate the monthly payments required to payoff a fully amortized loan using the PMT function. I'll demonstrate how to use this function. My sample file is the Payment Excel Workbook. You can find that in the Chapter One folder, of the exercise files archive.
The PMT function requires three arguments, and there are two more that are optional. The first required argument is the interest rate, which is 4.75% in this case, that's the annual rate. So, if you're making payments on a monthly basis, we'll need to divide that by 12, the number of months in a year. Next is the number of payments that you'll make, called the number of periods, in this case it's 360, which assumes a 30 year loan with payments made each of the 12 months of the year. Present value is the amount that you borrow, the future value is the amount left over when you're done making these payments.
So, if you wanted to underpay by say $50,000, and make a balloon payment of that size at the end of the loan, then you could calculate your payments based on that criteria. Future value for a fully amortized loan is always going to be zero. Type, indicates whether interest is compounded at the end of a period, or the beginning of a period. The most common is for interest to be compounded at the end of a period, which is type zero. Type one is at the begging of a period.
If you leave that argument blank, then Excel assumes zero. So, let's create our formula in cell B9 I'll type an equal sign, and then the function PMT, followed by a left parenthesis. Then we have the rate, which is in B3, and again we're assuming monthly payments, so I'll divide that by 12. The number of periods, the number of payments we're making is in cell B4, that's 360 for a conventional 30 year loan.
Comma, the present value, which is the amount we're borrowing is in B5, then a comma, and even though they're optional I'll include them. We have the future value of zero in B6, then comma. The type, we're working with the end of a period, which is the usual, so we have that in B7, which is zero. With all those arguments in place, I can type a right parenthesis to close out the list, and enter, and we see a payment of $2,347.41 every month.
And again, that will take care of all interest, and principal over 360 payments. The values display as a negative number that is in red and parenthesis, because that money is going out of our account. If you're more comfortable seeing the value as a positive number, you can just multiply the result of the formula by negative one.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart