Question about VBA amortization table

[rollintide89]

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?

Here is what I have:


Option Explicit

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
Next



End Sub







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.



2. 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:

a. 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 width.



8. You should bold all labels.



9. Create a Clear Macro with a button that will clear the spreadsheet.

[pike]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.