How to Add a Fixed Amount on the 15th of Every Month in WPS Spreadsheet
Question details
The user needs a WPS Spreadsheet formula to calculate a running balance that increases by a fixed amount on the 15th of each month from a specified start date, with no fixed end date.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is building a recurring balance or payment tracker and wants the current balance to update automatically as months pass.
- Observed behavior
- The user needs a dynamic formula based on the start date, starting balance, and fixed monthly amount rather than manual monthly updates.
Open your WPS Spreadsheet workbook and identify three input cells: start date, starting balance, and fixed monthly amount. Make sure the start date is stored as a valid date value, because the DATEDIF formula requires a recognized date in WPS Spreadsheet.
Use DATEDIF to Add a Fixed Amount on the 15th Each Month
Best when the start date is the 15th of the month and you want a simple live formula that recalculates with TODAY().
The formula uses DATEDIF to count complete months between the start date in A2 and today's date. It then multiplies that number by the fixed monthly amount in C2 and adds the result to the starting balance in B2.
Click cell A2 in WPS Spreadsheet, type a valid start date such as 8/15/2023, and press Enter.
Click cell B2 and type the starting balance, for example 750. If needed, select the cell and apply a currency format from the Home tab.
Click cell C2 and type the amount added each month, for example 70.
Click cell D2, type =B2+DATEDIF(A2,TODAY(),"M")*C2, and press Enter.
The cell D2 now returns the starting balance plus the fixed amount for each complete month since the start date. The result updates when the workbook recalculates.
Create a Self-Updating Monthly Balance Formula in WPS Spreadsheet
Use WPS Spreadsheet to build a live balance tracker with a DATEDIF and TODAY formula. It adds the fixed amount on the 15th monthly schedule and automatically updates without a fixed end date.
- 1. Open WPS Spreadsheet: Launch WPS Office and click Spreadsheet to open a new blank workbook.
- 2. Enter input data: In cell A2 type the start date, in B2 the starting balance, and in C2 the fixed monthly amount.
- 3. Insert the calculation formula: Click cell D2, type =B2+DATEDIF(A2,TODAY(),"M")*C2, and press Enter.
- 4. Format the result: Select D2 and choose a currency or number format on the Home tab to display the balance clearly.
- 5. Save the workbook: Click Menu > Save As, choose a location, and save the file as .xlsx to keep it compatible with Excel and WPS Spreadsheet.
Frequently Asked Questions
Will the balance update automatically when a new month starts?
Yes. The formula uses TODAY(), so WPS Spreadsheet recalculates it when the workbook opens or when a recalculation occurs. Each time the 15th passes and a complete month is counted by DATEDIF, the balance increases by the value in C2.
What should I do if the increase must occur only after the 15th of each month?
If the start date in A2 is already the 15th, the DATEDIF formula handles this by counting complete months from that date. If A2 has a different day, insert a helper cell, enter the first payment date with =DATE(YEAR(A2),MONTH(A2)+(DAY(A2)>15),15), and then reference that helper cell instead of A2 in your DATEDIF calculation.
Why does my formula show #VALUE! or #NUM!?
This usually means cell A2 is not a real date or one of the input cells contains text. Click A2, open Format Cells, choose Date, and re-enter the start date as a date value. Make sure B2 and C2 are numbers.
Can I use a different start date or monthly amount later?
Yes. Change the start date in A2, the starting balance in B2, or the fixed amount in C2 at any time, and D2 recalculates immediately.
