International Assignments by the Numbers: Cost Projections, Allowances, Cost-of-Living Indices, Split Pay & Assignment Budgets

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Measure an assignment that crosses three calendar years: total days with DAYS, days falling into each year with MIN/MAX date overlaps, the calendar months it touches, and an annual allowance prorated by day for each budget year.
• Find the spendable share of a salary in a band table with an approximate-match lookup, build a host-location index from a weighted spending basket and turn the index difference into an annual cost-of-living allowance that never goes negative.
• Look up a monthly housing amount by location and band with a two-way lookup, add transport and utilities, apply a company housing cap with MIN and turn it all into an annual cost per assignee.
• Cost the one-off items of an assignment with SUMPRODUCT, count the home-leave trips a policy grants for completed years with INT, and spread one-off and trip costs over the assignment months.
• Split a salary into a home-currency and a host-currency payroll, convert host-currency allowances back for the budget and measure how much a move of the exchange rate adds to the cost.
• Work through a deliberately simplified, fictional tax-equalisation step: the hypothetical home tax the assignee keeps paying, the host tax before and after gross-up, and the net tax cost the company carries.
• Add salary, allowances, tax cost and continued home benefits into an annual assignment cost, project it over the full term with the one-off costs, express it as a multiple of base salary and compare it with hiring locally.
• Track actual cost postings against the assignment budget: actual cost per type up to a cut-off date with SUMIFS, remaining budget, the share of the year's budget used, and a column chart of budget against actual.