Pro-Rata Calculations in Excel: Daily Rates, Partial Months, Joiners & Leavers, Mid-Cycle Plan Changes & Splitting Amounts by Period

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.

• Turn Tidewell's monthly fees into daily rates three ways – actual days in the month, a flat 30-day basis and the annual fee ÷ 365 – and measure how far apart the conventions are for a 10-day stay.
• Charge contracts that start or end inside a month: count the days used with DAYS, EOMONTH and DAY, divide by the days in the month and round the prorated charge to cents.
• Pay Tidewell's November joiners and leavers for part of the month two ways – by calendar days and by working days with NETWORKDAYS – and compare what each method pays.
• Handle upgrades and a downgrade in the middle of a monthly billing cycle: count the cycle and the days left, credit the unused part of the old plan, charge the new plan for the same days and work out the net amount.
• Split five fictional service invoices across January to April: count each invoice's days, find the overlap of every invoice with every month in one copyable formula, turn the overlap into amounts and see which month carries the most.
• Spread a fictional annual licence that runs from August to July across its twelve months by days, group the months by fiscal year and compare the day-based split with equal twelfths.
• Split 1,000.00 over seven months by days, see that the rounded parts add up to 1,000.02, measure the difference and put it on the largest part so the split matches the total to the cent.
• Prorate six fictional contracts across July, August and September in one copyable formula, let the totals and the rows-vs-columns check confirm the grid, count the active contracts, name the biggest one and chart the monthly totals.