Excel Annual Cost Calculator

Enter your current expenses in Scenario A, and revised expenses in Scenario B. Formulas calculate annual totals and differences

Using the Cost Calculator

The sample workbook calculates the difference in cost for each item, and the overall difference. go to top

cost per year differences

To see how the annual cost calculator works, you can watch this short video. The written instructions are below the video.

Enter the 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.

time units

go to top

Enter the Scenario A Amounts

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

scenario a

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.

budget variance forecast

In the Max Units column, you can adjust the maximum occurrences for an item. 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 weeks.

Enter the 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.

The difference between the two scenarios is shown at the far right.

scenario b and difference

How It Works

To see how the formulas are set up, you can watch this short video. The written explanation is 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]],
MATCH([@[Time Unit]],TimeUnits,0)),0))

annual quantity calculation

The Annual Cost is the Annual Qty multiplied by the Cost. go to top

=[@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


About Debra


Last updated: July 12, 2021 7:04 PM