Understanding the Components of a Mortgage Payment
Understanding how to calculate loan payments can be helpful when reviewing options for a home purchase or other large expense. Excel includes a built-in function, PMT, that makes it straightforward to estimate the monthly payment for a fixed interest mortgage or similar loan. This guide outlines the main parts of a mortgage payment, explains the PMT function, and shows how to use it with an example.
Components of a Mortgage Payment
- Loan Amount: The total you borrow from the lender. Interest is charged on this principal balance.
- Annual Interest Rate: The yearly percentage charged by the lender. This rate influences the size of each monthly payment.
- Loan Term: The agreed repayment period, such as 15, 20, or 30 years. The term length affects both the monthly payment and the total interest paid.
- Monthly Payment: The regular payment made until the loan is repaid. It usually covers principal and interest, and sometimes property taxes and insurance if these are included in escrow.
The PMT Function in Excel
Excel’s PMT function calculates the payment for a loan with fixed interest and equal installments. The syntax is:
=PMT(rate, nper, pv)
- rate: The interest rate per payment period (for monthly payments, divide the annual rate by 12).
- nper: The total number of payments (loan term × 12 months).
- pv: The present value, or the total loan amount.
Using this function provides a quick way to estimate monthly mortgage payments without manual calculations.
Step-by-Step Process to Calculate Mortgage Payments in Excel
Step 1: Open Excel and Create a New Worksheet
Open Microsoft Excel and start a new spreadsheet. This will be your workspace for entering data and calculating the mortgage payment.
Step 2: Set Up the Worksheet
In column A, enter the following labels:
- B4: Loan $
- B5: Annual Interest
- B6: Loan Term (Years)
- B7: Monthly PMT
Step 3: Enter Your Mortgage Data
In column C, type the values that match each label:
- C4: Loan amount (for example, $250,000)
- C5: Annual interest rate (for example, 4.5%)
- C6: Loan term in years (for example, 30)
- C7: Leave this blank for now. This will display the monthly payment after our calculation.
Step 4: Calculate the Monthly Payment
Click on cell C7 and enter the following formula:
=PMT(C5/12, C6*12, -C4)
How the formula works:
- C5/12: Converts the annual interest rate into a monthly rate.
- C6*12: Calculates the total number of monthly payments (loan term × 12).
- -C4: The loan amount is negative to represent money paid out.
Step 5: View the Monthly Payment
Press Enter. The monthly mortgage payment will now appear in cell C7.
Understanding the Results
The calculated monthly payment of approximately $1,267 indicates the monthly payment amount for the duration of the loan.
Conclusion
Calculating mortgage payments in Excel with the PMT function is a straightforward way to see what your monthly payment will be for fixed interest rate loans. By setting up the inputs clearly and applying the formula, you can estimate loan costs and compare options more easily. This approach can be helpful for planning and evaluating different financing choices.