2

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.

Grab from linked site

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

calc in excel

  • 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
2

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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