🤔 Have you tried to use exponentiation in a Salesforce formula?
Know that there are limitations!
See the "Payment (formula)" below:
( - Future_Value__c + Present_Value__c * ( 1 + Rate__c ) ^ Number_of_Periods__c ) /
( ( ( 1 + Rate__c ) ^ Number_of_Periods__c - 1 ) / Rate__c )
It naively attempts to calculate the monthly payment amount using ( 1 + monthly rate ) to the power of Number_of_Periods__c (360 months in 30-year loans).
🤯 But exponentiation using the operator ^ in Salesforce only works in a narrow set of scenarios.
😳 The primary limitation is: it only works with an integer exponent. If you use a column, even if it is defined as Number(18,0), it is internally stored as a Double and the formula will fail to calculate. And I've tested with a hard-coded 360 and it didn't work either. 🤷♂️
🎩🐰 The trick to implement these and other financial formulas is to convert the exponentiation to the equivalent EXP( LN() ) expression:
( - Future_Value__c + Present_Value__c * EXP( Number_of_Periods__c * LN( 1 + Rate__c ) ) ) /
( ( EXP( Number_of_Periods__c * LN( 1 + Rate__c ) ) - 1 ) / Rate__c )
🪵 This takes advantage of natural logarithm (LN), which is the inverse of natural exponentiation (EXP). By converting to a logarithm, you can multiply the exponent and then apply the inverse of the logarithm (which is the natural exponentiation EXP) and get an accurate result.
EXP and LN functions accept any number.
I like Math! 🔢♾️❤️
