How to calculate present value (PV) in formula

Question

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 )

Answer 1

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

Answer 2

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