Tiered Commissions, Income Taxes, and other "Tiered Rate" Calculations in PowerPivot

mentioned in my LAST POST that I would return with a variation on that commission calculation. Two months later and very much overdue, here it is! Apologies to those of you who have been waiting on me for this.

I left off with a calculation that applied a commission rate based on a rates table. That rate was applied to the total value.

But what about a scheme where one rate applies to the first N dollars (or pounds, euros, etc.), then another rate applies for the next M dollars, and so on? Income taxes are calculated this way, for instance. And being able to solve this sort of thing in Excel very efficiently (using PowerPivot) will be generally quite useful.

In this post, I'll explain how to do this in a model that I refer to as Base Plus. And while I will discuss this purely in the context of tiered commissions, keep in mind that it applies to taxes and many other things as well.