CREATE PROC sp_LoanAmount
(
--Supplying default values
@LoanAmount Money = 1000,
@RateOfInterest decimal(5,2) = 5,
@TimePeriod tinyint = 1
)
AS
BEGIN
DECLARE @LoanDate date
SET @LoanDate = GETDATE()
--Interest (P x R x T)/100; P: Principal (Loan Amount), R: Rate of Interest, T: Time period
DECLARE @Interest money
SET @Interest =( @LoanAmount * @TimePeriod * @RateOfInterest ) / 100
--Grant Total
DECLARE @TotalAmount money
SET @TotalAmount = @LoanAmount + @Interest
--Equated monthly installment (EMI)
DECLARE @EMIAmount money
SET @EMIAmount = @TotalAmount / (@TimePeriod * 12)
--Define date range for the report
DECLARE @StartDate date = @LoanDate;
DECLARE @EndDate date = DATEADD(MONTH,@TimePeriod * 12, @LoanDate)
--Generate one column of serie numbers
;WITH SequenceTable(SeqID) AS
(
SELECT 1
UNION ALL
SELECT SeqID + 1
FROM SequenceTable
WHERE SeqID < DATEDIFF(MONTH, @StartDate, @EndDate)
)
--add a CTE that translates those numbers into the dates in our range
, DateTable(CalendarDates) as
(
SELECT DATEADD(MONTH, SeqID, @StartDate)
FROM SequenceTable
),
--returning a range of dates
ResultTable as
(
select * from DateTable
)
--Adding Sequence Number, EMIAmount and OutstandingBalance to the data set.
SELECT
ROW_NUMBER() OVER (ORDER BY CalendarDates) SeqNo,
CalendarDates ,
CASE WHEN CalendarDates > GETDATE() THEN @EMIAmount ELSE 0 END AS EMIAmount,
SUM(@emiamount)
OVER(ORDER BY CalendarDates ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) OutstandingPrincipal,
FORMAT( SUM(@emiamount)
OVER(ORDER BY CalendarDates ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) / SUM(@TotalAmount),'P') [OutstandingPrincipal%]
FROM DateTable
GROUP BY CalendarDates
ORDER BY CalendarDates
OPTION (MAXRECURSION 0);
END;
GO
EXEC sp_LoanAmount@LoanAmount=1000,@RateOfInterest= 5,@TimePeriod= 1
Loan amount =£1.000
Rate of interest = 5%
Time period = 1 Year
