CODEWITHSUNDEEP
t-sqlexcel-formulaamortization
Craig
Craig

Reputation: 11

Loan Amortization Schedule - Need to solve for a target payment number

Open to using SQL or Excel... I can generate a loan amortization schedule. But, the problem I'm trying to solve is re-tooling the calculation such that the UNKNOWN VALUE is the payment number when a particular balance is achieved. Typically, you are solving for principal reduction, to get the new ending balance for incremented payments 1 through n. But I know the target unpaid balance that I'm looking for, and would like to figure out how to calculate the payment number when that balance would be achieved.

So, trying to figure out how to skip calculating every month of the amortization schedule to figure out when a target balance has been achieved, and instead back straight into the payment number from knowing the target balance.

I've figured out how to do it in SQL, very cool process, using a user-defined function for creating an amortization schedule over a portfolio of loans, with double-nested loops. The outer loop increments through the portfolio, the inner link increments scheduled payments numbered 1 through n. The problem is... it's going to take (est.) 61 hours to run (even after a concerted tuning effort) because the incremental looping can be very slow... IT/network folks are going to be very displeased with me.

Here's the user defined function for single use... I loop through this incrementing @payments. But the math is over my head for figuring out how to rewrite to solve for payment number, rather than solving for @upb (unpaid principle balance).

    BEGIN
        DECLARE @upb AS DECIMAL(11,2) = 0    --initialize
        DECLARE @premium AS DECIMAL(11,2) = @original_unearned_premium_balance
        DECLARE @interest AS FLOAT         = @interest_rate 
        DECLARE @term AS INTEGER       = @original_term    
        DECLARE @payments AS INTEGER       = @payments_made        

        SET @upb = 
             CAST(
                   @premium * POWER(1 + @interest / 12, @payments) - 
                   ROUND((@interest / 12 * @premium) / (1 - POWER((1 + @interest / 12),(@term * -1))), 2)  * ((POWER(1 + @interest / 12, @payments) - 1) / (@interest / 12))
                AS DECIMAL(11,2))

        RETURN @upb

    END

Upvotes: 0

Views: 220

Answers (1)

Craig
Craig

Reputation: 11

SOLUTION IDENTIFIED...

Replicate the Excel PMT and NPER functions in SQL; PMT is a component of the NPER function. NPER is going to give you number of payments remaining based on a current balance, interest rate and payment, so for my purposes, I needed to derive a result from Original Term (known value) less NPER result, to identify when that particular balance would be reached.

PMT fx - Replicate PMT function in SQL, use column values as inputs

NPER fx - provided in T-N comment above.

Thanks for the suggestion about NPER, T-N... it triggered my thinking to find the PMT function to solve this exercise. Was able to run an entire ETD portfolio in < 1 min.

Upvotes: 0

Related Questions