Help with rate of return question?!?

Question

Adam deposits the same amount of money each month into a bank account that accumulates interest compounded monthly. What interest rate will allow him to get a rate of return of 100% in 20 years?

How would you go about answering this question? We are allowed to use an excel spreadsheet to answer it. I dont know how to find out the rate of return at 100 percent.

Answer 1

You shouldn't call that the "rate of return," which usually means the overall rate earned. What it sounds like you are looking for is an accumulated value that equal twice the monthly payments.



It sounds like payments would be paid in at the beginning of each month, and you want an accumulated value at the end of the 240th month.



In an Excel spreadsheet, type a 1 in cells a1 to a240. Actually just type it once and copy it down. Then put a -480 in cell a241, and then in B1 type =IRR(A1:A241,.01)



That solves for the "internal rate of return" of that cash flow stream. The .01 is just a guess. Excel does a trial and error thing to solve this, since there is no simple direct formula, and the 0.01 is basically just giving it a hint. It works out to 0.5282%, which is a monthly rate since each period in the cash flow stream represents 1 month. The nominal annual rate compounded monthly is 12 times that, or 6.3380%. The annual effective rate is (1.005282)^12 or 6.5254%.



Douglas' formula is a not quite right. The brackets aren't quite right, and it looks almost like the formula for the PRESENT value, not the FUTURE value, and seems to assume payments are made at the end of each month.



The formula for the Future Value of an annuity paid at the beginning of each month is:

=(1+i) * ( (1+i)^240 - 1) / i

Remember that i will be a MONTHLY rate and you'll have to annualize it as I did above to get the nominal or effective rate.



If payments are made at the end of each month, the (1+i) factor at the beginning needs to be omitted.



The formula is easily derived from the formula for the sum of a geometric series:

If s = (1+i) then you want the sum of

s + s^2 + ... s^240

= s(1 + s^239)

= s (s^240-1)/(s-1)

= (1+i)((1+i)^240 - 1) / i



You can use Excel's goal seek feature to find the i that makes that = 480. The IRR function does that for you automatically, where you can list the payments for each period. It give the interest rate that makes the discounted value of the payment stream = 0. That's why the last cell in the range is -480 not +480. Equivalently, the accumulated value is +480. You can prove that by doing the monthly accumulation and getting 480 at the end of month 240.

Answer 2

My source says that can only be done by trial and error. The formula is:

480 = [1 - 1/(1 + i)^240/i] You try to do the algebra!

Answer 3

probable because of compounding in the commercial employer account. In Excel the IRR of cashflows for a three year funding like (-one thousand, one hundred, one hundred, 1100) will be 10%. besides the undeniable fact that on a bankaccount calculation the fee after 3 year must be 1331. This takes into consideration activity earned on activity, even as the IRR does no longer try this.