Having trouble thinking through a pricing with inflation formula. The formula needs to calculate the value of price + inflation for a specified year. Inflation will depend on the end date and the year being represented in the field. For example:
- Formula Field = Goal is to display the 2019 Price
- Year of Start Date = 1/1/2018 (YEAR(Start_Date__c))
- Amount = $100 (Amount)
- Inflation Rate = 3% (Inflation_Rate__c)
In the example above, I need to find what the price will be in 2019 with 3% inflation. The formula needs to be able to account for changes to the start date.
Inflation will not start until the year after the start date.
So if I were to change the start date to 1/1/2019, inflation would not need to be calculated; price would just be $100. But if I were to change the start date to 4/20/2017, inflation would need to be calculated for 2018 AND 2019. So the price would be $100 * 1.03 * 1.03 = $106.09. Any ideas on how to best accomplish this?
Thanks in advance!
Hi Kristy,
I'm not sure if I did this the same way you're asking it to work, but let's start with this and we can change it if needed.IF(((YEAR(Calculate_Inflation_Starting_This_Day__c) - YEAR(Start_Date__c)) = 1), ((YEAR(Calculate_Inflation_Starting_This_Day__c) - YEAR(Start_Date__c)) * Amount__c * Inflation_Rate__c) + Amount__c,
IF(((YEAR(Calculate_Inflation_Starting_This_Day__c) - YEAR(Start_Date__c)) > 1),
(((((YEAR(Calculate_Inflation_Starting_This_Day__c) - YEAR(Start_Date__c))-1) * Amount__c * Inflation_Rate__c)+ Amount__c) * (1 + Inflation_Rate__c)),
0
)
)
I have a Start Date (Start_Date__c...just like you mentioned)
...and then I'm specifying the date on which I want to calculate inflation.
The formula calculates the difference from the starting year to the specified "calculate" year and multiplies the Amount by the Inflation Rate percentage.
2018 to 2019 is only one year, so the formula calculates $103.00
When I change the Start Date to 4/20/2017 (like in your example), it calculates it as $100 * 1.03 * 1.03...or $106.09
Now, my question is...does it need to go out further than two years ?
(i.e. Amount * 1.03 * 1.03 * 1.03 if the date diff is three years)
If yes, what is the max number of years where inflation needs to be calculated ?
