VBA loan amortization error

[asiriel]

Hi guys,

I was doing a loan amortization example, but there had 2 types of error:5. invalid procedure and 13. mismatch type.
It warned me of the wrong payment=pmt equation(). I have redone it and followed the steps from a VBA tutorial. Can you please have a look and tell me where I got it wrong? Thanks a lot.


Sub Loan_Amortization()

' This program creates a ballon amortization payment and buyout schedule
' It uses the Pmt function to calculate the biweekly payment.

' Shortcut key Ctrl+y

Dim intRate, bwRate, initLoanBal, loanLife, period
Dim begBal, endBal
Dim payment, intComp, prinComp
Dim outrow, rownum

'************************************************************
' Programmer inputs
'************************************************************
outrow = 6 'Used to control where the output table will start


'Clear the output section of worksheet

'************************************************************
'User Inputs
'************************************************************
' The user provides these inputs in the worksheet and the
' program reads them in here.


intRate = InputBox("Enter APR: from 8% to 12% in 0.25% increment")
loanLife = InputBox("Enter amortization period 5-8 years")
initLoanBal = InputBox("Enter loan amount from 10,000 to 15,000")
period = InputBox("Enter loan payment period 1-4 years")



initLoanBal = Cells(2, 2).Value
intRate = Cells(3, 2).Value
loanLife = Cells(4, 2).Value
period = Cells(5, 2).Value


'************************************************************
' Compute and output results
'************************************************************
' Calculate biweekly payment

payment = Pmt(intRate, loanLife, initLoanBal)


' Initialize beginning balance for year 1
begBal = initLoanBal

' Loop to calculate and output biweekly amort. table
For rownum = 1 To loanLife * 26

intComp = begBal * bwRate
prinComp = payment - intComp
endBal = begBal - prinComp

Cells(outrow + rownum, 1).Value = rownum 'Payment number
Cells(outrow + rownum, 2).Value = begBal
Cells(outrow + rownum, 3).Value = payment
Cells(outrow + rownum, 4).Value = intComp
Cells(outrow + rownum, 5).Value = prinComp
Cells(outrow + rownum, 6).Value = endBal

begBal = endBal

Next rownum

'************************************************************
' Format the output data in the table
'************************************************************

End Sub

[jaslake]

Hi asiriel

Welcome to the Forum.

Couple of suggestions:

• Use Code Tags around all posted code
• Get rid of the Colors in your post...this Color Blind guy has no clue what it says
• Post a sample file...you'll get quicker, tested results.