MS-SQL Sprocs - looping,case,output parameters and 12 cents

[Lyle Fairfield]

The stored procedure script below is an example of how looping, case
statements and output parameters can be used in MS-SQL stored
procedures to accomplish things for which we may have had to use VBA
code, or automation, in the JET world. Some who have not yet worked with
MS-SQL may be interested.
The script is indented in reality but I have aligned it all left to make
reading easier, (I hope).

The Sproc takes an Account ID, an Interest Rate, and a Date, and
calculates the monthly interest for the month of the date, taking into
account daily transactions if any, and summing the daily interest
calculated at (1+apr^(1/365))-1 ... sometimes 366. It calculates the
interest for each day of the month (including the interest on the
interest) and adds them up, all within MS-SQL. It's quite fast, doing a
few dozen accounts in a couple of seconds.

But I do not post only to be informative. I post because the value the
Sproc returns is slightly off the value returned when I try to run an
identical model in Excel, or when I try to run an identical model in
ASP-JScript. For instance Excel and ASP report January's interest for
Account 21 is $536.35; this MS-SQL Sproc says $536.23.

The difference of $0.12 is not a problem in that I could decide to use
one or the other method and as long as I am consistent in doing so, this
is acceptable.

The crux of the problem is that I am the youngest of 3 siblings and must
be perfect. And this is not perfect. As I have three methods and two
give me the same answer I am assuming the third (this one) is in error.

But where? I have checked all the type declarations. They seem to be
consistent. I have checked the days scanned. They seem to be identical.
I know MS-SQL can be very idiosyncratic (read nuts) in its type
coercions but I can't see any where that this could be a problem as I
have for the most part made explicit conversions.

Can you see a problem?

ALTER PROCEDURE sp4060148Interest
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest smallmoney OUTPUT,
@GetAccountName varchar(250) OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestRate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest float
DECLARE @Today smalldatetime

SET @EndDate=DATEADD(year,1,@BeginDate)
SET @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
SET @APRPlusOne=@APR+1
SET @DaysinYearReciprocal=1.0000000000/@DaysinYear
SET @DailyInterestRate=POWER(@APRPlusOne,@DaysinYearRe ciprocal)-1
SET @Today=DATEADD(dayofyear,-DAY(@BeginDate)+1,@BeginDate)
SET @MonthlyInterest=0

WHILE MONTH(@Today) = MONTH(@BeginDate) AND YEAR(@Today)=YEAR(@BeginDate)
BEGIN
SELECT
@MonthlyInterest=@MonthlyInterest+(SUM(sq.Amount)+ @MonthlyInterest)*@DailyInterestRate
FROM
(
SELECT
CASE WHEN t.DebitAccountID=@AccountID THEN -t.TotalAmount ELSE
t.TotalAmount END AS Amount
FROM
[4060148Transactions] t
WHERE
(
t.DebitAccountID=@AccountID
OR
t.CreditAccountID=@AccountID
)
AND
t.Date<@Today
)
AS
sq

SET @Today=DATEADD(day,1,@Today)
CONTINUE
END
SET @GetMonthlyInterest = CAST(@MonthlyInterest AS smallmoney)
RETURN

(Yes I know I have a redundant Parameter but it is not doing any harm
and I have someone travelling in Europe right now who feeds this Sproc 5
parameters and I think I will wait until I can change that before I
remove the extra parameter.)

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Terry Kreft]

Lyle,
Try replacing your floats with decimals

--
Terry Kreft
MVP Microsoft Access
"Lyle Fairfield" <ly******@yahoo.ca> wrote in message
news:2p****************@read1.cgocable.net...
<SNIP>

Sproc returns is slightly off the value returned when I try to run an
identical model in Excel, or when I try to run an identical model in
ASP-JScript. For instance Excel and ASP report January's interest for
Account 21 is $536.35; this MS-SQL Sproc says $536.23.

The difference of $0.12 is not a problem in that I could decide to use
one or the other method and as long as I am consistent in doing so, this
is acceptable. <SNIP>
--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[Lyle Fairfield]

Terry Kreft wrote:

Lyle,
Try replacing your floats with decimals

Thanks for looking at this, Terry. I believe Excel uses the IEEE Double
Precision 64 bit representation of numbers by default.

To approximate this in T-SQL I have tried both
float
and
decimal(38,18)

Unfortunately, it seems that the outcome of the calculation of interest
is identical, regardless of which I use.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[billmiami2]

Take a look at your calculation of the daily interest rate. I would
think that it would simply be

@APR*@DaysinYearReciprocal

Which is mathematically

r/n

where r is the annual rate and n is the number of periods (365 or 366).

You are showing it as

[1+r]^(1/n)

I'm not familiar with this expression.

If I use a .05% APR with your method, I get an effective daily interest
rate of

0.000133681

which is not the same as

0.000136986 which is what I think you should have with daily compound
interest at 5%.

Bill E.
Hollywood

[Lyle Fairfield]

bi********@netscape.net wrote:

Take a look at your calculation of the daily interest rate. I would
think that it would simply be

@APR*@DaysinYearReciprocal

Which is mathematically

r/n

where r is the annual rate and n is the number of periods (365 or 366).

You are showing it as

[1+r]^(1/n)

I'm not familiar with this expression.

If I use a .05% APR with your method, I get an effective daily interest
rate of

0.000133681

which is not the same as

0.000136986 which is what I think you should have with daily compound
interest at 5%.

Bill E.
Hollywood

Suppose the APR is 8%

0.08/365 =0.00021917808219178082191780821917808

If you apply this and compound it every day for a year the effective APR is

1.00021917808219178082191780821917808 ^ 365

=

1.0832775717928069729659276566459.

which is nice work if you can get it, but it is not an APR of 8%.

The formula I gave is correct except in the world of unscrupulous and
dishonest business, which is close to but not all the world of business.
--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[billmiami2]

Lyle,

That is precisely the outcome you should have for interest compounded
daily at and 8% annual rate -- you should get more than 8%. This
converges to

e^(rt)=1.083287...

or about 8.329% as you approach instantaneous (or continuous)
compounding.

Perhaps APR is interpreted differently from the "r" that I'm
accustomed to seeing in a mortgage calculation. Perhaps it's the rate
at which one would end up with an 8% return when daily compounding is
applied, which would be the equivalent of an "r" of about 7.696%. If
that's true, then the APR is a "watered down" interpretation of an
annual interest rate. If the bank calculated my mortgage interest that
way, I'd save some money.

Bill

[Lyle Fairfield]

bi********@netscape.net wrote:

Lyle,

That is precisely the outcome you should have for interest compounded
daily at and 8% annual rate -- you should get more than 8%. This
converges to

e^(rt)=1.083287...

or about 8.329% as you approach instantaneous (or continuous)
compounding.

Perhaps APR is interpreted differently from the "r" that I'm
accustomed to seeing in a mortgage calculation. Perhaps it's the rate
at which one would end up with an 8% return when daily compounding is
applied, which would be the equivalent of an "r" of about 7.696%. If
that's true, then the APR is a "watered down" interpretation of an
annual interest rate. If the bank calculated my mortgage interest that
way, I'd save some money.

Yes, if banks were honest we would all save money.

If I lend you money at a rate of 8% a year, then, after a year you will
pay, or have paid me (or owe even) 8%.
I am not a bank. I am honest. Sorry, I see that I have repeated myself.

The Canadian Housing and Mortgage Corporation used to, and perhaps still
does, require that mortgages be compounded semianually, (even when
payments were made monthly), to minimize the effect of the simple
calculation of interest as you describe it.

--
--
Lyle

"The aim of those who try to control thought is always the same. They
find one single explanation of the world, one system of thought and
action that will (they believe) cover everything; and then they try to
impose that on all thinking people."
- Gilbert Highet

[billmiami2]

Lyle,

I'm not sure I consider this dishonestly. Perhaps it's because I've
been taught that compounding gives you an effective interest rate
that's higher than the stated annual rate. Therefore, when a bank
charges me a 6% rate for a loan, I expect to be paying an effective
rate (or APR, I suppose) of about 6.17% due to monthly compounding. I
guess in Canada, you would get the same loan for the bargain price of
only 6.09%!

I made two tiny changes to your procedure to specify
@GetMonthlyInterest and @MonthlyInterest as a decimal types and was
able to match results in MS Excel with excellent precision. Note that
I replaced your SQL statement with a fixed value of $100.00. I placed
my code below. Also, I think that you could throw away the iterative
approach altogether by calculating the future value of the incremental
daily cash flows and summing them. The first cash flow would be the
balance in the account at the beginning of day 1 of the month, for
which interest would be compounded at your daily rate for the number of
days in the month. The second cash flow would be the total additional
debits/credits made on day 1, compounded at your daily rate for the
number of days in the month minus one, and so on.

Bill E.

_____________
Declare @MonthlyInterest decimal(11,10)
exec sp4060148Interest 0, .08, '2/1/2005', @MonthlyInterest output
Print Cast(@MonthlyInterest as varchar)

-----------

ALTER PROCEDURE sp4060148Interest
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest Decimal(16,10) OUTPUT--,
--@GetAccountName varchar(250) OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestRate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest decimal(16,10)
DECLARE @Today smalldatetime

SET @EndDate=DATEADD(year,1,@BeginDate)
SET @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
SET @APRPlusOne=@APR+1
SET @DaysinYearReciprocal=1.0000000000/@DaysinYear
SET @DailyInterestRate=POWER(@APRPlusOne,@DaysinYearRe ciprocal)-1
SET @Today=DATEADD(dayofyear,-DAY(@BeginDate)+1,@BeginDate)
SET @MonthlyInterest=0

WHILE MONTH(@Today) = MONTH(@BeginDate) AND
YEAR(@Today)=YEAR(@BeginDate)
BEGIN
SELECT
@MonthlyInterest=@MonthlyInterest+(100.00000000+@M onthlyInterest)*@DailyInterestRate
--Print cast(@MonthlyInterest as varchar)

SET @Today=DATEADD(day,1,@Today)
CONTINUE
END
Declare @reaTemp real
Set @GetMonthlyInterest =@MonthlyInterest
RETURN

[Lyle Fairfield]

bi********@netscape.net wrote:

Also, I think that you could throw away the iterative
approach altogether by calculating the future value of the incremental
daily cash flows and summing them.
I would be interested in learning more about how you would do this.

***********
The first cash flow would be the
balance in the account at the beginning of day 1 of the month, for
which interest would be compounded at your daily rate for the number of
days in the month.

This was the basis for my error. In entering last month's interest I was
using the first day of this month as the date. This meant that interest
was not calculated properly for that day. When I changed this date to
the last day of the month for which the interest was calculated, the
correct answer was returned.

***********

Thanks for your interest and help with this matter, Bill. As a result of
working with the problem I think I have a more efficient SPROC (as
below), but I'd like to see how you would apply future value to the
question.

********** revised SPROC **********

ALTER PROCEDURE sp4060148Interest
@AccountID int=0,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest smallmoney OUTPUT
AS
DECLARE @APRPlusOne float
DECLARE @DailyInterestRate float
DECLARE @DaysinYear smallint
DECLARE @DaysinYearReciprocal float
DECLARE @EndDate smalldatetime
DECLARE @MonthlyInterest float
DECLARE @Today smalldatetime

SET @EndDate=DATEADD(year,1,@BeginDate)
SET @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
SET @APRPlusOne=@APR+1
SET @DaysinYearReciprocal=1.0000000000/@DaysinYear
SET @DailyInterestRate=POWER(@APRPlusOne,@DaysinYearRe ciprocal)-1
SET @Today=@BeginDate
SET @MonthlyInterest=0

WHILE MONTH(@Today) = MONTH(@BeginDate) AND YEAR(@Today)=YEAR(@BeginDate)
Begin
SELECT
@MonthlyInterest=@MonthlyInterest+(sq.Amount+@Mont hlyInterest)*@DailyInterestRate
FROM
(
SELECT SUM(CASE WHEN t.DebitAccountID=@AccountID THEN -t.TotalAmount
ELSE t.TotalAmount END) AS Amount
FROM [4060148Transactions] t
WHERE (t.DebitAccountID=@AccountID OR t.CreditAccountID=@AccountID)
AND t.[Date]<@Today
)
sq
SET @Today=DATEADD(day,1,@Today)
CONTINUE
End
SET @GetMonthlyInterest = CAST(@MonthlyInterest AS smallmoney)
Return


--
--
Lyle

[billmiami2]

Lyle,

Here is an example for you using a cash flow method. Because I wasn't
sure what your transaction table contained, I created my own
transaction table.

My transaction table is as follows:

----------------------------------------------------
CREATE TABLE [tblTransactions] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [int] NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[Amount] [money] NOT NULL ,
CONSTRAINT [PK_tblTransactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------------------------
Note that the TransactionID column is completely unnecessary but I put
it in out of habit.

My sample data for this table is as follows:
-----------------------------------------------------

ID Account Date Amount
1 1 12/4/2004 50
2 1 12/30/2004 50
3 1 1/10/2005 2:00:00 PM 100
4 1 1/10/2005 3:00:00 PM -50
5 1 1/25/2005 7:00:00 AM 100
6 1 2/1/2005 6:00:00 PM 50
-----------------------------------------------------

The table valued function fnDateRangeToTable makes it easy to calculate
daily cash flows for all days of the month in one query without
iteration. The function itself contains a loop, but there is almost no
cost to this. It is as follows:
---------------------------------------------
CREATE FUNCTION dbo.fnDateRangeToTable
--Loop through a range of dates to create a table
(
@dtsStartDate smalldatetime,
@dtsEndDate smalldatetime
)
RETURNS @tblResult TABLE (Date smalldatetime)
AS
BEGIN
Declare @dtsDate smalldatetime
set @dtsDate=@dtsStartDate

While @dtsDate<=@dtsEndDate
Begin
--Insert the new record
INSERT INTO @tblResult SELECT @dtsDate
--increment the date
Set @dtsDate=dateadd(dd,1,@dtsDate)
End

RETURN
END

---------------------------------------------
And finally, my procedure is as follows:

---------------------------------------------

CREATE PROCEDURE dbo.spCalculateInterestForMonth
--Use cash flow method to calculate interest
@AccountID int=1,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest float output
AS
set nocount on

--Declare variables
Declare @FirstDayMonth smalldatetime
Declare @FirstDayNextMonth smalldatetime
Declare @LastDayMonth smalldatetime
Declare @DaysInMonth smallint
Declare @APRPlusOne float
Declare @DailyInterestRatePlusOne float
Declare @DaysinYear smallint
Declare @DaysinYearReciprocal float
Declare @EndDate smalldatetime
Declare @MonthlyInterest float

Set @EndDate=DATEADD(year,1,@BeginDate)
Set @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
Set @APRPlusOne=@APR+1
Set @DaysinYearReciprocal=1.0000000000/@DaysinYear
Set @DailyInterestRatePlusOne=POWER(@APRPlusOne,@Daysi nYearReciprocal)
Set @FirstDayMonth=DATEADD(dayofyear,-DAY(@BeginDate)+1,@BeginDate)
Set @FirstDayNextMonth=DateAdd(m,1,@FirstDayMonth)
Set @LastDayMonth=DateAdd(d, -1, @FirstDayNextMonth)
Set @DaysInMonth=DateDiff(d, @FirstDayMonth, @FirstDayNextMonth)
Set @MonthlyInterest=0

--Calculate the future value of the balance as of the beginning of the
first day of the month
--less the nominal value to get the interest on the beginning balance.
SELECT
@MonthlyInterest=Sum(Amount)*(Power(@DailyInterest RatePlusOne,@DaysInMonth)-1)
FROM tblTransactions
WHERE Date < @FirstDayMonth

--Sum the future values of the incremental daily cash flows and
subtract the nominal value
--to get the interest on the incremental flows. Add this to the
interest on the beginning balance.
SELECT
@MonthlyInterest=@MonthlyInterest+Sum(FV.FutureVal ue)-Sum(FV.CashFlow)
FROM
--subquery to calculate future values for daily incremental cash
flows
(SELECT CF.Date, CF.CashFlow,
CF.CashFlow*Power(@DailyInterestRatePlusOne,@DaysI nMonth+1-Day(CF.Date))
AS FutureValue
FROM
--subquery to get daily incremental cash flows
(SELECT D.Date, Sum(Amount) AS CashFlow
FROM tblTransactions AS T INNER JOIN
dbo.fnDateRangeToTable(@FirstDayMonth, @LastDayMonth) AS D
ON T.Date >= D.Date AND T.Date < DateAdd(d,1,D.Date)
WHERE AccountID=@AccountID
GROUP BY D.Date) AS CF) AS FV

Set @GetMonthlyInterest = @MonthlyInterest
RETURN
--------------------------------------------------
Testing the procedure for January, 2005 at an 8% APR gives me
Declare @Interest float
exec spCalculateInterestForMonth 1, .08, '2/1/2005', @Interest output
select @Interest as Result

Which returns 1.035964279819126

If you take my three transactions for January and set their amounts to
zero, you get

0.65578261878596056

Which is what you should earn in interest on a $100 balance as of
1/1/05 compounded over 31 days.

Bill

[Lyle Fairfield]

bi********@netscape.net wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

Lyle,

Here is an example for you using a cash flow method. Because I wasn't
sure what your transaction table contained, I created my own
transaction table.

My transaction table is as follows:

----------------------------------------------------
CREATE TABLE [tblTransactions] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [int] NOT NULL ,
[Date] [smalldatetime] NOT NULL ,
[Amount] [money] NOT NULL ,
CONSTRAINT [PK_tblTransactions] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------------------------
Note that the TransactionID column is completely unnecessary but I put
it in out of habit.

My sample data for this table is as follows:
-----------------------------------------------------

ID Account Date Amount
1 1 12/4/2004 50
2 1 12/30/2004 50
3 1 1/10/2005 2:00:00 PM 100
4 1 1/10/2005 3:00:00 PM -50
5 1 1/25/2005 7:00:00 AM 100
6 1 2/1/2005 6:00:00 PM 50
-----------------------------------------------------

The table valued function fnDateRangeToTable makes it easy to calculate
daily cash flows for all days of the month in one query without
iteration. The function itself contains a loop, but there is almost no
cost to this. It is as follows:
---------------------------------------------
CREATE FUNCTION dbo.fnDateRangeToTable
--Loop through a range of dates to create a table
(
@dtsStartDate smalldatetime,
@dtsEndDate smalldatetime
)
RETURNS @tblResult TABLE (Date smalldatetime)
AS
BEGIN
Declare @dtsDate smalldatetime
set @dtsDate=@dtsStartDate

While @dtsDate<=@dtsEndDate
Begin
--Insert the new record
INSERT INTO @tblResult SELECT @dtsDate
--increment the date
Set @dtsDate=dateadd(dd,1,@dtsDate)
End

RETURN
END

---------------------------------------------
And finally, my procedure is as follows:

---------------------------------------------

CREATE PROCEDURE dbo.spCalculateInterestForMonth
--Use cash flow method to calculate interest
@AccountID int=1,
@APR float=0,
@BeginDate smalldatetime,
@GetMonthlyInterest float output
AS
set nocount on

--Declare variables
Declare @FirstDayMonth smalldatetime
Declare @FirstDayNextMonth smalldatetime
Declare @LastDayMonth smalldatetime
Declare @DaysInMonth smallint
Declare @APRPlusOne float
Declare @DailyInterestRatePlusOne float
Declare @DaysinYear smallint
Declare @DaysinYearReciprocal float
Declare @EndDate smalldatetime
Declare @MonthlyInterest float

Set @EndDate=DATEADD(year,1,@BeginDate)
Set @DaysinYear=DATEDIFF(dayofyear,@BeginDate,@EndDate )
Set @APRPlusOne=@APR+1
Set @DaysinYearReciprocal=1.0000000000/@DaysinYear
Set
@DailyInterestRatePlusOne=POWER(@APRPlusOne,@Daysi nYearReciprocal)
Set @FirstDayMonth=DATEADD(dayofyear,-DAY(@BeginDate)+1,@BeginDate)
Set @FirstDayNextMonth=DateAdd(m,1,@FirstDayMonth)
Set @LastDayMonth=DateAdd(d, -1, @FirstDayNextMonth)
Set @DaysInMonth=DateDiff(d, @FirstDayMonth, @FirstDayNextMonth)
Set @MonthlyInterest=0

--Calculate the future value of the balance as of the beginning of
the
first day of the month
--less the nominal value to get the interest on the beginning
balance. SELECT
@MonthlyInterest=Sum(Amount)*(Power(@DailyInterest RatePlusOne,@DaysInMont
h)-1)
FROM tblTransactions
WHERE Date < @FirstDayMonth

--Sum the future values of the incremental daily cash flows and
subtract the nominal value
--to get the interest on the incremental flows. Add this to the
interest on the beginning balance.
SELECT
@MonthlyInterest=@MonthlyInterest+Sum(FV.FutureVal ue)-Sum(FV.CashFlow)
FROM
--subquery to calculate future values for daily
incremental cash
flows
(SELECT CF.Date, CF.CashFlow,
CF.CashFlow*Power(@DailyInterestRatePlusOne,@DaysI nMonth+1-Day(CF.Date))
AS FutureValue
FROM
--subquery to get daily incremental cash flows
(SELECT D.Date, Sum(Amount) AS CashFlow
FROM tblTransactions AS T INNER JOIN
dbo.fnDateRangeToTable(@FirstDayMonth, @LastDayMonth) AS D
ON T.Date >= D.Date AND T.Date <
DateAdd(d,1,D.Date)
WHERE AccountID=@AccountID
GROUP BY D.Date) AS CF) AS FV

Set @GetMonthlyInterest = @MonthlyInterest
RETURN
--------------------------------------------------
Testing the procedure for January, 2005 at an 8% APR gives me
Declare @Interest float
exec spCalculateInterestForMonth 1, .08, '2/1/2005', @Interest output
select @Interest as Result

Which returns 1.035964279819126

If you take my three transactions for January and set their amounts to
zero, you get

0.65578261878596056

Which is what you should earn in interest on a $100 balance as of
1/1/05 compounded over 31 days.

Bill

Thanks Bill

This is very sophisticated but I will have to study it a lot more before I
undesrtand it fully.

While I know the table creation of tblResult is just the return of a UDF,
and not very demanding at all, I will have to persuade myself before I
create any unnecessary objects in the db. I try never to do this.

If I were serious in making this the most efficient possible I think I
would try remove the date incrementing part of my code and recalculate when
a payment or charge has been made.

I would then get something like

http://www.ffdba.com/4060148/loan.asp
( I am still using a daily rate of
(1+apr)^(1/365)-1
on this page.)

Thanks again for your interest and help.

--
Lyle

"The aim of those who try to control thought is always the same. They find
one single explanation of the world, one system of thought and action that
will (they believe) cover everything; and then they try to impose that on
all thinking people."
- Gilbert Highet

[billmiami2]

My pleasure Lyle. It got me thinking and that's good, even if it was
only worth 12 cents!

If you like, you can always place the UDF code directly into the stored
procedure as opposed to creating a new object. I created it as a UDF
because it's something that could potentially be used in other
procedures.

I think we've beaten this one up enough!

Bill