Spreadsheets: Excel: finding the monthly loan payment

Tutoring financial math, spreadsheets might be used. The tutor shows how to find the monthly payment on a loan using Excel.

In yesterday’s post I tell how to find the amount against the principal that a certain payment removes, using the ppmt function.

Perhaps more directly, a person might wonder how to calculate the monthly payment in the first place. Here’s how:

Example: Imagine a 25-year, $100,000 loan at 4% compounded monthly, with monthly payments as well. Find the monthly payment.

Solution: Using Excel, you’d enter

=pmt(4%/12, 300, 100000)

300 means the total number of payments: 12 per year for 25 years. The payment is assumed to be at month’s end.

Hopefully, the answer will come to -527.84, the negative meaning money paid out (as opposed to received).

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.

Leave a Reply