Need Amortizaton Schedule 1

[thebluffer]

Does anyone have a procedure for creating an loan amortization schedule?

 

[vbMax]

Option Explicit
Sub generateAmort()

Dim x As Integer
Dim interest As Double
Dim noPayments As Integer
Dim principle As Double


principle = InputBox("Enter the amount of the loan", _
"Input Principle&quot
interest = InputBox("Enter the interest rate" _
& vbCrLf & "(7½% should be entered as 7.50)", _
"Input Interest Rate&quot
noPayments = InputBox("Enter the number of payments." _
& vbCrLf & "(30 years = 360 payments)", _
"Input Number of Payments&quot

Cells.Select
Selection.ClearContents
Range("A1&quot.Select

Range("A1&quot.Value = "Principle"
Range("A2&quot.Value = "Interest Rate"
Range("A3&quot.Value = "Number of Payments"
Range("A5&quot.Value = "Monthly Payment"

Range("B1&quot.Value = principle
Range("B2&quot.Value = interest / 100
Range("B2&quot.NumberFormat = "0.00%"
Range("B3&quot.Value = noPayments
Range("B5&quot.FormulaR1C1 = "=ABS(PMT(R2C2/12,R3C2,R1C2))"

Range("D1&quot.Value = "Payment No"
Range("E1&quot.Value = "Beg Balance"
Range("F1&quot.Value = "Payment"
Range("G1&quot.Value = "Interest"
Range("H1&quot.Value = "Payment to Principle"
Range("I1&quot.Value = "End Balance"

For x = 1 To noPayments
Range("D" & LTrim(Str(x + 1))).Value = x
Next x

Range("E2&quot.Value = Range("B1&quot.Value
Range("F2&quot.Value = Range("B5&quot.Value
Range("G2&quot.FormulaR1C1 = "=RC[-2]*R2C2/12"
Range("H2&quot.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2&quot.FormulaR1C1 = "=RC[-4]-RC[-1]"

For x = 2 To noPayments
Range("E" & LTrim(Str(x + 1))).FormulaR1C1 = "=R[-1]C[4]"
Range("F" & LTrim(Str(x + 1))).FormulaR1C1 = "=R5C2"
Range("G" & LTrim(Str(x + 1))).FormulaR1C1 = "=RC[-2]*R2C2/12"
Range("H" & LTrim(Str(x + 1))).FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I" & LTrim(Str(x + 1))).FormulaR1C1 = "=RC[-4]-RC[-1]"
Next x

Range("E:E,F:F,G:G,H:H,I:I,B1,B5&quot.NumberFormat = "#,##0.00"

Cells.Select
Cells.EntireColumn.AutoFit
Range("A1&quot.Select


End Sub