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.

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.

Enter the Scenario A Amounts

On the CostPerYear worksheet, enter your current spending, by filling in the green cells for 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.

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.

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]],
IFERROR(INDEX(TimeAnnual,
MATCH([@[Time Unit]],TimeUnits,0)),0))
*[@Qty]

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