Excel: Simple prorating over time

Some time ago, I wrote about a problem of prorating amounts over time, as costs (and revenues for that matter) and reporting dates seldom coincide. In hindsight, I may have overcomplicated 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 prorating right. I have been reviewing various third-party financial models where simple errors have been made, and it has prompted me to address the situation now.

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 inexperienced users. 

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 prorated 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) prorates 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), or 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), say in cell K18, 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 (ie, 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 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 example Excel file, Simple Prorating Over Time).

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)

Word to the wise

This really does sound like yet another of my articles where I state I shall write about a "simple" notion and then write a formulaic solution that takes several bulleted steps to explain. However, the concept is simple, and the formula is no longer than necessary to ensure that it calculates correctly.

This is a common problem in financial modelling, and it is well worth taking the time to understand it, in order to calculate and apply it as necessary in the real world.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling and Continuing Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Oliver Rowe at Oliver.Rowe@aicpa-cima.com.