1. Payment Function
The first spreadsheet formula that we will cover in this course is arguably the most important - the payment function.
2. The payment function
The payment function allows both parties to determine how much the installments will be throughout the loan; assuming that the loan interest and payments remain constant throughout the term.
The function in your spreadsheet that you will use is the PMT() function, where PMT stands for "payment."
Now - the mathematical formula that is on the slide is not the one that is in the PMT() function in your spreadsheet program. This is actually the formula for deriving a payment.
What is worth pointing out is that there are only 3 variables in that formula; the principal, rate and payments.
3. The payment function in spreadsheets
Setting up a payment in a spreadsheet is much easier than using the raw formula. All you need are the 3 variables and the spreadsheet will handle all the math behind the scenes.
The Interest Rate is the nominal interest rate.
The Amortization Periods is the number of total amortization periods on the loan.
The final parameter, Present Value, refers to the initial balance of the loan.
The final 2 parameters, Future Value and End or Beginning are optional and will not be used. These are cases where we aren't amortizing the loan to zero, or in odd cases where loan payments are made at the beginning of a term instead of at the end of a term.
4. Payment formula example
On to our first real example of the formula in action!
We are dealing with a 15 year term loan, with a $100,000 principal, 6% per annum nominal interest rate and it pays payments on an annual basis.
The payment formula is entered into cell E3, and it starts with the interest rate from B3, then the number of periods from B4 and finally finishes with the principal, or present value, from cell B2.
The result should be minus 10,296.28. Now, normally we would want this to show positive, but the formula is telling us that making 10,296.28 payments annually at the end of the year will pay off the loan in 15 years.
5. Payment formula example with monthly payments
The last example that we have with the payment formula makes one change from the example that we saw on the last slide, and adds in a monthly payment frequency.
Since there is no parameter in the formula itself to change the payment frequency, we need to change either the inputs or the formula in order to reflect that the loan is being paid monthly instead of annually.
Changing the inputs does not make sense, as it is good practice to keep the interest rate in annual terms so that it can be compared against other loans.
So, we instead divide the interest rate by 12 in the formula in cell E3, to reflect the monthly nominal interest rate, and multiply the number of periods in the formula in cell E3 by 12.
This brings up a total monthly installment of $843.86.
6. Time to try out the PMT() function!
Now that we've gone over the payment function, it's your turn to practice using the payment function on some sample term loans.