2

On January 1st I invest 1238. I do this every January 1st for 20 years. At the end of 20 years (So December 31st of the 20th year), I have 32880 in the account. What's my rate of interest?

By playing around with excel and creating 20 rows of changing values, I find that the interest rate is 3.5787%. I believe this to be the answer.

But I cannot find any set of values to plug into the Rate() function to get me this result. Seems Rate() always rants a negative value plugged in which doesn't make sense to me. And it reports 2.6 or 2.9% depending on interest being calculated at the beginning or the end of the period.

[Edit]: People have mentioned to use RATE(). Here's what I get:

rate(20,-1238,0,32880,0) = 2.885

rate(20,-1238,0,32880,1) = 2.629

Neither of those are my expected answer.

Edit2: As I mentioned in the comments, I placed an interest value into B1. Then I put 1238 into A1. I made A2 = ((1 + B1) * A1) + 1238. Then I repeated for A3-A20. I then played with B1 until the end result was as expected. I got 3.5787%. My problem was that this only accounted for 19 years of interest. I needed to create 21 rows, not 20. And the last row would not add the $1238 again. Doing this I am able to plug in 2.629 into B1 and get the result I expected.

  • You had it right with RATE -- pmt is negative if your putting money in -- use end of period. – bdimag Oct 15 '15 at 16:16
  • I edited to show the Rate() results. It does not seem right. – Paul Oct 15 '15 at 16:18
  • If you build a table of deposits and interest for 20 periods (interest based on RATE(20,-1238,0,32880,0)) you end up with 32880 – bdimag Oct 15 '15 at 16:20
3

I believe the 2.63% is the rate. That's what:

=RATE(20,-1238,0,32880,1)

gives, as does:

=XIRR(R2C2:R22C2,R2C1:R22C1)

(with 1238 in R2C2:R21C2, -32880 in R22C2, 1/1/95 through 1/1/14 in R2C1:R21C1, and 12/31/14 in R22C2) and if you check that by "hand", that is in some column, in rows 2-21, put:

=POWER(1.0263,20-ROW()+2)*1238

then sum those, you get $32,884.11, which is just some rounding away from $32,880.

Why do you expect 3.5787% to be the answer?

  • So I stuck a percentage into cell B1. Then I stuck 1238 into A1. I then set A2 = (A1*B$1+A1+1238). So interest on what was there, plus what was there, plus a new payment. I extended this 20 rows. Then I played around with the interest in B1 until I got the expected ending value. – Paul Oct 15 '15 at 16:22
  • Ok, yes the last row just needs to be the final year of interest. Then your procedure is the same as my =POWER formula, and in fact gives the same final answer, $32,884.11. This is exactly why I calculate stuff like this two different ways, if you get the same answer, you're probably doing things correctly, if you get different answers, you can investigate which way is right. – blm Oct 15 '15 at 16:36
  • FWIW, if you go in Excel to DATA/What-if Analysis/Goal Seek you can have Excel do the "playing around" with the interest rate to give you desires final total... – DJohnM Oct 15 '15 at 18:30

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.