Excel formula for calculating interest rate of earning

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.

: 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. Commented Oct 15, 2015 at 16:16
• I edited to show the Rate() results. It does not seem right.
– Paul
Commented 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 Commented Oct 15, 2015 at 16:20

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
Commented 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
Commented 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... Commented Oct 15, 2015 at 18:30