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