I am trying to get a macro in VBA excel to create an amortization table for me. I have written what I believe is the right
code, but I keep getting an error message. It gives me a Run-time Error '1004' : Application-defined or object defined
error. I made the labels for the table with Period, Payment, Interest, Principle, and Balance. When I run the macro it will
show an input box for "Enter rate of loan in decimal form", then "Enter number of years", then
"Enter the amount of loan" and then it shows the error. The macro is supposed to fill in the table under the
headings based on what is put in the input cells. Am I missing anything or not defining the right variables?
is what I have:
Public Sub Amortization()
Dim Rate As Long
Dim Amount As Currency
Dim Time As Currency
Dim Payment As Currency
Dim Balance As Currency
Dim X As Currency
Dim Y As Currency
Dim MPayment As Currency
Dim Principal As Currency
Rate = InputBox("Enter rate of loan in decimal from", "RATE")
Range("a1").Value = "Rate of Loan"
Range("b1").Value = FormatPercent(Rate, 2)
Time = InputBox("Enter the number of years", "Time")
Range("a2").Value = "Number of Years"
Range("b2").Value = Time
Amount = InputBox("Enter the Amount of Loan", "AMOUNT")
Range("a3").Value = "Amount Borrowed"
Range("b3").Value = FormatCurrency(Amount, 2)
Range("a4").Value = "Amount of Monthly Payment"
Range("b4").Value = FormatCurrency(-Pmt(Rate / 12, Time * 12, Amount), 2)
Range("c6").Value = "Period"
Range("d6").Value = "Payment"
Range("e6").Value = "Interest"
Range("f6").Value = "Principle"
Range("g6").Value = "Balance"
Range("a1:a4").Font.Bold = True
Range("c6:g6").Font.Bold = True
Payment = Range("b4").Value
Balance = Amount
For X = 1 To Time
Sheet1.Cells(Y, 3) = X
Sheet1.Cells(Y, 4) = FormatCurrency(MPayment, 2)
Sheet1.Cells(Y, 5) = FormatCurrency(-IPmt(Rate, X, Time, Amount), 2)
Principal = FormatCurrency(-PPmt(Rate, X, Time, Amount), 2)
Sheet1.Cells(Y, 6) = FormatCurrency(Principal, 2)
Y = Y + 1
Here is what I am supposed to do to create the macro. I believe I might have missed some variables that need to
be defined, as hinted in question 6. If you can help I greatly appreciate it!
Create a macro called Amortization
with a button that will perform the following tasks:
1. Prompt the user to enter the rate of the loan
in decimal form and write label Rate of Loan in A1 with the value in B1 formatted as a percentage.
Prompt the user to enter the number of years for the loan and write label Number of Years for the loan in A2 with the value
in B2 formatted as currency.
3. Prompt the user to enter the amount of the loan and write label Amount
Borrowed in A3 with the value in B3 formatted as currency.
4. Write the label Payment in A4 with the
actual value in B4 formatted as currency. Use the PMT function for this calculation.
5. Create an
amortization table with the following headings and format the values with currency style formatting:
Period-this will reflect the number of payments.
b. Payment-use the PMT function to calculate monthly payment
c. Interest-use the IPMT function to calculate monthly interest.
d. Principal-use the PPMT function to
calculate monthly principal.
e. Balance-first time subtract the principal from loan amount. Afterwards, you will
use the old balance minus the current principal to calculate the new balance.
6. Creation of the
amortization table will require a While or For loop where the rate must be divided by 12 and the years of loan multiplied by
12. You should create variables for Rate, Time, Amount, and Payment. It will also help to create variables for Period,
Interest, Principal, and Balance.
7. Make sure your macro also widens columns a-g to an appropriate
8. You should bold all labels.
9. Create a Clear Macro with a button that
will clear the spreadsheet.