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

9
  • 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. Commented Aug 27, 2016 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
    Commented Aug 27, 2016 at 13:30
  • Right. I'm not seeing that option in excel. Commented Aug 27, 2016 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. Commented Aug 27, 2016 at 21:57
  • @FiveBagger See the linked site. Commented Aug 27, 2016 at 22:27

1 Answer 1

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
  • +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! Commented Aug 28, 2016 at 16:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .