November 22, 2008 at 10:25 am
I don't have when the loan was originated or initiated... we are just figuring Principal Payments from Next_Payment_Date on...
I tried all the dates you all mentioned and they came out the way I expected...
Now I just discovered that this
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('FEB 29 2007' AS DATETIME),3) AS ResultDATE
gives me an error, because it doesn't exists... so I need to build some type of check to handle that...??? hmmm
SELECT * FROM
(
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('JAN 29 2007' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('JAN 30 2007' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('FEB 28 2007' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('JAN 29 2008' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('JAN 30 2008' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('FEB 29 2008' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',1,CAST('FEB 28 2009' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',5,CAST('OCT 29 2008' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',5,CAST('OCT 30 2008' AS DATETIME),3) AS TESTDATE UNION
SELECT
[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M',5,CAST('OCT 31 2008' AS DATETIME),3) AS TESTDATE
) A
ResultDATE
-----------------------
2007-02-28 00:00:00.000
2007-03-31 00:00:00.000
2008-02-29 00:00:00.000
2008-03-31 00:00:00.000
2009-03-29 00:00:00.000
2009-03-30 00:00:00.000
2009-03-31 00:00:00.000
(7 row(s) affected)
November 22, 2008 at 1:53 pm
It becomes fun.
John, can you please show the plan of payments generated by your function for a loan started on 29 Jan 2007?
Single loan, pay dates month by month.
_____________
Code for TallyGenerator
November 22, 2008 at 3:19 pm
I don't understand why the OP is even posting his code here.
His failure to answer any of the requests for information on the expected results makes it impossible to offer any help.
My guess is that he doesn't understand what the requirements are himself.
November 22, 2008 at 4:01 pm
I feel like I'm chasing a moving target myself. Everytime I think I'm close to understanding the problem, it changes (or the water gets muddier, pick your own metaphor...).
November 22, 2008 at 5:15 pm
I'll give you that...
So I took the date and ran it against jan 29 2007... what anomaly am I miss that you are trying to point out...
The functions and results are below... most should have the tally table...
FYI... I am dealing with a lot of dirty data from 4 separate sources... seems none was created with the same logic
so i have a lot of prep work to do just to get to the last part of my SP to actually do the Amortizations...
back in 30, coffee time...
Thanks,
John
DECLARE @NEXTPAYDATEDATETIME
SET@NEXTPAYDATE = CAST('JAN 29 2007' AS DATETIME)
DECLARE @CNTINT
SET@CNT= 1
SELECT
[CPMTest].[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M', 1,
DATEADD(MM, T.N, @NEXTPAYDATE) ,
3)
FROM
[Other].[dbo].[Tally] T
WHERE
T.N <= 110
Results:
-----------------------
2007-03-31 00:00:00.000
2007-04-29 00:00:00.000
2007-05-29 00:00:00.000
2007-06-29 00:00:00.000
2007-07-29 00:00:00.000
2007-08-29 00:00:00.000
2007-09-29 00:00:00.000
2007-10-29 00:00:00.000
2007-11-29 00:00:00.000
2007-12-29 00:00:00.000
2008-01-29 00:00:00.000
2008-02-29 00:00:00.000
2008-03-31 00:00:00.000
2008-04-29 00:00:00.000
2008-05-29 00:00:00.000
2008-06-29 00:00:00.000
2008-07-29 00:00:00.000
2008-08-29 00:00:00.000
2008-09-29 00:00:00.000
2008-10-29 00:00:00.000
2008-11-29 00:00:00.000
2008-12-29 00:00:00.000
2009-01-29 00:00:00.000
2009-02-28 00:00:00.000
2009-03-31 00:00:00.000
2009-04-29 00:00:00.000
2009-05-29 00:00:00.000
2009-06-29 00:00:00.000
2009-07-29 00:00:00.000
2009-08-29 00:00:00.000
2009-09-29 00:00:00.000
2009-10-29 00:00:00.000
2009-11-29 00:00:00.000
2009-12-29 00:00:00.000
2010-01-29 00:00:00.000
2010-02-28 00:00:00.000
2010-03-31 00:00:00.000
2010-04-29 00:00:00.000
2010-05-29 00:00:00.000
2010-06-29 00:00:00.000
2010-07-29 00:00:00.000
2010-08-29 00:00:00.000
2010-09-29 00:00:00.000
2010-10-29 00:00:00.000
2010-11-29 00:00:00.000
2010-12-29 00:00:00.000
2011-01-29 00:00:00.000
2011-02-28 00:00:00.000
2011-03-31 00:00:00.000
2011-04-29 00:00:00.000
2011-05-29 00:00:00.000
2011-06-29 00:00:00.000
2011-07-29 00:00:00.000
2011-08-29 00:00:00.000
2011-09-29 00:00:00.000
2011-10-29 00:00:00.000
2011-11-29 00:00:00.000
2011-12-29 00:00:00.000
2012-01-29 00:00:00.000
2012-02-29 00:00:00.000
2012-03-31 00:00:00.000
2012-04-29 00:00:00.000
2012-05-29 00:00:00.000
2012-06-29 00:00:00.000
2012-07-29 00:00:00.000
2012-08-29 00:00:00.000
2012-09-29 00:00:00.000
2012-10-29 00:00:00.000
2012-11-29 00:00:00.000
2012-12-29 00:00:00.000
2013-01-29 00:00:00.000
2013-02-28 00:00:00.000
2013-03-31 00:00:00.000
2013-04-29 00:00:00.000
2013-05-29 00:00:00.000
2013-06-29 00:00:00.000
2013-07-29 00:00:00.000
2013-08-29 00:00:00.000
2013-09-29 00:00:00.000
2013-10-29 00:00:00.000
2013-11-29 00:00:00.000
2013-12-29 00:00:00.000
2014-01-29 00:00:00.000
2014-02-28 00:00:00.000
2014-03-31 00:00:00.000
2014-04-29 00:00:00.000
2014-05-29 00:00:00.000
2014-06-29 00:00:00.000
2014-07-29 00:00:00.000
2014-08-29 00:00:00.000
2014-09-29 00:00:00.000
2014-10-29 00:00:00.000
2014-11-29 00:00:00.000
2014-12-29 00:00:00.000
2015-01-29 00:00:00.000
2015-02-28 00:00:00.000
2015-03-31 00:00:00.000
2015-04-29 00:00:00.000
2015-05-29 00:00:00.000
2015-06-29 00:00:00.000
2015-07-29 00:00:00.000
2015-08-29 00:00:00.000
2015-09-29 00:00:00.000
2015-10-29 00:00:00.000
2015-11-29 00:00:00.000
2015-12-29 00:00:00.000
2016-01-29 00:00:00.000
2016-02-29 00:00:00.000
2016-03-31 00:00:00.000
2016-04-29 00:00:00.000
(110 row(s) affected)
ALTER FUNCTION [dbo].[fn_AM_CALC_NEXT_PAY_DATE]
(
-- Add the parameters for the function here
@PMT_FREQ_MULTCHAR(1),
@PMT_FREQINT,
@NEXT_PAY_DATEDATETIME,
@RECCNTINT
)
RETURNSDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @dRetValDATETIME
DECLARE @dEVALDATEDATETIME
-- Add the T-SQL statements to compute the return value here
IF @RECCNT = 1
BEGIN
SET @dRetVal = @NEXT_PAY_DATE
GOTO OK
END
-- SEE IF THIS DATE IS THE LAST DAY OF MONTH, IF SO THEN COMPARE IF IS THEN RETURN LAST DAY...
IF @PMT_FREQ_MULT <> 'D'
BEGIN
SET @dEVALDATE =[dbo].[fn_AM_IsLastDay](@NEXT_PAY_DATE)
IF @NEXT_PAY_DATE = @dEVALDATE
BEGIN
SET @dRetVal=
(
CASE @PMT_FREQ_MULT
WHEN 'M' THEN [dbo].[fn_AM_IsLastDay](DATEADD(MONTH, @PMT_FREQ, @dEVALDATE))
WHEN 'Y' THEN [dbo].[fn_AM_IsLastDay](DATEADD(YEAR, @PMT_FREQ, @dEVALDATE))
END
)
SET @dEVALDATE=[dbo].[fn_AM_IsLastDay](@dRetVal)
END
ELSE
BEGIN
SET @dRetVal=
(
CASE @PMT_FREQ_MULT
WHEN 'M' THEN DATEADD(MONTH, @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'Y' THEN DATEADD(YEAR ,@PMT_FREQ, @NEXT_PAY_DATE)
END
)
END
END
ELSE
BEGIN
SET @dRetVal=
(
CASE @PMT_FREQ_MULT
WHEN 'M' THEN DATEADD(MONTH, @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'D' THEN DATEADD(DAY , @PMT_FREQ, @NEXT_PAY_DATE)
WHEN 'Y' THEN DATEADD(YEAR ,@PMT_FREQ, @NEXT_PAY_DATE)
END
)
END
OK:
-- Return the result of the function
RETURN @dRetVal
END
ALTER FUNCTION [dbo].[fn_AM_IsLastDay]
(
-- Add the parameters for the function here
@dEVALDATEDATETIME
)
RETURNSDATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @dRetValDATETIME
-- Add the T-SQL statements to compute the return value here
--SET @dRetVal= DATEADD(M, DATEDIFF(M,0,@dEVALDATE)+1,0)
SET @dRetVal= DATEADD(HH,-24,DATEADD(M, DATEDIFF(M,0,@dEVALDATE)+1,0))
-- Return the result of the function
RETURN @dRetVal
END
November 22, 2008 at 5:51 pm
What does this code
DECLARE @NEXTPAYDATEDATETIME
SET@NEXTPAYDATE = CAST('JAN 29 2007' AS DATETIME)
DECLARE @CNTINT
SET@CNT= 1
SELECT
[CPMTest].[dbo].[fn_AM_CALC_NEXT_PAY_DATE]('M', 1,
DATEADD(MM, T.N, @NEXTPAYDATE) ,
3)
FROM
[Other].[dbo].[Tally] T
WHERE
T.N <= 110
has to do with the task as you described it?
It's actually an approach I suggested: take initial date and add number of periods.
You should be doing something like this:
DECLARE @PAYDATE DATETIME, @N int
SET@PAYDATE = CAST('JAN 29 2007' AS DATETIME)
SET @N = 1
WHILE @N < 110
BEGIN
SELECT [CPMTest].[dbo].[fn_AM_CALC_NEXT_PAY_DATE](@PAYDATE)
SET @PAYDATE = [CPMTest].[dbo].[fn_AM_CALC_NEXT_PAY_DATE](@PAYDATE)
SET @N = @N + 1
END
And even with correct approach your function fails.
Because for me these results:
2008-02-29 00:00:00.000
2008-03-31 00:00:00.000
2008-04-29 00:00:00.000
.....
look inappropriate.
If you replace your function in the code you posted with DATEADD you'll find it works much better.
And please, don't post the code of you function.
So far you fail to understand the problem.
And chaotic typing won't help you any way.
_____________
Code for TallyGenerator
November 23, 2008 at 10:49 pm
Here's the question you haven't answered. You have a loan with a monthly payment, the first payment is due on January 30th, when is the March payment due, since your previous payment for that calculation will be either February 28th (for a normal year) or February 29th (for a leap year)? Based on your formula, it would have to be either March 28th or March 29th. Without knowing the first payment date, you can't accurately compute future payment dates.
Are getting the picture yet on what we are trying to figure out?
November 24, 2008 at 12:11 am
Maybe I'm missing something, but, for the record... I've never heard of a loan being specifically for "the last day of the month". I've seen loan dates of, for example, Jan 29, 30, and 31 and the way they're handled is that if a month has one of those dates during the year, then that date is used. If not the largest day of the month available in a month is used.
The idea of using the Tally table to generate dates is a good one, provided that, as Sergiy says, you add the number of periods to the first date instead of trying to add 1 to a previous date.
DateAdd will take care of the rest properly and, I've gotta tell ya, 170,000 loans over 4 million rows is nothing for size. "Running" formula's like the running balance of a loan are quickly and easily done using the same update principle found in the following article...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 6:36 am
Of course I am doing it that way Jeff... none other would do... it takes about 15 minutes to do the Approx. 4MILL rows...
I think I am getting it... basically don't compare my Next_Payment_Date with Maturity_Date, but rather compare My very beggining (row1) Last_Payment_Date with the Maturity_Date... thus if the loan was for 15 months i would do DATEADD(MM, 15, Last_Payment_Date)... Rather than (row14) DATEADD(MM, 15, Previous_Payment_Date)...
As well don't figure out my Next_Payment_Date from the Previous_Payment_Date, rather figure out my next payment date by the month or frequency I am on... so if I am on month 11 of 15, then I would go DATEADD(MM, 11, Last_Payment_Date) (row1 date)
November 25, 2008 at 9:48 am
Sounds like you need to establish exactly what it means to have a date "one month from a given date", and while you're method for x number of days from a given date is fine, you'll also need to define exactly what it means to have a date "one year from a given date". Our calendar is where the grief is, as the difference in length (in days) for each month causes the month problem, as adding 1 month to Jan 31st with DATEADD gives you 2/28, or 2/29 in a leap year, but so does adding 1 month to Jan 30th. You also get 11/30 with adding 1 month to 10/31 or to 10/30. Similarly, you have the exact same problem for any two months where the previous month is longer than the current month. For the year problem, you have to concern yourself with adding a year to 2/29 and getting 2/28.
For calculation of daily interest whenever there's a daily component over the entire time period, you'll end up needing the total difference in days between the beginning of the period and the end thereof. This means that amortization calculations will not involve a constant value for principal or interest month to month, despite a fixed interest rate.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 25, 2008 at 7:22 pm
This sounds like a problem that the OP's client (or business partner if in the same company) should be involved with. I'd be very leery of letting a programmer decide the rules for determining a payment date. Let the programmer invent the HOW for that calculation, but the WHAT has to come from the business. It'll be something in the loan promissary notes -- legal documents that define the conditions of repayment. Programming expediency has zero weight against what's in those contracts.
JohnSteinbeck, please do consult with your client and get an answer to the basic questions that have been asked. I'm sure that you'll find, as Jeff and others have suggested, you really need an anchor point upon which to base the payment schedule. If the first payment date isn't recorded, then perhaps the loan origination date and the maturity date could be used to derive that rather essential detail.
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply