Chat with us, powered by LiveChat

# Computing the Periodic Payment for a Loan

## Excel Financial Function

Computing the periodic payment is probably the most used Excel financial function simply because it is something everyone uses.
Let’s walk through an example.
You want to purchase a car and finance \$24,000 over 24 months at an interest rate of 4%.   So, the question is –What is the monthly payment?
Here we have a value today (Pv) of \$24,000.  We want a monthly payment for 24 months (Nper) at an annual interest rate of 4%.
First, we need to change the annual interest rate to a monthly one by dividing 4% by 12 = .0033333  Let’s reflect this in our worksheet.
Matching the rate to the payment frequency is important.  If you want to compute a monthly payment and have an annual rate, the rate must be divided by 12.  (So many people forget this little fact which is why I am really emphasizing it here.) Click on cell B5, then click on the Fx button at the top left of the formulas tab.
At the top of the window that opens, select the category “Financial, then find and select PMT at the bottom.

Click O K.

Fill in the cell references for Rate, Nper and PV as shown below: Rate is B3, Nper is B2 and PV is B1. Click OK.
The monthly payment is \$1,042.20.  Remember, since this was a monthly payment, the annual interest rate was adjusted by dividing it by 12.Also, please note that the answer is negative as it is considered an outflow. Yes- you can tell the programmer was not a CPA 🙂  The easiest way to fix this is to go up to the formula bar and put a negative sign in front of PMT so that it looks like =-PMT(B3,B2,B1)

Let’s  double-check our work by making an amortization schedule.
An amortization schedule starts with the initial loan amount and progresses it forward using the monthly interest rate and computed monthly payment.

Start with the beginning balance of \$24,000.  Add interest to this amount at 4% divided by 12 (=B1*.04/12).  Reflect the payment as a negative amount and sum across the row (B4:D4) for the ending balance. The new beginning balance in B5 should be the result in E4 (the formula in B5 will be =E4).  Copy these down 23 rows (for a total of 23 periods).  The ending balance should be very close to zero, but may not be exactly zero due to rounding (the use of only two decimal points in the payment). This proves that the monthly payment for a loan of \$24,000 over 24 months is correct and provides us with the interest and remaining balance at the end of each payment period.

Mortgages work identically except that the number of periods is generally longer.

### One Response

1. Chuck Sarahan says:

How about modifying this post to include extra payments(both annual and one time) and a downloadable mortgage amortization file.

I would also like to see a post on simple interest amortization loans. That is more interesting than the compound interest loan in that the date of payments will impact the interest calc than it will with a compound interest loan.

• I wanted to compliment you on the Accounting Fraud – Recent Case Studies (AUCASEMC) course I just took. The ebook was fantastic! Concise, easy to understand, very well structured, and very interesting. Exactly the way I like it. Thank you for putting it together. I feel it took me enough time to be worth 2 CPEs, but it was fantastic nonetheless. Thank you.

- Mark R.

• “Thank you for a high quality CPE resource. As a CPA in a small nonprofit, I find your library quite helpful to meet my needs. I appreciate the service.”-

- Cheryl

• Thank you for providing very informative CPEs. I have enjoyed your courses for years now. Yours is truly my go-to CPE sources.

- Lily