Resource Costing Guide: Managing Billing Rates Across Projects

In professional services, your cost is people multiplied by time multiplied by rate. It sounds simple until you start dealing with reality.

Someone gets promoted mid-engagement and their rate changes. The same person bills at different rates on different contracts because of client-specific negotiations. You need to calculate what a project actually cost six months ago—using the rate that was active then, not today's rate. Oh, and the person who built your tracking spreadsheet left the company, and nobody's quite sure how the formulas work anymore.

This guide covers how to handle resource costing properly: the concepts, the common pitfalls, and the infrastructure you need to get accurate numbers without manual heroics.

How Billing Rates Work in Professional Services

Before diving into management challenges, let's clarify terminology that often gets confused.

Billing Rate (Client Rate)

The hourly rate charged to the client for a specific resource. This is what appears on invoices. "Sarah's billing rate is $200/hour on the Alpha engagement."

Billing rates are typically:

• Set by role/band: Junior Consultant = $150, Senior Consultant = $195, Principal = $250
• Negotiated per contract: Client A pays standard rates, Client B negotiated a 10% discount
• Subject to escalation: Annual rate increases, often tied to contract anniversaries

Cost Rate (Internal Rate)

The fully loaded internal cost of one hour of that person's time. This is what the engagement costs your firm, regardless of what you bill the client.

Cost rate includes:

• Base salary: Annual salary ÷ available hours/year
• Benefits loading: Health insurance, retirement, payroll taxes (typically 25-35% of salary)
• Overhead allocation: Office space, tools, management, administrative support

If a senior consultant earns $140,000/year with 32% benefits loading ($44,800), and you have $15,000 in allocated overhead, their total loaded cost is $199,800. With 1,880 available hours, that's approximately $106/hour cost rate.

Bill Rate vs. Cost Rate

The difference between these two numbers is your margin per hour. If Sarah bills at $200/hour and costs $106/hour, you make $94/hour when she works. Her margin is 47%.

This is why tracking both rates—and tracking them accurately—matters. Revenue without accurate cost data gives you incomplete profitability.

Rate Bands

Most firms organize rates by role or band rather than by individual. A rate card might look like:

When someone changes bands (promotion), their rates change. When annual escalations occur, everyone's rates shift.

The Rate Change Problem

Here's where most tracking systems fall apart: rates aren't static, but most systems treat them as if they are.

The Spreadsheet Failure Mode

Consider a typical Excel budget tracker. Sarah's rate lives in cell B4 of the Rate Card tab. Every cost calculation uses a formula like:

=Hours * VLOOKUP("Sarah", RateCard!A:C, 3, FALSE)

Sarah's rate is $180/hour in January through March. She gets promoted in April; you update cell B4 to $200.

Here's what happens: every single cost calculation that references B4 now uses $200—including January, February, and March. Your historical costs just increased by 11% retroactively. January now shows higher cost than it actually was.

If you're comparing this year's profitability to last year's, or analyzing cost trends, you're looking at distorted data. The spreadsheet doesn't know that January should use $180 and April should use $200.

Why This Matters Financially

On a 12-month engagement with a 5-person team where 2 people get mid-year rate changes:

• Person 1: Rate change from $165 to $185 (+$20/hr × 300 hours pre-change = $6,000 cost distortion)
• Person 2: Rate change from $200 to $225 (+$25/hr × 240 hours pre-change = $6,000 cost distortion)

Your historical cost reports are now overstated by $12,000. Your trend analysis shows costs increased when they didn't—the rate just changed.

For variance analysis, this is fatal. If you saved a baseline budget in January and you're comparing to actuals in June, and the baseline now uses June rates for January's planned hours, the variance is meaningless.

How to Calculate Resource Cost Correctly

The solution is rate effective dates. Instead of one rate per person, you maintain a rate history: each rate has a start date (and implicitly, an end date when the next rate begins).

Effective Rate Lookup

For any cost calculation, you need: who (the person), what (the project), and when (the date of the hours being costed).

The lookup logic:

1. Find all rates for this person
2. Filter to rates that were active on the date in question
3. Use that rate for the calculation

A person's rate history might look like:

For hours worked in March 2025, use $165. For hours in May 2025, use $185. For hours in February 2026, use $200.

The Calculation

Cost = Hours × Effective_Rate_At_Time

For Sarah working 40 hours in March 2025:

• Lookup: What was Sarah's rate on March 15, 2025?
• Answer: $165 (the $165 rate was effective 2025-01-01, before the April change)
• Cost: 40 × $165 = $6,600

For Sarah working 40 hours in May 2025:

• Lookup: What was Sarah's rate on May 15, 2025?
• Answer: $185 (the $185 rate became effective 2025-04-01)
• Cost: 40 × $185 = $7,400

Implementation in Spreadsheets

It's possible to do this in Excel, but it's painful. You need:

• A rate history table with person, effective date, and rate
• MAXIFS or INDEX/MATCH formulas to find the most recent rate as of a given date
• These formulas in every cost calculation

=SUMPRODUCT(MAX((RateHistory[Person]="Sarah")*(RateHistory[EffectiveDate]<=A2)*RateHistory[Rate]))

This formula finds the maximum rate where the person matches and the effective date is less than or equal to the date in A2. It works, but it's complex, slow on large datasets, and breaks if someone adds rows incorrectly.

This is exactly why purpose-built tools exist.

Per-Project Rate Overrides

Beyond rate changes over time, you often have rate variations by project.

When Overrides Apply

• Negotiated discounts: Client B negotiated 15% off standard rates
• Contract-specific rates: Government contracts often have pre-defined rate ceilings
• Promotional pricing: You offered reduced rates to win the deal
• Legacy agreements: An old contract locked in rates that are now below standard

Resolution Order

When calculating cost, the system needs a clear hierarchy:

1. Check for project-specific rate: Does this person have a custom rate for this specific project?
2. If yes, use it: The project rate overrides the standard rate
3. If no, use standard rate: Fall back to the person's current standard rate (with effective date logic)

Example

Jane's standard billing rate is $200/hour. However:

• On Project Alpha, she bills at $175/hour (client negotiated a discount)
• On Project Beta, she uses standard rates ($200/hour)
• On Project Gamma, there's a special rate of $185/hour

Jane works 20 hours on each project in April:

• Alpha: 20 × $175 = $3,500
• Beta: 20 × $200 = $4,000
• Gamma: 20 × $185 = $3,700
• Total: $11,200

If you used her standard rate for everything, you'd calculate $12,000 and overstate revenue. The project-level overrides matter.

The Staffing Agency Bill Rate Calculation

If you're a staffing or staff augmentation firm, there's an additional calculation: your bill rate to the client is derived from your cost rate plus a markup.

The Formula

Bill Rate = Cost Rate × Markup Multiplier

Or equivalently:

Bill Rate = Cost Rate / (1 - Target Margin)

Typical Markups

Markups vary by skill level, market conditions, and contract type:

Calculating Margin Per Resource

Once you have both rates, margin is straightforward:

Margin = (Bill Rate - Cost Rate) / Bill Rate × 100

Example:

• Cost rate: $85/hour (what you pay the contractor plus burden)
• Markup: 1.5x
• Bill rate: $85 × 1.5 = $127.50/hour
• Margin: ($127.50 - $85) / $127.50 = 33.3%

Quick Calculator

For a target margin approach:

Formula: Bill Rate = Cost / (1 - Margin%)

How to Manage Rates at Scale

The Spreadsheet Approach

You can manage rates in Excel, but it requires discipline:

Rate Card Tab:

• Columns: Person, Band, Standard Rate, Effective Date
• New row for each rate change (don't overwrite, add)
• Sort by person, then effective date

Project Override Tab:

• Columns: Person, Project, Override Rate, Effective Date
• Only populate where overrides exist

Cost Formulas:

• First check project override tab
• If no match, lookup standard rate using effective date logic
• This gets complex and fragile

The Database Approach

Modern tools store rate history as proper data:

Team Member Table:

• Person ID, Name, Current Band, Hire Date

Rate History Table:

• Person ID, Rate, Effective From, Effective To (null if current)

Project Rate Override Table:

• Person ID, Project ID, Override Rate, Effective From, Effective To

Queries join these tables and filter by date to get the correct rate. This is what MyProjectBudget does under the hood.

How MyProjectBudget Handles It

When you set a rate in MyProjectBudget:

1. You specify an effective date: "This rate starts April 1"
2. The old rate is preserved: Historical hours still use the old rate
3. New hours use new rate: April onwards calculates at the new rate
4. No manual formula updates: The system handles the date logic

For project overrides:

1. Set a project-specific rate: "On Project Alpha, this person bills at $175"
2. System checks override first: Any hours on Alpha use $175
3. Fallback to standard: Hours on other projects use the standard rate

Building Your Rate Card

Whether you use spreadsheets or software, you need a documented rate card.

Template Structure

Review Cadence

Quarterly rate review: Are your rates competitive? Are cost rates accurate based on actual loaded costs?

Annual rate escalation: Most firms increase rates annually, often 3-5%. Plan for this in long-term contracts.

Promotion checkpoints: When someone promotes, their rate changes. Have a clear process for updating the system.

Communicating Rate Changes

To clients: For T&M contracts, rate changes usually require notice per contract terms (often 30-90 days). Build this into your escalation process.

Internally: When rates change, ensure:

• Finance knows for billing purposes
• Project managers know for forecasting
• The tracking system is updated with effective dates

Resource costing isn't conceptually difficult—it's operationally challenging. The math is multiplication. The hard part is maintaining accurate rate histories, handling project-specific overrides, and ensuring historical costs don't get distorted when rates change.

The firms that track profitability accurately aren't doing heroic spreadsheet maintenance. They have systems that handle rate complexity automatically, so their PMs can focus on managing projects instead of debugging formulas.

Ready to replace your spreadsheet? MyProjectBudget gives you forecasts, actuals, and profitability in real time—across every project and billing code. Free to start, no credit card required.

Try MyProjectBudget Free