CUMIPMT.sql

/** 
 * Cumulative Interest
 * (SQL Server) 
 * AzsaturnDx (2022) - No copyright at all
 *
 * Returns the cumulative interest paid on a loan between @start and @end periods.
 *
 * @Rate FLOAT - Required. The interest rate.
 * @Periods INT - Required. The total number of payment periods.
 * @Value INT - Required. The present value.
 * @Start FLOAT - Required. The first period in the calculation. Payment periods are numbered beginning with 1.
 * @End FLOAT - Required. The last period in the calculation.
 * @Type - Required. The timing of the payment.
 		0 (zero) Payment at the end of the period
		1 Payment at the beginning of the period
 
 * @return FLOAT - Cumulative interest
 *
 * @Example
 * 	  SELECT dbo.CUMIPMT(0.09/12,30*12,125000,13,24,1)
 *
 * REQUIRES: dbo.PMT, dbo.FV
 * Required functions are available @: https://gist.github.com/Azsaturndx/8ce3e47e2d3210a65fc83b09ac32da5f
 */

CREATE FUNCTION [dbo].[CUMIPMT]
	(@Rate Float
	,@Periods INT
	,@Value Float
	,@Start INT
	,@End INT
	,@Type INT)  
RETURNS Float
BEGIN
	--Return error if values are lower or equal to zero.
	IF @Rate <= 0 OR @Periods <= 0 OR @Value <= 0 RETURN NULL
	IF @Start < 1 OR @End < 1 OR @Start> @End RETURN NULL
	IF @Type NOT BETWEEN 0 AND 1 RETURN NULL

	DECLARE @Payment FLOAT =  dbo.PMT(@Rate,@Periods,@Value,0,@Type)
	DECLARE @Interest FLOAT = 0

	IF @Start = 1
	BEGIN
		IF @Type = 0
		BEGIN
			SET @Interest = - @Value
			SET @Start += 1;
		END --IF @Type == 0
	END --IF @Start = 1

	DECLARE @i INT = @Start

	WHILE ( @i <= @End)
	BEGIN
		IF @Type = 1
		BEGIN
			SET @Interest += dbo.FV(@Rate,@i - 2,@Payment,@Value,1) - @Payment
		END --IF @Type = 1
		ELSE
		BEGIN
			SET @Interest += dbo.FV(@Rate,@i - 1,@Payment,@Value,0)
		END --ELSE IF @Type = 1

		SET @i += 1
	END --WHILE ( @i <= @End)

	SET @Interest *= @Rate

	RETURN @Interest
END; -- End of Function