To set the Retainage calculation select Settings -> Retainage Calculation from the Paymee menu. The following dialog opens:
Notes:
-The Retainage settings per the above dialog are applied to the entire project. If you need to change only the retainage settings for a particular line item, then use setting D or E and then you will be able to over-ride each line item setting with a value or formula.
-Pressing OK to the above dialog will reset all retainage settings for the entire project. Restated: if you were using setting D or E, then any over-written line item settings will be lost.
-Retainage is always applied on a cumulative basis (unless you change this via a custom formula). Restated: retainage is always computed based on the cumulative value completed for a line item. It is not based on the 'this application' value.
Item A: This setting will release the retainage for the entire project. Restated: any retainge will now be counted as completed work instead & will be included in the billing. This setting should always be set on job completion for your last billing.
Item B: This setting will apply the same retainage percent to all line items. This setting will not allow user to over-ride any values.
Item C: This setting will apply the same retainage percent to all Stored Material line items & a sperate value to all non-Stored Material line items. A line item is considered stored material if the 'This Application value' is entered into the Store Material column. Note this column may be turned off (not visible) depending on your customize settings.
Item D: This setting will apply the same retainage percent to all line items - but will allow you to edit individual line items. Warning: if you already have this set, pressing OK will reset any overridden line items back to the default per item D. Tip: to see the default line item formula press F2 when on the retainage value for each line item.
Item E: This setting will apply a formula that you specify to calculate retainage. Formulas must start with = . See below for more info on retainage formulas.
Item F: This setting specifies if retainge is calculated for any Deletions. For consistency with American & AIA billing standards, this setting is not enabled if using the America Template (turned off).
Setting Custom Retainage Calculations
Some projects may require Retainage to be calculated in a custom manner that cannot be expressed as a percentage of Work-in-Place or Stored Material. For this case, you would select the Custom formula option in the dialog above & then you could enter a formula that is a function of any data provided in Paymee.
The following line item variables are available for use in Retainage formulas:
Col_Desc
SchedValue
PrevValue
PrevAppPct
ThisAppWIP
ThisAppStoredMat
ThisAppPct
TotalValue
TotalPct
BalanceValue
ToFinishPct
Retainage
The following Summary variables are also available:
OriginalContract
ValueOfChanges
TotalContract
TotalCompleted
TotalRetainage
TotalBilled
PrevAppTotalBilled
PaymentDue
BalanceToFinish
Using the above you can construct virtually any Retainage calculation formula....
Example 1. Suppose Retainage for a project was given as: "20% until each item was 50% completed then 10% until complete". This could be entered as:
=IF(TotalPct<50%, 20%* TotalValue, IF(TotalPct < 100%, 10%* TotalValue, 0)).
Example 2. Suppose Retainage was given as: "10% for any line item where the Scheduled Value is over $100,000 otherwise 15%". This could be entered as:
=IF(SchedValue>100000, 10% * TotalValue, 15% * TotalValue).
Example 3. Suppose Retainage was given as: "10% for all Interior Building items & 20% for all Exterior Building items". For this case we recommend using a constant word within each line item's Description field so that a formula can be constructed to delineate between 'Interior Building' and 'Exterior Building'. In this case we can have all the 'Interior Building' line items contain the word 'Interior' in the line item description. Then we can use the following formula:
=IF(ISERROR(FIND("Interior", Col_Desc)), 20% * TotalValue, 10% * TotalValue)
Formulas must start with a '='. The general syntax for an Excel IF formula is:
=IF( [Condition] , [Return if Condition is TRUE] , [Return if Condition is FALSE] )
For more information on entering formulas, see Excel Help (F1).