Creating a Model with periodic Loops

Learn how to use the previous function to correctly model periodic loops.

For most use cases the ROLLFORWARD function provides a much simpler and faster calculation. Only if you need to adjust the basis of the simulated value in a future year, e.g. by additions/deductions, the previous loop is needed.

Once you have modeled the general structure of your business model, you might want to add some periodic loops in there to achieve the correct calculation of certain KPIs. 

The previous function will help you when you want to calculate these loops - for example when your ending balance of a calculation also represents the input balance for the same calculation in the next period. This can be the case in many financial KPI calculations, for example when you want to calculate your company's ROI, but of course, other applications are possible.

To use the previous function, add a calculated node "next" to your starting balance actuals node. For the new calculated node, use the Previous function as operation: PREVIOUS('Final Balance', "Year", 'Opening Balance Actuals', "2015"). This will cause your calculated beginning balance to always contain the values of the final balance node, except for the specified year (here 2015) in which the Opening Balance Actuals value is used. Of course, you could also do this on a monthly level. 

To calculate the final balance of the loop, you can simply use the starting balance and then add or subtract the necessary values as you would normally do to calculate the first final balance. We recommend that each item you deduct from or add to your starting balance has its own node and has absolute values. The values can be entered as percentages if you want to calculate a change, however, this is often hard to track and understand in later loops.