How to calculate monthly mortgage payment in Excel?
For most of modern people, to calculate monthly mortgage payment has become a common job. In this article, I introduce the trick to calculating monthly mortgage payment in Excel for you.
|To quickly add a specific number of days to a given date, Kutools for Excel's Add Years/Months/Days to Date utilities can give you a favor.|
Recommended Excel Productivity Tools
To calculate monthly mortgage payment, you need to list some information and data as below screenshot shown:
Then in the cell next to Payment per month ($), B5 for instance, enter this formula =PMT(B2/B4,B5,B1,0), press Enter key, the monthly mortgage payments has been displayed. See screenshot:
1. In the formula, B2 is the annual interest rate, B4 is the number of payments per year, B5 is the total payments months, B1 is the loan amount, and you can change them as you need.
2. If you want to calculate the total loancost, you can use this formula =B6*B5, B6 is the payment per month, B5 is the total number of payments months, you can change as you need. See screenshot: