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 BPA Solution

The North52 BPA 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: