Excel: finding loan payment at the beginning of the month

Tutoring financial math, loan payments are calculated. The tutor mentions how Excel handles payments at period beginning, as opposed to period end.

In my post of October 5, I show how to find the monthly payment of a loan using Excel. If not stated, the payment is assumed to be at month-end.

What if a loan has the payment at the beginning of the month?

Example:

Calculate the monthly payment on a 25-year, $100,000 loan at 4% compounded monthly, if the payment is made at the beginning of each month.

Solution:

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

Hopefully you receive the answer -526.08. (Negative means paid out as opposed to received.)

The fourth and fifth arguments aren’t present in my earlier post. The fourth is for the loan’s future value (assumed 0 if not specified). The fifth is for loan type: 0 for month-end payment, but 1 for beginning of month. The fifth is assumed 0 (month-end) unless given.

The parallel situation with annuities is annuity due, where payments are received at month beginning. More about that here.

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

Leave a Reply