How to calculate a loan or mortgage payment
Work out a loan or mortgage payment in Grid by Zaweba with PMT, then split each payment into interest and capital with IPMT and PPMT.
Use the PMT function. In Grid by Zaweba, PMT takes the interest rate per period, the number of payments and the amount borrowed, and returns the regular payment. IPMT and PPMT then split any single payment into the interest part and the part that pays off the loan, which is all you need to build a full repayment schedule.
Three figures to start with
Three figures: the annual interest rate, the length of the loan, and the amount you're borrowing. Put each in its own labelled cell rather than typing them into the formula. That way you can change the rate or the term and see the payment update, which is the whole point of doing it in a spreadsheet.
Working out the payment
Most loans and mortgages are paid monthly, so convert the inputs to months. The rate per period is the annual rate divided by 12, and the number of periods is the number of years times 12. Then PMT with those two values and the loan amount gives the monthly payment.
The answer comes back as a negative number. That's the spreadsheet convention for money going out, since the loan amount is money coming in. If you'd rather see a positive figure, put a minus sign in front of the loan amount inside the formula, or in front of PMT itself.
Two optional arguments cover other cases: a future value, if the loan isn't meant to reach zero, and a type, for payments made at the start of each period rather than the end.
Splitting interest and capital
Each payment is partly interest and partly repayment, and the balance shifts over time. Early on most of the payment is interest; by the end most of it is capital. IPMT returns the interest part of a given payment number and PPMT returns the capital part. Added together they make the PMT figure.
To build a schedule, number the payments down a column from 1 to the total. Beside each, IPMT and PPMT with that payment number give the split, and a running balance column starts at the loan amount and reduces by the capital part each row. The fill handle makes this quick: write the first row, then double-click the handle to fill down.
Number formats keep it readable. A currency format with two decimal places on the money columns, and a percentage format on the rate cell, make the schedule easy to scan.
Answering other loan questions
The same family of financial functions works in other directions. NPER tells you how many payments it takes to clear a loan at a given payment. RATE works out the interest rate from the payment, term and amount. PV tells you how much you could borrow for the payment you can afford, and FV gives the value of regular savings after a number of periods.
If you know the payment you want and need to find, say, the deposit that gets you there, Goal Seek on the Data tab will work backwards for you: set the payment cell to your target by changing the loan amount cell.
Checking your figures
A schedule is a good check on itself. The balance in the last row should be zero, or very close, and the capital column should add up to the amount borrowed. If they don't, look at whether the rate and term are both in the same units.
Real lenders may calculate interest daily or add fees, so treat your own figures as a guide and compare them with the lender's statement. For planning and comparing offers, though, PMT and its partners in Grid by Zaweba's Financial family of 55 functions give you clear answers on your own PC, with nothing sent anywhere.
