Fixed Rate Loan Amortization Table (Alternatives to PMT Function)

[Tugboat00]

Hello all,

I am new to VBA so perhaps this is a foolish question. I have created a fixed amortization table using the pmt function with input boxes in vba to return the values. It seems to work well.

I am wondering what some of the alternative methods are for doing the same thing without using the PMT function. I am looking for some of the easier ways to do this. THe fixed amortization table I created has equal annual installments and the payment begins in the first year. If anyone has a link to a detailed guide that would be even better.

Appreciate any help.

[Leith Ross]

Hello Tugboat00,

The attached workbook will provide you with a fixed rate amortization schedule. You only need to input the loan amount, the annual interest rate and the loan period in years. Click the button and you will create a schedule of payments showing the principal, interest, and balance for each payment.

Here is the VBA macro to generate the amortization schedule. The attached workbook is a working example.

Sub Amortize()

  ' Written:    April 20, 2016
  ' Author:     Leith Ross
  
    Dim Amount      As Double
    Dim Balance     As Double
    Dim Cell        As Range
    Dim Interest    As Double
    Dim n           As Long
    Dim Payment     As Double
    Dim Payments    As Integer
    Dim Period      As Integer
    Dim Principal   As Double
    Dim Rate        As Double
    Dim Rng         As Range
    Dim Wks         As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Cell = Wks.Range("A2")
        
        Set Rng = Cell.CurrentRegion
        Set Rng = Intersect(Rng, Rng.Offset(1, 0))
        
        If Not Rng Is Nothing Then Rng.ClearContents
        
        Application.ScreenUpdating = False
            
            Amount = Wks.Range("J3")
            Rate = Wks.Range("J4") / 12
            Period = Wks.Range("J5")
            
            Payments = Period * 12
            Payment = (Amount * (Rate * ((1 + Rate) ^ Payments))) / (((1 + Rate) ^ Payments) - 1)
        
            Balance = Amount
            
            For n = 1 To Payments
                Interest = Balance * Rate
                Principal = Payment - Interest
                Balance = Balance - Principal
                Cell.Resize(1, 5).Value = Array(n, Payment, Principal, Interest, Balance)
                Set Cell = Cell.Offset(1, 0)
            Next n
            
        Application.ScreenUpdating = True
        
End Sub

[Tugboat00]

Hi Leith,

Thanks for the response. I have already created something like this though.

At the moment I am trying to construct a fixed loan amortization table with formulas and which uses the goal seek function. I have found a textbook example but I am running into some problems.

I get 1004 runtime error for the goal seek part of the code. I have tried fixing this but I am stuck.

If anyone would be willing to take a look, would you mind if I pm'd the code? I do not want to post it on the forum as I may use it as a template for an assignment later on. Want to avoid those plagiarism checks.

Any help appreciated.