function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
TTaggTTagg 

Loan Maturity Date Formula

I cannot seem to figure this formula out at all.

We have

Original Funding Date
Original Term
Formula (Original Maturity Date)

Example:

Original Funding Date: 3/10/2000
Original Term: 120

Needs to return: 2/10/2010

Formula being used:
DATE( YEAR( Original_Funding_Date__c ) + ( Original_Term__c /12) ,  MONTH(Original_Funding_Date__c )-1 ,DAY(Original_Funding_Date__c ) )


Problem: It seems to return an error when the month is in January/1. I'm assuming this is because salesforce doesn't understand how to count backward to 12 from 1.

Thank you so much for your help in advance!


Alex TennantAlex Tennant
You're right, you need to account for the situation where the month of Original_Funding_Date__c is January. You will also need to account for this in your year calculation too (unless I've misunderstood what you're trying to achieve).

The following should handle this for you:

IF(
  MONTH(Original_Funding_Date__c) == 1),
  DATE(
    YEAR( Original_Funding_Date__c) + ( Original_Term__c /12) - 1,
    12,
    DAY(Original_Funding_Date__c) 
  ),  
  DATE(
    YEAR(Original_Funding_Date__c) + ( Original_Term__c /12),
    MONTH(Original_Funding_Date__c) - 1,
    DAY(Original_Funding_Date__c) 
  )
)