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?




  • AlejandroGomez
    edited April 2023

    Hi @CommunityMember113484

    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.

  • Thanks for looking into.

    I've tried with both 0 and 1 for payment timings and they both arrive at the same answer!