Take the 2-minute tour ×
Quantitative Finance Stack Exchange is a question and answer site for finance professionals and academics. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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$.

share|improve this answer

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%: Two ways to partition yearly interest over the months

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).

share|improve this answer
    
Exactly what I was looking for, thanks. Also, my use of "variance" is the jargon for my industry, although I understand where you're coming from. –  jeff m Sep 8 at 18:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.