RATE formula help

CommunityMember113484
CommunityMember113484
in Modeling

Hi,

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.

image.png

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:

image.png

Why am I getting different returns?


Thanks

Tagged:

Answers

  • AlejandroGomez
    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):

    image.png

    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.

    image.png
    image.png

  • CommunityMember113484
    CommunityMember113484

    Thanks for looking into.

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

Categories