Forum Discussion
Rate issue in excel
Hello I am trying to figure out why the RATE function is excel is not working properly for me. I am trying to solve the problem =Rate(100,0,-125,36000000) 100=n, 125=PV, 0=PMT, FV=36 Million, but when I click enter it keeps showing #NUM!
I have tried to do research and watch videos, but I am not sure what is wrong. I know the correct answer is 13.40% , but I was trying to practice using excel to get that answer. If you are able to help me in any way I would greatly appreciate it. Thanks
- https://support.microsoft.com/en-us/office/rate-function-9f665657-4a7e-4bb7-a030-83fc59e748ce
guess defaults to 0.10 if it's omitted. The recommendation is to try small increments between 0.11 and 0.99 until you land on a value that converges.
4 Replies
FYI, RATE will always give you only an approximate result; sometimes good, sometimes not.
But when pmt=0, the periodic rate can be calculated exactly by:
=(fv/pv)^(1/nper) - 1
formatted as Percentage. The formula assumes that both fv and pv are positive.
For example, (36000000/125)^(1/100) - 1 results in 13.3950050991972%.
- Thanks so much for your help it worked for me!!! Now It brings me to another question how do I know what number to use for the guess for future references?
- https://support.microsoft.com/en-us/office/rate-function-9f665657-4a7e-4bb7-a030-83fc59e748ce
guess defaults to 0.10 if it's omitted. The recommendation is to try small increments between 0.11 and 0.99 until you land on a value that converges.
