Error Trapping within a Crystal Formula

[narlyjoe]

I have a simple formula that uses the Nper function to calc the remaining term (in months) on a database of several hundred commercial loans. The intent is to compare the results of the Nper formula to the actual source system data values and isolate loans with improper interest rates, payment amounts etc.

Example:
NPer ({MORTGAGE.INTRATE}/12,-{MORTGAGE.P&IPMTAMT},{MORTGAGE.CURPRINBAL})

The problem: on some loan records, the Nper function is causing a 'numeric overflow' error. This occurs on records where the loan rate, principal, and payment return an infinite number for Nper. In other words, they won't ever amortize. I need the formula to somehow evaluate to 999 or something to identify these records, instead of returning the error.

Solution? I've been trying to find a way to do a On Error Goto type of branching formula, but it doesn't appear Crystal supports that syntax, even using Basic Syntax in the Formula Editor.

Am I missing a simple solution here? Any other ideas on error trapping in Crystal formulas?

 

[synapsevampire]

You should isolate the condition which causes the error and address that.

Providing example data and expected output with your posts generally results in accurate fast answers.

In this example, if the table contains a zero in the{MORTGAGE.P&IPMTAMT}, then I can see where the formula might fail.

Try checking for nulls and zeros, as in:

if not(isnull({MORTGAGE.P&IPMTAMT}))
and
{MORTGAGE.P&IPMTAMT} > 0
and
not(isnull({MORTGAGE.CURPRINBAL}))
and
{MORTGAGE.CURPRINBAL} > 0
then
NPer ({MORTGAGE.INTRATE}/12,-{MORTGAGE.P&IPMTAMT},{MORTGAGE.CURPRINBAL})
else
999

-k

 

[narlyjoe]

Thanks for the reply. I didn't put the complete version of the formula in my original post, sorry. I am already doing the checking you describe, making sure there are no nulls or zeros. Those don't seem to be causing the problem. Here's an example that does generate the numeric overflow error:

NPer ({MORTGAGE.INTRATE}/12,-{MORTGAGE.P&IPMTAMT},{MORTGAGE.CURPRINBAL})

MORTGAGE.INTRATE = .07
MORTGAGE.P&IPMTAMT = -2310.00
MORTGAGE.CURPRINBAL = 586851

The error happens because a loan with these terms will never amortize fully. Hence the Nper formula can't return a valid result.

Really think I need to do an 'on error' kind of thing to trap the error and isolate these type of records. Haven't been able to come up with the right syntax in Crystal. Maybe it simply can't be done.

 

[chelseatech]

The only solution i can see is to replace your NPer function with an equivalent function written in VB inside a User Function Library. You can use On Error to return a value of 0 or -1 (or some other invalid period) if an error occurs.

If you can write the function in VB, you can compile it into a UFL - there is a presentation on my web site telling you how to do that.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[tektipdjango]

the remaining term is greater than :
minTerm=MORTGAGE.CURPRINBAL/MORTGAGE.P&IPMTAMT
So you can check for minterm>500 if for instance your remaining term cannot be greater than 500 monthes.

In your sample the MORTGAGE.P&IPMTAMT is less than 0 explaining the error

so test (assuming you have alredy test for null and zeroes) like sinapse said and add the condition:

if MORTGAGE.CURPRINBAL/MORTGAGE.P&IPMTAMT>500 then 999 else NPER...

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[narlyjoe]

Thanks for both replies. Good ideas. I think testing for a minimum term will work, for now. One of those things I should have done right away...

I'll also check into getting a user function compiled. We are a financial institution and would use such a function often.