Rounding differences

I am unclear about what kind of solution you are looking for.

It is a fact that whenever we round values, we might encounter "quantization errors". They are unavoidable.

That is why most financial reports have a footnote, something like: ``Due to rounding, the sum of the individual line items might not add up to the total.``

-----

The correct solution for your purposes depends on "generally accepted accounting principles". It is not an Excel problem.

I am not a CPA, nor do I know what alternatives are acceptable for your situation.

-----

I would clearly separate facts from calculated approximations.

Approximations should be recalculated for each reporting cycle, based on the facts for that cycle.

Approximations should not be used to derive "facts" and other approximations.

In other words, your "check" is invalid and irrelevant, at least the way that you did it.

-----

Fact: starting NAV is 335,000,000.000000 (C10), and ending NAV is 334,669,340.380000 (C16).

Fact: starting #units is 335,000.0000 (C33), and ending #units is 335,000.0000 (C37).

It is unclear to me which values are rounded to 4 dp and which to 6 dp.

But they should be recorded and used in calculations to the precision allowed by the system.

What you record is fact.

Your report might round them to 2 dp, if that is smallest denomination of currency for you (US).

But what you report is an approximation, which should be recalculated for each reporting cycle.

-----

Approximation: starting (average) unit value 1000.0000 (C25), and ending (average) unit value 999.0129 (C27).

As noted above, each approximation should be calculated based on facts, not other approximations.

So my calculations would be:

C25: =ROUNDDOWN(C10/C33, 4)

C27: =ROUNDDOWN(C16/C37, 4)

Note that I round down instead of just round.

My rationale is: that is how it must be done for stock exchanges (splits and mergers). The number of units exchanged is rounded down, and the difference between the original NAV and the new NAV (#units * per-share-value, rounded as prescribed by the underwriter) is distributed as "cash-in-lieu".

Since we cannot distribute "negative" cash-in-lieu, we must round down the number units exchanged.

However, for your purposes, you might choose to round, since it is just an approximation (wink).

In either case, you should include the caveat that due to rounding, calculations based on the reported appproximations might not match actual facts.

----

If you choose to round down, you might choose to report effectively the cash-in-lieu amount so that the calculated approximations do indeed match the facts.

There might be a better term to use for that in this context. I think I've seen it in some brokerage statements.

But the term escapes me at the moment.

I might report "excess NAV", for lack of a better term.