Excel, of course, has gazillions of mathematical formulas. Some you probably use regularly (like sum, count, etc.), but others you may not use quite as much. Some, of course you'll never use unless you are an engineer, mathematician, or scientist. One of the formulas that is fairly useful is payment formulas. This allows you to fill in various blanks and come up with a formula for payments. Say you want to buy a car for instance. If you know how much the interest rate is and the value of the car, you can plug in different term lengths (48 months, 60 months) to compare monthly payment options.

To set up a payment formula, you create cells for each of the formula parts, as needed.

Here are the parts of this formula explained:
 
Rate: This is the annual interest rate, divided by 12. (see note below)

Nper: Number of payments

NOTE: The number of payments should match the payment period of the interest rate. So for instance, I broke the interest rate down into a monthly component (divided by 12), and therefore, I want my number of payments to be the number of months, so I will put in 60 months instead of 5 years. If I were going to make one payment a year for the next five years, I would have the left the rate as an annual rate and put 5 in the number of payments. Make sense how those are linked?

PV: Present Value - This is the amount of money you are borrowing.

FV: Future Value - This is any expected value you might have left at the end of the loan. This would come into play with a lease or a mortgage balloon payment. If it is blank, Excel will assume it is zero.

Type of Payment: (Optional) This field is optional, but it uses a 1 to reflect if the payment is at the beginning of the period or a 0 (or blank) if the payment is at the end of the period. I never use it myself.

Payment: This is where the formula goes and it will show the payment (monthly in my example) that will result from that combination of interest rate, number of payments, and amount borrowed.

A2=rate
B2=number of payments
C2=present value
D2=future value
E2 would be type of payment (I left this out)
 

The formula in cell E2 or F2 would be:
=PMT(A2,B2,C2,D2)

Excel has additional features that greatly expand formulas to allow you to play with the numbers to see how they affect the outcome. Those include Goal Seeker and What If Analyses (Scenarios). I may write some future blog posts on how to use these functions, as they are pretty cool and useful. In the meantime, I encourage you to look them up if you are not familiar with them. You might be surprised at how helpful they are.