Numeric Overflow Error - Workaround?

[MCuthill]

Hi Everyone,

I am working on a formula to calculate the time (Term) in months it will take to pay off a loan. I am getting a data overflow error on 1 record in my test sample, is there a way to work around this?

The formula is as follows: {@Term_Curr_Pmt}

(12*([blue]NPER[/blue](({@IntRate_Per_Payment}/100),{LN.PMTPI},-{LN.BAL})))/{LN.DIST1AF}

Where:
{@IntRate_Per_Payment} is the Interest Rate for one Period. Expressed as a whole number, example 6% is .5%/Month (when mathematically it is .06 annual and .005/month)
{LN.PMTPI} is the payment on the loan.
{LN.BAL} is the loan balance.
{LN.DIST1AF} is the Annual Frequency (# Payments / Year)

I get the Numeric Overflow on the following values:
{@IntRate_Per_Period} : 3.5
{LN.PMTPI} : $9400.00
{LN.BAL} : $248,732.54
{LN.DIST1AF} : 2

Thanks,


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[synapsevampire]

Try the following:

(12*(NPER((3.5/100),9400,-248734.54)))/2

Which is what you claim the values to be.

Works fine here resulting in:

454.44

So I'd guess that one of your fields isn't the value you think that it is.

-k

 

[MCuthill]

synapsevampire,

I tried both:

NumberVar IntRatePerPeriod:=0.035;
NumberVar PaymentAmount:=9400;
NumberVar LoanBalance:=-268732.54;
NumberVar PaymentsPerYear:=2;

ROUND((12*(NPER(IntRatePerPeriod,PaymentAmount,LoanBalance))/PaymentsPerYear),4)

AND

NumberVar IntRatePerPeriod:=ROUND(({@IntRate_per_Payment}/100),4);
NumberVar PaymentAmount:=ROUND({LN.PMTPI},2);
NumberVar LoanBalance:=ROUND((-1*{LN.BAL}),2);
NumberVar PaymentsPerYear:=ROUND({LN.DIST1AF},0);

ROUND((12*(NPER(IntRatePerPeriod,PaymentAmount,LoanBalance))/PaymentsPerYear),4)

and both cases give me a numeric overflow. I had only rounded the InterestRatePerPeriod to "2" at first but that errored on some earlier records - "4" bypassed these.

I do not know if it makes a difference, but I am using Crystal Reports 10, and Microsoft SQL Server 2000.

Thanks,


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"