Amortization Loan Statement in SSRS

Last Post 17 Jul 2012 01:48 PM by ANDREY KALASHNIK. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
devo983
New Member
New Member

--
19 Apr 2009 07:09 PM
Hello,

I am trying to find a good resource or sample code for generating monthly loan statements in SSRS based off of information in T-SQL tables (such a loan amount, interest rate, taxes, insurance, due date, etc.). I have a query to summarize an entire amortization schedule if the borrower makes all payments on time, but I have no dynamic way of showing a daily balance based off of compounding interest taking into account how much the borrower has paid or has not paid (which may be greater than or less than their monthly payment amount). I also want the functionality to add a Late Fee to the principal balance if the payment received date is after 10 days of their due date. All of these loans have the same terms and I don't want to spend a lot of money buying another piece of software to be able to pull of the transactions being stored in SQL. Any suggestions for a good, cost-effective way to enable SSRS the run these calculations and generate monthly statements with that information?

Thanks in advance
sqllgarrett
New Member
New Member

--
23 Jul 2009 03:29 PM
I need these documents too, very interested, thanks.
jwright237
New Member
New Member

--
21 Aug 2009 07:24 AM
This should get you started. The last payment is off a little if the numbers are not round. You will have to add the future or present value into the mix.:


/*** Loan Calculation Variables ***/
DECLARE
@InterestRate FLOAT,

@Loan FLOAT,

@AmoritizationMonths FLOAT,

@Payment FLOAT,

@Period FLOAT,

/*** CALCULATED PROCEDURAL VARIABLES ***/

@Payment2 FLOAT,

@TotalPayment FLOAT,

@FinanceCharges FLOAT,

@CompoundingPeriod FLOAT,

@CompoundingInterest FLOAT,

/*** CALCULATED LOAN VARIABLES ***/

@CurrentBalance FLOAT,

@Principal FLOAT,

@Interest FLOAT,

/*** MORTGAGE TIME VARIABLES ***/

@MortgageStartDate SMALLDATETIME,

@MortgageEndDate SMALLDATETIME,

@MortgagePayDate SMALLDATETIME,

@MortgageDueDate SMALLDATETIME



/*** USER VARIABLES ***/

SET @InterestRate = 0.0719

SET @CompoundingPeriod = 12

SET @Loan = 1000000.57

SET @AmoritizationMonths = 360

SET @MortgageStartDate = '2005-2-01'

/*** END USER VARIABLES ***/

SET @CompoundingInterest = @InterestRate/@CompoundingPeriod

SET @Payment = ROUND(

(

((@InterestRate/12) * @Loan)/(1- ( POWER( (1 + (@InterestRate/12)),
(-1 * @AmoritizationMonths) )))),2)

SET @TotalPayment = @Payment * @AmoritizationMonths

SET @FinanceCharges = @TotalPayment - @Loan

IF EXISTS(SELECT object_id FROM tempdb.sys.objects WHERE name LIKE '#Schedule%')

BEGIN

DROP TABLE #SCHEDULE

END

CREATE TABLE #SCHEDULE (

PERIOD INT

,PAYDATE SMALLDATETIME

,PAYMENT MONEY

,CURRENT_BALANCE MONEY

,INTEREST MONEY

,PRINCIPAL MONEY

)

SET @Period = 1

SET @MortgageEndDate = DATEADD(year,@AmoritizationMonths/12,@MortgageStartDate)

SET @MortgagePayDate = @MortgageStartDate

BEGIN

WHILE (@Period < = @AmoritizationMonths)

BEGIN

SET @CurrentBalance = ROUND (

@Loan * POWER( (1 + @CompoundingInterest) , @Period ) -
( (@Payment/@CompoundingInterest) *
(POWER((1 + @CompoundingInterest),@Period ) - 1)),2)

SET @Principal =
CASE
WHEN @Period = 1
THEN
ROUND((@Loan - @CurrentBalance),2)

ELSE
ROUND
((SELECT CURRENT_BALANCE - @CurrentBalance
FROM #SCHEDULE
WHERE PERIOD = @Period -1),2)

END

SET @Interest = ROUND(ABS(@Payment - @Principal),2)

SET @MortgageDueDate = @MortgagePayDate

INSERT
#SCHEDULE(PERIOD,
PAYDATE,
PAYMENT,
CURRENT_BALANCE,
INTEREST,
PRINCIPAL)

SELECT

@Period,
@MortgageDueDate,
@Payment,
@CurrentBalance,
@Interest,
@Principal

SET @Period = @Period + 1

SET @MortgagePayDate = DATEADD(MM,1,@MortgagePayDate)

END

END

SELECT * FROM #SCHEDULE
GO
SQLUSA
New Member
New Member

--
01 Sep 2009 12:31 AM
>cost-effective way to enable SSRS the run these calculations and generate monthly statements with that information?

Do you mean doing the calculations (expressions) in SSRS?

Stored procedure is the best approach for such an undertaking with T-SQL logic like posted above.

Kalman Toth
http://www.sqlusa.com - SQL Server 2008 Training
horton.rj
New Member
New Member

--
29 Dec 2009 02:28 PM
I am trying to tweak this code to be flexible enough for a few more payment frequency types.

For example; I want to pass in Annual 360 days or Annual 365 days or monthly. Your code seems to only calculate on a monthly basis?
Paul
New Member
New Member

--
06 Sep 2010 08:38 AM
is there any way to do this amortization in bulk (without using loops or cursors)?
any help really appreciated, thank you
ANDREY KALASHNIK
New Member
New Member

--
17 Jul 2012 01:48 PM
jwright237,

That's some good stuff. Thanks.

You are not authorized to post a reply.

Acceptable Use Policy