I have a formula (see below) which works in Excel and uses the -PV function (in bold). The PV function calculates the present value of a loan based on certain criteria. However, Salesforce doesn't recognise the function...

Is there a similar function/formula I can utilise to get the value I need?

The full formula

( Advance_Cost_Less_VAT__c - Predicted_Residual_Value__c /((1+(MAX( Base_Rate__c )+ Rate_Over_Base_Rate__c )/12)^( Subsequent_Rentals__c +IF( Residual_With_Final_Rental__c ="No",1,0))))/(-PV((MAX( Base_Rate__c )+ Rate_Over_Base_Rate__c )/12, Subsequent_Rentals__c ,1)+ Advance_Rentals__c )

(-PV((MAX( Base_Rate__c )+ Rate_Over_Base_Rate__c )/12, Subsequent_Rentals__c ,1)+ Advance_Rentals__c )

There is no function equal to the Excel PV function in Salesforce formula fields. You will need to rebuild the PV calculation using the mathematical functions available.

There are some examples of how to calculate PV in Salesforce formula fields in this discussion: https://success.salesforce.com/answers?id=90630000000gh0qAAA

However, looking at the formula you have here, which has multiple references to PV and a lot of extra calculations, you run a serious risk of hitting the salesforce formula field length limit. You will find this out while composing the formula.

I'd recommend cutting the calculation up in separate formula fields (just like in the link above), so you can validate intermediate results. Then combine the intermediate fields in a final calculation field. (This will not reduce the max formula size limit, though)

If you hit the max size limit, the alternatives are: - use workflow field updates to calculate intermediate results - use an APEX Trigger to do the calculation


Solved it!

To all in the future.

I manually created the formula using Present Value of Annuity instead of just Present Value

View the formula here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.