Amortization table help

Rolltide89 · Nov 8, 2011

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!

 [FONT=&quot]Create a macro called Amortization with a button that will perform the following tasks:[/FONT]
[FONT=&quot]1. [/FONT][FONT=&quot]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.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]2. [/FONT][FONT=&quot]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.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]3. [/FONT][FONT=&quot]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.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]4. [/FONT][FONT=&quot]Write the label Payment in A4 with the actual value in B4 formatted as currency. Use the PMT function for this calculation.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]5. [/FONT][FONT=&quot]Create an amortization table with the following headings and format the values with currency style formatting:[/FONT]
[FONT=&quot]a. Period-this will reflect the number of payments.[/FONT]
[FONT=&quot]b. Payment-use the PMT function to calculate monthly payment[/FONT]
[FONT=&quot]c. Interest-use the IPMT function to calculate monthly interest.[/FONT]
[FONT=&quot]d. Principal-use the PPMT function to calculate monthly principal.[/FONT]
[FONT=&quot]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.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]6. [/FONT][FONT=&quot]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.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]7. [/FONT][FONT=&quot]Make sure your macro also widens columns a-g to an appropriate width.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]8. [/FONT][FONT=&quot]You should bold all labels.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]9. [/FONT][FONT=&quot]Create a Clear Macro with a button that will clear the spreadsheet.[/FONT]

Fazza · Nov 8, 2011

hi. suggest you debug the code. maybe step through the code and view locals to see values of variables - especially at the step that errors. what does that reveal?

Amortization table help

Rolltide89

New Member

Excel Facts

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

Amortization table help

Rolltide89

New Member

Excel Facts

Fazza

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock