Calculate Monthly Loan Payments in Excel with WPS Office Spreadsheets
Question details
The user needs a formula to count how many monthly car-loan payments have occurred between a first payment date and a current date, including the current payment month.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user is tracking a car loan with monthly payments and wants to know how many payments have occurred as of the current month.
- Observed behavior
- For a first payment on February 4 and a current date in June, the expected count is five when the current month is included.
Confirm that the first payment date and current date are stored as actual date values rather than text, and decide whether the current payment month should be counted as a paid month.
Use the YEAR and MONTH formula to count payments including the current month
Best when monthly payments are counted by calendar month and the current month should be included, such as counting February through June as five payments.
This formula compares only years and months, so the day of the month does not affect the result. The +1 at the end includes the first payment month in the count.
Put the first payment date, such as 2/4, in a cell like A2, and the current date in another cell like B2. Make sure both cells are formatted as Date.
In an empty cell, enter =((YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2))+1 and press Enter.
WPS Spreadsheets returns the number of monthly payments including the current month. Adjust A2 and B2 to match your actual date cells.
Adapt the formula when the current month has not yet been paid
Use this variation when you only want to count completed payment months before the current month.
Calculate Monthly Loan Payments with WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-compatible date functions, so you can use the YEAR and MONTH formula to track loan payments without switching software. Open the file, enter the formula, and WPS calculates the count instantly.
- 1. Open your loan tracker in WPS Office Spreadsheets: Launch WPS Office and open the existing .xlsx loan file, or create a new spreadsheet and enter First Payment Date and Current Date in separate cells.
- 2. Enter the monthly payment count formula: Select the result cell, type =((YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2))+1, and press Enter, replacing A2 and B2 with your date cells.
- 3. Apply the formula to other loans or dates: Drag the fill handle or copy the formula to calculate counts for additional loan records. WPS preserves the Excel-compatible formula behavior.
Frequently Asked Questions
Does the formula include the first payment month?
Yes. The +1 at the end includes the first payment month. For a first payment on February 4 and a current date in June, the formula returns 5. If you do not want to include the first payment month, remove the +1.
What if my payment dates have different day values?
The YEAR/MONTH formula compares only year and month, so the day of the month does not change the result. This makes it suitable for counting monthly payments by calendar month.
How do I adjust the formula if the current month is not paid yet?
Remove the +1: =((YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)). This counts completed calendar months only.
Why does the formula return #VALUE! in WPS Spreadsheets?
The date cells may be stored as text. Select the cell, set the format to Date, re-enter the date, or use a formula such as DATE(year,month,day) before applying the payment count formula.
Can I open this Excel loan tracker in WPS Office and use the same formula?
Yes. WPS Office Spreadsheets supports Excel-compatible formulas and .xlsx files, so you can open the workbook, enter the formula, and share it with Excel users.
