CREATE PROCEDURE sp_AprRate (
     @Capital As Float,
     @Balloon As Float,
     @Rental As Float,
     @Term As SmallInt,
     @MthsAdv As SmallInt, 
     @FirstMth As SmallInt, 
     @PayFreq As SmallInt, 
     @ExtraPay As SmallInt
)

AS

DECLARE @XPOWER As Float, @XPOW2 As Float, @Guess As Float, @GuessChange As Float
DECLARE @xNum As Float, @XDEN As Float, @RealAPR As Float
DECLARE @Periodno As SmallInt, @Count As Int
DECLARE @FirstPay As Int, @LastPay As Int, @T1 As Int

SELECT @FirstPay = @FirstMth - 1
If @FirstPay < 0 
	SELECT @FirstPay = 0

SELECT @T1 = @MthsAdv - @FirstPay

If @T1 < 0 
	SELECT @T1 = 0

SELECT @LastPay = @Term + @ExtraPay - @T1, @MthsAdv = @MthsAdv + 1, @LastPay = @LastPay - 1


SELECT @Guess = 1, @Count = 0
If @PayFreq = 0 
	SELECT @PayFreq = 1

WHILE 1 = 1 BEGIN
	IF @Rental = 0 
		BREAK
	
	SELECT @Count = @Count + 1
	
	IF @Count > 99
		BREAK

	SELECT @xNum = 0, @XDEN = 0

	SELECT @Periodno = 1
	WHILE @Periodno <= @Term BEGIN

		SELECT @XPOWER = POWER(@Guess, (@Periodno - 1))
		SELECT @XPOW2 = @XPOWER * @Guess
		IF @Periodno = 1 And @MthsAdv > 0 BEGIN
			SELECT @xNum = @xNum + @Rental * @MthsAdv / @PayFreq * @XPOW2, @XDEN = @XDEN + @Rental * @Periodno * @XPOWER
		END
		IF @Periodno >= @FirstMth BEGIN
			IF @Periodno <= @LastPay BEGIN
				IF (@Periodno - @FirstMth + 1 / @PayFreq) = (Sign((@Periodno - @FirstMth + 1) / @PayFreq) * Cast(Abs((@Periodno - @FirstMth + 1) / @PayFreq) as integer)) OR @ExtraPay != 0 BEGIN
					SELECT @xNum = @xNum + @Rental * @XPOW2
					SELECT @XDEN = @XDEN + @Rental * (@Periodno) * @XPOWER
				END
			END
		END
		SELECT @Periodno = @Periodno + 1
	END

	SELECT @XPOWER = POWER(@Guess, (@Term - 1))
	SELECT @XPOW2 = @XPOWER * @Guess
	SELECT @xNum = @xNum + @Balloon * @XPOW2
	SELECT @XDEN = @XDEN + @Balloon * @Term * @XPOWER

	SELECT @xNum = @xNum - @Capital

	SELECT @GuessChange = @xNum / @XDEN
	SELECT @Guess = @Guess - @GuessChange
	IF Abs(@GuessChange) < 0.0000001 BEGIN
		BREAK
	END
END

If @Count > 99 Or @Rental = 0 BEGIN
	RETURN SELECT 0
END ELSE BEGIN
	SELECT @RealAPR = POWER((1 / @Guess), 12) - 1
	RETURN SELECT Cast(((@RealAPR * 1000 + 0.5) / 10) as smallint)
END

GO