Thread: Rounding Problem when calculating 4 equal instalments of a given total

All I need to do is calculate how a premium is paid over 4 quarterly instalments by simply using the following oh so simple 'formula':

=A1/4

This is shown in 4 columns - 1st Instalment, 2nd Instalment, 3rd Instalment & 4th Instalment. The same above formula is replicated across.

However, because of the way Excel rounds calculation outputs up or down it doesn't appear accurate enough.

For example...

A1 Total = 129,335.70

Excel calculates each instalment as below:

Inst 1 = 32,333.93
Inst 2 = 32,333.93
Inst 3 = 32,333.93
Inst 4 = 32,333.93

When you total those 4 instalments up you have 129,335.72 not 129,335.70!

Does anyone know how to get around this annoying rounding issue?!?

Much appreciated

You'll always get rounding issues in cases like this. One way to resolve it is to make the 4th payment = to the balance due, ie the total less the sum of the first 3 payments.

Also make sure that you round the first 3 payments:
=round(A1/4,2)
so that what you see on the screen is exactly what is stored.

Regards
Mike