Calculating FV. Where is my mistake? Is my error in excel or in using BAII?

Question

Problem statement: What is the FV of an investment of $10,000 which pays 7% interest, compounded monthly, for five years? What is the FV is it's compounded semi-annually for 5 years?

Using excel I have the following answer:

And using BAII texas: I set:

Part a)

Compounded monthly : P/Y=1 C/Y=12

PV= -10,000

PMT=0

I/Y=7

N=5

Ans: $14,176.25

Part b)

Compounded semi annually: P/Y=1 C/Y=2

PV= -10,000

PMT=0

I/Y=7

N=5

Ans: $14,105.99

Question: I can't found my error, if is in excel or using my calculator? Thanks!

Answer 1

Under the assumption that the $7\%$ interest rate is a nominal rate of interest compounded monthly in the first case and semiannually in the second, we see that the effective monthly interest rate would be $j = 0.07/12 = 0.0058333$, and the accumulated value after $n = 60$ months is $$FV = 10000 (1+j)^{60} = 14176.25.$$ Your calculator is correct.

In the second case, the effective semiannual interest rate would be $j = 0.07/2 = 0.035$, and the accumulated value after $n = 10$ 6-month periods is $$FV = 10000 (1+j)^{10} = 14105.99.$$ Again, the calculator is correct. You seem to have an error in your spreadsheet; especially since it seems to think that your future value is negative. As to what that error could be, I have no way to tell since I cannot examine the formulas in the cells.

Answer 2

Your Excel formula is incorrect. You should have:

=FV$(\frac{0.07}{12}, 5*12, 0, -10000, 0) = £14,176.25$

=FV$(\frac{0.07}{2}, 5*2, 0, -10000, 0) = £14,105.99$

Remember: FV() in Excel is interest driven and is not "pure" math compounding. It assumes that interest ($7$%) is paid in equal tranches (e.g. $1/12$ per year for monthly payments or $1/2$ per year for semi-annual payments), which are compounded. FV() calculates $(1 + (\frac{7%}{12}))^n$ for period $n$. In effect, it applies an effective annual rate.

What I call "pure" compounding would be $(1 + 7\%)^{1/n}$, where $n$ is the period number (e.g. $n =$ month $3$). Therefore, FV() will always be higher than "pure" compounding because effective rates end up being higher than nominal rates.