RATE formula help
I am trying to replicate the Excel formula RATE in Anaplan. It seems to work for Q4, but not for the rest of the quarters.
The excel formula is: =RATE(FRAC,,-$C$30,H30) where -C$30 is -100 and H30 is Total Return Index for that period.
I have created below in Anaplan:
Why am I getting different returns?
I am not extremely familiar with the RATE function, but I would say that the number of payment periods in Anaplan has to be entered in months (3) as opposed to year (1.25) in Excel.
Edit: I have tried myself and it did not work. I got close to it by working it out in months: (I have converted your FRAC in months by multiplying it by 12 and I have multiplied by 12 the RATE as well):
Not of much help, but something interesting I have found though is that excel assumes payments at the end of the period by default, and Anaplan at the beginning.1
Thanks for looking into.
I've tried with both 0 and 1 for payment timings and they both arrive at the same answer!0