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.