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.

3
  • You had it right with RATE -- pmt is negative if your putting money in -- use end of period.
    – bdimag
    Oct 15, 2015 at 16:16
  • I edited to show the Rate() results. It does not seem right.
    – Paul
    Oct 15, 2015 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, 2015 at 16:20

1 Answer 1

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?

3
  • 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, 2015 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, 2015 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, 2015 at 18:30

You must log in to answer this question.

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