+ Start a Discussion
erin ryan 8erin ryan 8 

Using Numbers, Currency, and Percentages in Formulas

I feel the directions aren't clear. Is the entire formula below belong in the interest rate field? or should there be two formula fields. Thanks 
IF(Principal__c < 10000, 0.02,

 IF(Principal__c >= 10000 &&

  Principal__c < 100000, 0.03, 0.04))

 Principal__c  *  EXP( Interest_Rate__c  * (YEAR(TODAY()) - VALUE(YearStarted)))
Amit Sinha 39Amit Sinha 39
Try this out.

IF( Principal__c < 10000, 0.02,
     IF( ( Principal__c >= 10000 &&
           Principal__c < 100000 ), 0.03, 0.04 )
) *  EXP( Interest_Rate__c  * ( YEAR(TODAY() ) - VALUE(YearStarted) ) )
James LoghryJames Loghry
Erin, to me at least, it looks like there might have been a typo in that formula.  I've reached out to the trailhead team to see if they can provide a better response and perhaps some clarity around this particular challenge.
To start, insert the field Principal in the formula editor. We want to multiply Principal by e raised to the power of the interest rate, multiplied by the number of years that the account has been open. We can do that with the EXP()function. EXP(number) returns e raised to the power of number.

For the interest rate, insert the field we made earlier, Interest_Rate__c. To find the number of years that the account has been open, subtract YearStarted, the year that the account was opened, from TODAY(), a built-in function that returns the current day. Because YearStarted is a Text value, you first have to convert it to a Number using VALUE(), which takes a Text string and returns a Number. We also use the function YEAR(), which pulls just the year from a Date, in this case the current date returned by TODAY().

You can do it all in one formula if you like, but the instructions suggest two formula fields. One as field it would be 
Principal__c  * 
    IF(Principal__c < 10000, 0.02,
    IF(Principal__c >= 10000 && Principal__c < 100000, 0.03, 0.04))  * 
  (YEAR(TODAY()) - VALUE(YearStarted))

The Time for Catter is Right MeowThe Time for Catter is Right Meow
Woah Matt Bertuzzi FTW! That's a great one.

If you decide to do it like we outline in Trailhead, you'll need a new formula field for this part:

Principal__c * EXP(Interest_Rate__c * (YEAR(TODAY()) - VALUE(YearStarted)))

I will work with the writer to make this more clear. Thanks for the feedback, and thanks James for looping us in!
Ann BeattieAnn Beattie
I am working on this same trailhead badge and I do not have a YearStarted field. The following document states that YearStarted is only available for Data.com Prospector or Data.com Clean

Even if I create a field with the API name of YearStarted, the formula still throws an error saying that YearStarted does not exist.