RATE function not working as I understand it

  • Thread starter Thread starter daniel
  • Start date Start date
D

daniel

At the beginning of each quarter for 4 quarters, I deposit $1 in the
bank. At the end of the year, I want $4.4 (including the compounded
interest). Hence, I deposit a total of $4 (4 times $1). What is my
quarterly Rate? =-RATE(4,-1,4*(1+.1)) equals - 3.706 which if I then
use in excel and compound dragging across multiple cells the sum total
of (1.037, 1.08, 1.12, 1.16) is 4.38 and not 4.4. The only way I have
been able to find the correct compound rate is using goal seek which
found 3.84898048017355%. I know I am mixing and matching annual with
quarterly but I don't believe it should change my calculation. I have
tried adjusting for 365/360 in case Rate was using some sort of
partial year but that didn't account for the difference. Let's see I
have been at this for 6 hours and tried PMT, PPMT and all the variants
of PV. thanks.

Daniel
 
At the beginning of each quarter for 4 quarters, I deposit $1 in the
bank. At the end of the year, I want $4.4 (including the compounded
interest). Hence, I deposit a total of $4 (4 times $1). What is my
quarterly Rate? =-RATE(4,-1,4*(1+.1)) equals - 3.706 which if I then
use in excel and compound dragging across multiple cells the sum total
of (1.037, 1.08, 1.12, 1.16) is 4.38 and not 4.4. The only way I have
been able to find the correct compound rate is using goal seek which
found 3.84898048017355%. I know I am mixing and matching annual with
quarterly but I don't believe it should change my calculation. I have
tried adjusting for 365/360 in case Rate was using some sort of
partial year but that didn't account for the difference. Let's see I
have been at this for 6 hours and tried PMT, PPMT and all the variants
of PV. thanks.

Daniel
Click to expand...


=RATE(4,1,0,-4.4,1)


--ron
 
You missed a parameter in your function.

First, =Rate(4,-1,4.4) returns -3.706% (note the negative sign). That means
you lost money.

Your problem is you entered PV rather than FV. You need:

=rate(4,-1,0,4.4)

That returns 3.849%
 
And I missed a parameter. You need:
=rate(4,-1,0,4.4,1)

The last parameter tells Excel payments occur at the beginning of the
period.
 
Back
Top