Amortization calculator

[lior03]

hello
the aim of this userform is to help calculate loan
payments.
i used several textboxes to help the user enter his data
my questions- what is wrong with the formula i used
(pmt)
second - how to deal thth numberformats namely
in one textbox data should appear as "0.00%"
an d in another i want the sum borrowed appear as
"#,###.00" and the period as "##.00"
thanks

[Bob Phillips]

hello
the aim of this userform is to help calculate loan
payments.
i used several textboxes to help the user enter his data
my questions- what is wrong with the formula i used
(pmt)
second - how to deal thth numberformats namely
in one textbox data should appear as "0.00%"
an d in another i want the sum borrowed appear as
"#,###.00" and the period as "##.00"
thanks

Well, as I see it the main problem with the formula is that you should negate the amount borrowed. Once you borrow that money, it is not an asset, it is a liability, so should be negative.

To get the sum borrowed in the required format, use

TextBox4.Value = Format(Pmt(TextBox3.Value / 12, TextBox2.Value * 12, -TextBox1.Value), "#,###.00")

To get the percentage to look correct, you have a problem. If you change it as it is entered, a 1 will automatically change to 1.00%, with the cursor after the %, not where you want it if you want to add decimal places. If you format it after leaving the textbox, what is the point? You could try this, not idael, but ...

Option Explicit

Private mEnableEvents As Boolean

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Private Sub CommandButton3_Click()
TextBox4.Value = Format(Pmt(Replace(TextBox3.Value, "%", "") / 100 / 12, _
            TextBox2.Value * 12, -TextBox1.Value), "#,##0.00")
End Sub

Private Sub TextBox2_Change()
    With TextBox2
        If mEnableEvents Then
            mEnableEvents = False
            If Int(CDbl(.Text)) <> CDbl(.Text) Then
                .Text = Format(.Text, "0.00")
                .SelStart = Len(.Text) - 1
            Else
                .Text = Format(.Text, "0.00")
                .SelStart = Len(.Text) - 3
            End If
            mEnableEvents = True
        End If
    End With
End Sub

Private Sub TextBox3_Change()
    With TextBox3
        If mEnableEvents Then
            mEnableEvents = False
            On Error Resume Next
                .Text = Replace(.Text, "%", "")
            On Error GoTo 0
            If Int(CDbl(.Text)) <> CDbl(.Text) Then
                .Text = Format(.Text / 100, "0.00%")
                .SelStart = Len(.Text) - 2
            Else
                .Text = Format(.Text / 100, "0.00%")
                .SelStart = Len(.Text) - 4
            End If
            mEnableEvents = True
        End If
    End With
End Sub

Private Sub TextBox2_Enter()
    mEnableEvents = True
End Sub

Private Sub TextBox3_Enter()
    mEnableEvents = True
End Sub