Calculation Issues

Convert Monthly, Quarterly, Semiannual, and Annual Expenses to Monthly Amounts in Excel

Khadija KhanKhadija Khan Nov 12, 2026 868 views

Question details

Convert expenses billed at monthly, quarterly, semiannual, or annual intervals into equivalent monthly amounts, optionally including the quantity of each item.

Convert Recurring Expenses to Monthly Amounts in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the expense table columns

In Excel, create columns named Rate, Amount, and Quantity. Enter frequency labels such as monthly, quarterly, semiannually, or yearly in the Rate column.

2
Add a Monthly Amount 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]

3
Fill the formula through the table

Press Enter. If your data is formatted as an Excel table, Excel automatically fills the formula down the Monthly Amount column.

4
Check unmatched frequency labels

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 SWITCH formula to calculate each expense as a monthly amount
Monthly values calculated consistently: The formula returns an equivalent monthly cost for each recognized payment frequency and includes the item quantity.
Calculate recurring expenses in WPS Spreadsheets

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. 1. Create your expense columns: Open WPS Spreadsheets and add columns for Rate, Amount, Quantity, and Monthly Amount.
  2. 2. Enter payment frequencies: Type monthly, quarterly, semiannually, or yearly in the Rate column for each expense.
  3. 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. 4. Apply the formula to all expenses: Press Enter and drag the fill handle down the Monthly Amount column to calculate every row.
Use SWITCH formulas to apply the correct monthly divisor by payment frequency.Multiply recurring costs by quantity for accurate monthly expense planning.Open and edit Microsoft Excel .xlsx and .xls expense workbooks.Use a familiar spreadsheet interface for a smooth transition from Excel.
QA img-9

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.