Convert Monthly, Quarterly, Semiannual, and Annual Expenses to Monthly Amounts in Excel
Question details
Convert expenses billed at monthly, quarterly, semiannual, or annual intervals into equivalent monthly amounts, optionally including the quantity of each item.

- Product
- Microsoft 365 Excel
- Device & OS
- not provided
- Scenario
- The user is building an expense table and wants a consistent monthly cost for each recurring expense.
- Observed behavior
- Each expense needs to be divided by the correct number of months for its payment frequency and multiplied by quantity when applicable.
Place the frequency, payment amount, and quantity in separate columns of an Excel table. Use consistent frequency labels, such as monthly, quarterly, semiannually, and yearly, because the formula must match them exactly.
Use a SWITCH formula to calculate each expense as a monthly amount
Use this method when your table contains a text-based payment frequency and you want Excel to choose the correct monthly divisor automatically.
A monthly payment is unchanged, while quarterly, semiannual, and yearly payments are divided by 3, 6, and 12 respectively. Multiply the result by Quantity when the listed amount is the cost per unit.
In Excel, create columns named Rate, Amount, and Quantity. Enter frequency labels such as monthly, quarterly, semiannually, or yearly in the Rate column.
Select the first cell in the Monthly Amount column and enter: =SWITCH([@Rate],"monthly",[@Amount],"quarterly",[@Amount]/3,"semiannually",[@Amount]/6,"yearly",[@Amount]/12,0)*[@Quantity]
Press Enter. If your data is formatted as an Excel table, Excel automatically fills the formula down the Monthly Amount column.
If a row returns 0, compare its Rate cell with the labels used in the formula. Correct spelling, spacing, or wording so the text matches exactly.

Use a divisor column for a simple frequency-based calculation
Use this method when you prefer to store the number of billing months in a separate column rather than using a text-matching formula.
Calculate Equivalent Monthly Expenses with WPS Spreadsheets
WPS Spreadsheets lets you organize recurring expenses in a table and use a SWITCH formula to convert monthly, quarterly, semiannual, and annual payments into a single monthly budget figure.
- 1. Create your expense columns: Open WPS Spreadsheets and add columns for Rate, Amount, Quantity, and Monthly Amount.
- 2. Enter payment frequencies: Type monthly, quarterly, semiannually, or yearly in the Rate column for each expense.
- 3. Insert the monthly conversion formula: Select the first Monthly Amount cell and enter =SWITCH([@Rate],"monthly",[@Amount],"quarterly",[@Amount]/3,"semiannually",[@Amount]/6,"yearly",[@Amount]/12,0)*[@Quantity].
- 4. Apply the formula to all expenses: Press Enter and drag the fill handle down the Monthly Amount column to calculate every row.

Frequently Asked Questions
What divisor should I use for quarterly, semiannual, and annual expenses?
Divide quarterly expenses by 3, semiannual expenses by 6, and annual or yearly expenses by 12 to calculate their equivalent monthly amounts.
How do I include quantity in an Excel monthly expense formula?
Multiply the monthly result by the Quantity value. For example, use =[@Amount]*[@Quantity]/[@Divisor], or add *[@Quantity] to the end of a SWITCH formula.
Why does my SWITCH formula return 0 for an expense?
The final 0 is the fallback result when Excel cannot find a matching Rate label. Check that the cell text exactly matches monthly, quarterly, semiannually, or yearly in the formula.
Can I use annual instead of yearly in the formula?
Yes. Replace "yearly" with "annual", or include both labels, such as "annual",[@Amount]/12,"yearly",[@Amount]/12.
Can this formula be used in an Excel table?
Yes. Structured references such as [@Rate], [@Amount], and [@Quantity] are designed for Excel tables and automatically adjust for each row.




