Views:

Overview

North52 BPA can be used for calculating common financial formulas. This example shows you how to create the Present Value of a future amount of money.

North52 Decision Suite

The North52 Decision Suite solution works like this:

  • A Formula is created for a entity which has four custom fields which capture the Future Value Amount (FV), Percentage Interest Rate Per Period (I) and the number of periods (N)
  • A Decision Table is set up which:
    • Checks all fields have values
    • Calculates the Present Value using the following formula:
      • PV = FV/ (1+ I/100)N
    • Saves the calculated value into a custom field called Present Value

Formula

  • Create a new formula, setting the following values in the Formula Guide:
    • Source Entity set to Financial Calculation (or your entity which has the four fields outlined above) 
    • Set Formula Type to Save - Perform Action
    • Select the Decision Table editor
  • Change the Name of the formula to Financial Calculation - Present Value
  • Expand the Source & Target section on the Register tab
    • Crtl-Click to select the fields: PV Amount, PV Interest Rate Per Period (%), PV Periods (your fields may be named differently)
  • Click Save
  • Right-click on column A and select Insert > Insert Condition
    • Repeat this again so that you have 3 condition columns
  • Select cell A2
    • Select the Source tab, expand the Source node and select PV Amount
  • Select cell B2
    • From the Source tab select PV Interest Rate Period (%)
  • Select cell C2
    • From the Source tab select PV Periods
  • Select cell A4
    • Select the Functions tab, and search for containsdata
    • Ctrl-click the ContainsData function, this will add {{{ContainsData}}} to cell A4
  • Repeat for cells B4 and C4 (or copy and paste from cell A4)
  • Select cell D2
    • From the Source tab, expand the Source node and select Present Value
  • Select cell D4
    • Expand the cell editor by clicking the + in the top right of the Decision Table sheet
    • From the Source tab, select the PV Amount field
    • Type / 
    • Select the Functions tab, and search for Power
    • Select the Pow function to add the function and placeholder parameters to the editor
    • Delete the 'value' parameter including the quotes
    • Type 1+ (if you prefer you can add spaces for readability either side of the plus character) 
    • From the Source tab, select PV Interest Rate Per Period (%) 
    • Type /100
    • Delete the 'power' parameter including the quotes
    • From the Source tab, select the PV Periods field
    • Your formula should now look similar to this: 
      [north52_financialcalculation.north52_pvamount]/Pow(1 + [north52_financialcalculation.north52_pvinterestrate]/100, [north52_financialcalculation.north52_pvperiods])
    • Close the cell editor by clicking the X in the top right of the Decision Table sheet
  • Click Save

Testing

Create a record which has your Present Value custom fields completed, save and review the calculation. You may want to check the calculation returns the correct value by using the PV function in Excel:


Did you know?

TestShield automated tests for Dynamics 365 are easily exported/imported

TestShield uses standard Dynamics 365 solutions to package up your configured tests for easy transport and deployment to other Dynamics 365 instances.

This enables use in automated Azure DevOps pipelines where you can automate the build of a Dynamics instance and apply the TestShield solution to execute your tests. 

It also allows you to put your automated tests into source control alongside other customizations.

Learn more about TestShield

Keywords: Decision Table, Pow, ContainsData, DecisionTable, Formula Manager