Home > Formulas > Lookup

Excel Annual Cost Calculator

In this Microsoft Excel cost calculator, enter your current daily, weekly or monthly expenses, to see annual totals. To calculate potential savings, enter revised expenses in Scenario B. Formulas calculate annual totals and differences. See how much you can save in annuals costs, after a few cost cuts. Or, see what the overall impact will be, if some of your costs increase.

Using the Cost Calculator

Where can you cut back in your daily, weekly or monthly spending? Even a small reduction in a daily expense can add up to big savings, in the annual total cost for that item.

Or, what expenses will be increasing over the coming months? Some cost increases are out of your control

In the sample workbook that you can download on this page, there are formulas that calculate the difference in annual cost for each item, and the overall annual cost difference.

cost per year differences

Video: Using Annual Cost Calculator

To see how the annual cost calculator works, you can watch this short video. The written instructions are below the video, and there is an embedded workbook in the next section, so you can try out the calculator.

Try Annual Cost Calculator

If your web browser supports it, try the Annual Cost Calculator in this embedded Excel workbook. How much can you save each year, if you make small changes to your current spending?

  • Green cells are unlocked
    • you can change the values in those cells, to adust the results in Scenario A and Scenario B
  • Click icons at the bottom of the embedded workbook to:
    • download a copy of the workbook, to use in Excel for the Desktop
    • open a full-sized version of the workbook, in Excel for the Web

Enter Time Units

In this template, there is a Lists sheet, where the time units are entered. You can adjust the number of work days per week, and the number of work weeks per year, to match your schedule.

  • B2:B8 is a named range -- TimeUnits
  • C2:C8 is a named range -- TimeAnnual

These named ranges are used as a lookup for the cost per year calculations.

So, when you select a time unit on the CostPerYear sheet, this lookup table returns the number of periods for the selected time unit.

For example, select Weekly, and the cost is multiplied by 52 - the number of weeks in a year.

time units

go to top

Enter Scenario A Amounts

On the CostPerYear worksheet, enter your current spending, by filling in the green cells for Scenario A.

This screen shot shows the entries in the sample file, and you can edit the list, to include your own current expenses.

For example, what are your current home maintenance costs every month? From that amount, the workbook can calculate your annual maintenance cost.

Note: You don't need to enter all of your current expenses. Just input the expenses that you want to reduce, or amounts that you think will increase, for any reason.

At the top of the sheet, in cell H3, you can see the total of your annualized costs.

scenario a

Select Time Units

There is a drop down list of time units in column E, and you can select an item from that list. The list is based on the TimeUnits range on the Lists sheet.

The drop down lists are created with data validation, based on the list of Time Units on the Lists sheet, in the named range, TimeUnits

budget variance forecast

Set a Maximum

In the Max Units column, you can adjust the maximum occurrences for an item.

For example, in the screen shot above, Golf is entered as a Weekly item, and the Max Units is set at 25, because the expense occurs only during the summer months of the year.

Enter Scenario B Amounts

Next, enter your revised spending plans in the green cells for Scenario B. The item name is linked to Scenario A, and you can enter a different cost, quantity, time unit, or maximum units.

For example, maybe you found a better option for your cell phone plan, and that will give you a lower monthly cost.

Or, did you find a new auto service centre, and your car's annual maintenance expense will be lower? What about fuel costs, for your home or car, or the cost of electricity? Will they be increasing or decreasing?

The difference between the two scenarios, for each line item, is shown at the far right.

The total difference is calculated at the top of the worksheet, in cell

scenario b and difference

Cost Calculator Formulas

To see how the cost calculator formulas are set up, you can watch this short video. There are written notes below the video

To calculate the annual quantity, a formula checks the Max Units column, and uses that number, if one is entered. Otherwise, it looks up a number from the time units table.

Then, that number is multiplied by the quantity.

Because the scenarios are in named Excel tables, there are field names instead of cell references. The INDEX and MATCH functions are used for the lookup.

Here is the formula for the Annual Qty column:

  • =IF([@[Max Units]]<>"",[@[Max Units]], IFERROR(INDEX(TimeAnnual, MATCH([@[Time Unit]],TimeUnits,0)),0)) *[@Qty]

annual quantity calculation

In column H, the Annual Cost is the Annual Qty multiplied by the Cost.

  • =[@Cost]*[@[Annual Qty]]

Get the Sample File

To get the zipped sample file, in xlsx file format, click here: Excel Annual Cost Calculator. The CostPerYear sheet is protected, with no password. There are no macros in the file.

More Tutorials

Named Excel tables

INDEX and MATCH functions

Create a drop down list

Names - Naming Ranges

 

 

Last updated: October 2, 2022 3:50 PM