How to Calculate Monthly Payments in Excel
Most people are painfully aware that buying a house or car typically obligates them to make monthly payments for the life of the loan. The amount of these payments relates to three important factors: the interest rate on the loan, the number of payment periods and the amount financed. Although the monthly payments don't change when using a fixed interest rate, the amount allocated to principal and interest varies over time. The monthly payment and the amounts allocated to principal and interest are calculable using a complicated formula, but the task is greatly simplified by using Excel 2013.
Understanding the Data
When you're shopping around for a loan, you'll likely be given the annual interest rate. However, when you calculate the monthly payments, use the monthly interest rate. To convert the interest rate, simply divide by 12. Similarly, most payment terms are expressed as years, so multiply the number of years times 12 to calculate the number of payment periods. The final data point is the amount actually financed, so if you bought a car for $30,000 and put $5,000 down, you're only financing $25,000.
Excel's PMT function calculates the monthly payment of the loan, inclusive of principal and interest. Enter "=PMT(interest,periods,amount)" without quotes (here and throughout) in an empty cell. Replace the variables with the appropriate figures or calculations. As an example, you could enter "=PMT(.0042,60,25000)" to calculate the payment on a $25,000 loan for five years with a 0.42 percent monthly interest rate. Alternatively, you could enter "=PMT(5%/12,5*12,30000-5000)" to perform all the data calculations together. You can enter interest in decimal or percentage format, but you need to include the percent sign for percentages. By default, the results appear in red font surrounded by parenthesis to illustrate an expense.
Excel's PPMT function calculates the amount of principal included in the payment. Therefore, you'll need the period number to calculate that specific payment's principal amount. Enter "=PPMT(interest,number,periods,amount)" in an empty cell, and replace the variables with appropriate figures or calculations. In this case, "number" is the integer period number, such as number 15 out of 60 required payments. If you plug in several numbers, you'll notice that as the period number increases, so does the amount allocated to the principal.
Excel's IPMT function calculates the amount allocated to interest in a given payment and uses the same format as PPMT. Enter "=IPMT(interest,number,periods,amount)" in an empty cell and replace the variables with the appropriate figures or calculations. As the payment number increases, the amount allocated to interest decreases. If you add the results from the PPMT and IPMT calculations for the same period number, you should get the same result as from the PMT function.
Differences With Real Estate
The PMT, PPMT and IPMT functions work with real estate as well, but these purchases typically have additional fees that must be addressed. As an example, closing costs might be rolled into the loan, so even if you fully finance a $150,000 home, the actual loan amount might slightly more than that. Ask your loan officer how much the actual loan amount should be and use that figure in the calculations. In addition, escrow fees and mortgage insurance is often added to the principal and interest payment, so you'll need to ask the loan officer how much extra is added to the principal and interest. Add these costs to the PMT result to calculate your actual monthly payment.