Pro-Rata Calculations in Excel: Daily Rates, Partial Months, Joiners & Leavers, Mid-Cycle Plan Changes & Splitting Amounts by Period
Charge, pay and split fairly for part of a period – daily rates under different conventions, partial first and last months, salaries for joiners and leavers, mid-cycle plan changes and annual amounts spread across months and years so every part adds back to the total – with every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
This course works through pro-rata calculations for a fictional co-working company, with every convention clearly marked as illustrative. You compare daily rates on actual days, a 30-day basis and annual ÷ 365, charge partial first and last months with DAYS, EOMONTH and DAY, pay joiners and leavers by calendar days and by working days with NETWORKDAYS, credit and charge mid-cycle plan changes, split invoice periods across months with overlap days, spread an annual licence across months and fiscal years with SUMIFS, make rounded parts add up to the total and build a quarterly proration dashboard with checks and a chart.
Lessons
- Daily Rates Three Ways: Actual Days, a 30-Day Basis & Annual ÷ 365 Pro
- Partial First and Last Months: Days Used, Days in the Month & the Prorated Charge Pro
- Joiners and Leavers: Calendar-Day vs Working-Day Proration of a Monthly Salary Pro
- Mid-Cycle Plan Changes: Credit for the Old Plan, Charge for the New One & the Net Amount Pro
- Overlap Days Between Two Periods: Splitting an Invoice Period Across Months Pro
- Spreading Annual Amounts: a Licence Across Months and Fiscal Years Pro
- Rounding That Adds Up: Rounded Parts vs the Total & Where the Difference Goes Pro
- Proration Dashboard: Many Contracts, Monthly Totals, Checks & a Chart Pro