# Simple Pro-Rating Over Time

Some time ago, I wrote about a problem of pro-rating amounts over time, as costs (or revenues for that matter) and reporting dates seldom coincide. In hindsight, I may have over-complicated the fundamental problem by introducing full-time versus part-time and inflationary factors.

I write, “…I *may* have…” because whilst the article
addressed common salary cost issues, it obfuscated a more common problem of
just getting pro-rating right. Recently,
I have been reviewing various third party financial models where simple errors
have been made, and it’s prompted me to address the situation here today.

Consider the following simple dataset:

My plan is to allocate these amounts on a daily basis, displayed monthly, for the calendar year 2022. This is really quite simple, but it’s amazing how many times errors are made by the inexperienced.

One of the most common mistakes is actually calculating the number of days in the duration wrong. All too often, modellers will calculate this as

**End Date – Start
Date**

but this is *wrong* – plain and simple – since this
deducts the first day of the duration from the computation. For example, a cost that is incurred on 17
August 2022 would have a start and end date of said date. Therefore, according to the above formula,
the duration would be *zero* [0] days.
We must add one [1] to the subtraction:

**End Date – Start
Date + 1**

That’s better. It might be a good idea to add a dates check at this point too:

With
the table positioned as in the graphic *(above)*, the error check formula
in cell **J18 **is given by

**=IF(OR(I18-H18<0,COUNT(H18:I18)=1),1,)**

This checks for two things:

1.
The End Date occurs before the Start Date (**I18-H18<0**)

2.
If entered, *both *dates have been included
(**COUNT(H18:I18)=1**).

This ensures the integrity of the dates data entry.

Now I am in position to calculate the pro-rated amounts on a daily basis, summarised monthly, across the calendar year 2022:

This is one formula copied down and across the entire
block. For example, the formula in cell **M18** is given by

**=IF($I18-$H18>=0,MAX(MIN(M$7,$I18)-MAX(M$6,$H18)+1,0)*$G18/($I18-$H18+1),)**

The formula may look a little involved, but it is not that sophisticated:

**MIN(M$7,$I18)**calculates the earlier of the final day (**M$7**) in the period and the End Date (**$I18**), with anchoring included to ensure the formula may be copied down and across correctly

- Similarly,
**MAX(M$6,$H18)**calculates the later of the first day (**M$6**) in the period and the Start Date (**$H18**)

- Therefore,
**MIN(M$7,$I18)-MAX(M$6,$H18)+1**calculates the number of relevant days in the period. The adjustment of adding one [1] is to ensure the later of the first day in the period and the Start Date is included, as explained earlier

- Calculating the maximum of this
and zero,
**MAX(MIN(M$7,$I18)-MAX(M$6,$H18)+1,0)**, is computed just to ensure the End Date is later than or equal to the Start Date

- Multiplying by the scalar
**$G18/($I18-$H18+1)**pro-rates the amount (**$G18**) by the number of days in the period overall (**$I18-$H18+1**)

- Finally, this is all wrapped in
an
**IF**statement, where the formula checks that the End Date is greater than or equal to the Start Date (**$I18-$H18>=0**), else zero [0] is returned.

That is all that is required, but
I add a row total and a final check for completeness, *viz.*

The row total in column **L** simply sums the 12 months’
values. However, the ‘**Amt Check**’
(amount check) in cell **K18** *(say) *requires perhaps some
explanation:

**=IF(J18<>0,,(ROUND($L18-($G18*(MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)),Rounding_Accuracy)<>0)*1)**

**MAX($7:$7)** and **MIN($6:$6) **calculate the end
date and the first date of the calendar year (*i.e.* 31 December 2022 and
1 January 2022) respectively. Therefore,

**MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1**

calculates the total number of days the amounts should be
allocated to the year 2022, using similar logic to that detailed earlier. Since **$I18-$H18+1 **computes the total
number of days the amount should be spread across in total, the quotient

**MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)**

works out the proportion attributable to 2022 (which will be a value between 0% and 100%). Thus, the multiplication

**$G18*MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)**

is the total amount attributable to 2022, and

**$L18-($G18*MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1))**

represents the difference between the row total (**$L18**)
and this amount calculated. Since Excel
may generate rounding errors due to the fact its computations are rounded to so
many significant figures,

**(ROUND($L18-($G18*(MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)),Rounding_Accuracy)<>0)*1**

the **ROUND** function is employed to check that these
two values are equal to a given number of decimal places (denoted by the
constant **Rounding_Accuracy**, which is set at five [5] in the attached Excel file).

This formula only makes sense if the dates have been entered
correctly, hence the final **IF **condition that the other check in cell **J18** must not have been triggered:

**=IF(J18<>0,,(ROUND($L18-($G18*(MIN(MAX($7:$7),$I18)-MAX(MIN($6:$6),$H18)+1)/($I18-$H18+1)),Rounding_Accuracy)<>0)*1)**

Next month, we will be looking at the philosophy of
relativistic quantum mechanics and its impact upon Non-Euclidean n-dimensional
modular contingencies. *(Don’t wait up
for that one – Ed.)*