Community perspective: Performing FVSchedule Excel function in Anaplan
When searching for FVSchedule in Anaplan Community there was seemingly method for/content about this. There are always multiple methods to achieve the same result but this method has provided the required outcome and may be useful for anyone hoping to tackle an FVSchedule or similar calculation in Anaplan.
What is FV Schedule?
First, we start with FV (Future Value) which is a formula used in Excel and is already an available function in Anaplan. FV calculates the future value of an amount based on a constant interest rate.
Unlike FV, in FVSchedule (Future Value Schedule) the interest rates can be defined as a schedule of rates and can vary in each period. The FV calculation is done iteratively by applying the calculation to the first interest rate of the schedule, then using the outcome of the first calculation to be applied to the next interest rate and so on (e.g., return 10% in year 1, 8% in year 2, 13% in year 3, etc.).
FVSchedule is available in Excel, but is not a function in Anaplan and therefore a workaround is needed to replicate the FVSchedule, when the calculation of a future value with a variable or adjustable rate is required.
Example case:
The example used is a calculation for future value (Future Interest return/performance) for December 2022, from a compounded 3 month variable (Rolling) monthly return schedule.
Anaplan Method:
Note: See end notes for prior attempts and reasoning for this approach. Due to the iterative nature of the rolling calculation, a module with two time dimensions is used to facilitate this in Anaplan. However, Anaplan is currently only able to apply one time dimension to a module. A list called ‘Fake time’ is created and used to add the additional time dimension. In this example we are using months, so a month (Period) list is used.
Once a ‘Fake time’ list is created and module has been set up to link real to fake time.
A module with time, fake time and any other specific use case dimensions can be added.
Fake time has been positioned on the row dimension along with line items. Real time is positioned on the column. Please note that the calculation 1 view below has been left unoptimized for ease of explanation. Calculation view 2 below is an optimized version of this view which combines logic into fewer line items (Less line items and therefore less space) and the subsidiary views are in system module.
Calculation view 1 (Not optimized):
Calculation view 2 (Optimized):
The real time dimension at the top may be confusing, but is used primarily used for the iterative calculation and ease of the Anaplan native time functions (specifically the previous function). It can be easier to understand the concept if you imagine the “Number of Months” line item to be the header.
The “Month” Line Item identifies what month the return should come from to populate the “Base %” line item. We then compound the growth over time, subtracting 1 to be left with the total growth between the “month” line item and the month in the columns (e.g., the total growth between Oct 22 and Dec 22 is 0.94%).
The calculation above returns the same result as the Excel FV schedule screenshot provided earlier, matching the variable growth rate over time.
This is the engine of the calculation and we can then return this calculation output to a view that is easier to read/to dashboard for users.
---
End notes:
Initial method:
We first attempted to perform a simpler/more efficient method which used a compound interest equation such that FV CI = P(1+r/n)^nt – P. Anaplan’s powerful timesum function was used in addition to this to get a formula:
POWER(1 + TIMESUM(Performance Calc.Calc Net Return Monthly, Time.‘Oct22’, Time.‘Dec’) / 3, 3) – 1
Which was optimized/centralized became:
POWER(1 + TIMESUM(Performance Calc.Calc Net Return Monthly, 'INP 00 - Model Start Date'.'3 months Start', 'INP 00 - Model Start Date'.Model Date for FV Shed) / 'INP 00 - Model Start Date'.'3 Months', 'INP 00 - Model Start Date'.'3 Months') - 1
The outcome of this calculation was close to the required result, but was not entirely accurate as the logic above uses a sum of interest rate schedule and divides it by the number of periods, resulting in an average, rather than an iterative calculation which gets less accurate the more extreme the variability is with the interest schedule.
---
Company: Lionpoint
Author: Caleb Williams
Co-author: Mitch Stepanuks
---