# Annual compounding with monthly payments

I'm trying to solve the following problem using the Time Value of Money formulae on MS Excel and getting nowhere. Can someone please help me?

X makes monthly payments of \$3500 at the end of each month for the past 10 years. The fund has a compound return of 12%. What is his accumulated corpus at the end of 10 years?

The correct answer by the book is \$784,126, which matches what I am getting from putting these numbers here. The grab from the output is shown below.

If I was to use the PMT formula in MS EXCEL, how do I get the same answer?

• A comment, not answer. I see the problem. On the linked site, the way to get the correct result is with annual compounding. So you need 12 deposits, but only credit interest once per year. – JTP - Apologise to Monica Aug 27 '16 at 13:24
• @JoeTaxpayer this is handled effectively by any handheld financial calculator, because it has entry points for p/y - payments per year and c/y - compoundings per year. I'm trying to use excel to reach the same end. – Anees Rao Aug 27 '16 at 13:30
• Right. I'm not seeing that option in excel. – JTP - Apologise to Monica Aug 27 '16 at 14:09
• If the interest is compounded monthly, then your answer is correct. If it is compounded annually, then the amount is about \$737,000. So, I don't see where the \$784,126 is coming from. – Five Bagger Aug 27 '16 at 21:57
• @FiveBagger See the linked site. – JTP - Apologise to Monica Aug 27 '16 at 22:27

This calculation arrives at the correct answer. However, it uses the formula for an annuity due. This means the payments are made at the beginning of the month and the last month of the 10 year period has interest accrued.

See the section, Calculating the Future Value of an Annuity Due.

The rate is given as an effective rate.

``````annual effective rate = 12%

monthly rate = (1 + 0.12)^(1/12) - 1 = 0.00948879
``````

with

``````n = 120
d = 3500
r = 0.00948879
``````

In Excel, `=FV((1+0.12)^(1/12)-1,120,3500,0,1)`

• +1 - Ha! I almost commented to the question, "I understand the issue. But Chris Degnen knows spreadsheets better than anyone." And I was right. Nice work, sir! – JTP - Apologise to Monica Aug 28 '16 at 16:18