Different interim balances when calculating annual compound interest different ways

Question

Something interesting that doesn't quite make sense to me. When calculating compound interest using P(1+r)^t, I'm calculating in two ways - one using the day 1 principal balance, and the other using the balance as it grows month to month. However, I'm getting different slightly different values during the middle of the term(months 13-23), but the same beginning and ending values. I'm fairly certain the values at any given point should be equivalent, but I'm not quite sure why they're off. I attached a link to a spreadsheet to help get the point across, Cells G31:G43 should be 0's if the calculation is equivalent.

https://drive.google.com/file/d/0BxZVRdEyIo6wSXVITUc2MnZHV0E/edit?usp=sharing

Answer 1

The way you calculate interest on interest (column H) is different from the way you treat the initial capital. In column E they are both calculated using the same method but column F or L accumulates interest from two different sources.

Technically $P*r*t$ is not equivalent to $P*(1+r)^t-P$.

Answer 2

No, they should not be equivalent. In a way actually, both values are somewhat meaningless. While the annual interest rate is 10%, you can compound it not only every year, but also every month, every day, or even continuously. However, the formula $P\times(1+r)^t$ implies yearly compounding and does not have too much meaning for fractional values of $t$: it is going to give you an exactly right answer for integer $t$s, and intermediate values for fractional $t$s.

So as you might have already suspected, you have found a slightly different alternative to partition, if you will, [the correct] yearly interest into the year's months. To make things simpler, let's look at year 1, rather than year 2 which has the added complication of Interest on Yr 1 interest etc.: note that an alternative to your Column M [Cumulative Capital Account] could have simply been again $P\times(1+r)^t$ - and you would have gotten the same \$0.37, \$0.64, \$0.88, etc. differences that you noticed for Year 2. Thus demonstrating on Year 1 would be valid.

In your calculations, you make an implicit assumption that January is equivalent to December: both have 31 days and both yield \$8.49 in interest. This is not exactly wrong but this is not how this formula is supposed to be used. You are actually saying something like, "the yearly interest is \$100 on \$1000, so a 31-day month should yield $\frac{31}{365}$ of that interest - whether it is in the beginning or at the end of the period. Hence the accrual is linear with time: depends on the number of days, but not where in the year these days are." Conversely, the power function $(1+r)^t$ increases faster with larger values of $t$: so it might be trying to tell you that December should yield more interest (call it implicit compounding if you wish): in fact, this power function increases only \$8.13 in January, but \$8.87 in December.

It's hard to see because the differences are so small. But let's imagine a more extreme example, with an annual interest rate of 100%:

Now the difference between the two partitions increases to more than $85 mid-year - but vanishes at year-end. Note the linearity of the "Fraction of yearly interest" approach in Year 1.

P.S. You have misused the term variance in the spreadsheet - should be difference (one has to be very careful with mathematical terminology).