Monthly repayments

[a2010]

Hi,

I need to write a program (in VBA) that produces the monthly repayments needed to pay back a loan of £y over a certain number of years, n, given a particular annual interest rate, r.

So I need to create 3 consecutive input boxes to input the values of the three input variables, y, n and r. I also need to do this in a sub called ’calculate’ which apparently does the calculations that will result in the monthly payment required.

This figure should be output to a message box which states The monthly payment required to pay of the loan is "£y".

Thanks.

[Bob Phillips]

Why would you use VBA? You could have 3 input cells and use one of the nuilt-in functions.

[a2010]

I'm wirting this program for an assigment, and it is to be written all in VBA.

[SamT]

Homework, hunh?

We won't do your homework for you, but we can guide you thru getting it done.

Are you going to use an MS Form or Worksheet Controls for the inputs and output?

Do you know how to calculate the result given the three inputs?

SamT

[a2010]

Yeah there's quite a few calculations that I need to do, and I do know how to calculate the result correctly, however I just need to know the outline of what do to using forms. Thanks.

[SamT]

Open a new Workbook
Keyboard ctrl+F11
This opens the VBA
Insure the "+ next to VBA Project (Book1) is a "-"
Right click on anything under Microsoft Excel Objects
Choose "Insert" and User Form
This will open a UserForm1 in the VBE that you can play with
If you don't see the Toolbox; Controls, use the View menu

You'll want labels "A" and Textboxes "ab|"... click 'em to select 'em and click the new UserForm1 to drop 'em. All the icons have hints, just hover the mouse over them.

If you don't see the properties Window on the bottom left, keyboard F4.

Click on any property and hit F1 for help on that property

You'll need at least one Command button to force calculations. After dropping it on the UserForm andf setting its Caption, doubleclick to open the UserForm's Code page and start the Procedure, (Sub) you will write.

I reccommend that the Sub CommandButton1_Click() only calls the actual calculation Sub.

SamT

[a2010]

thanks, i appreciate it

[a2010]

Ok Ive come up with:

Option Explicit
Private Sub Calculate_Monthly_Repayments_Click()
Dim y As Variant
Dim r As Variant
Dim n As Variant
Dim ratio As Double
Dim calc1 As Double
Dim calc2 As Double
Dim calc3 As Currency

y = Application.InputBox("Please enter the loan that is to be paid off")
If Not IsNumeric(y) Then
MsgBox "You must enter a number."
Exit Sub
End If
If y < 0 Then
MsgBox "Please enter a positive value."
Exit Sub
End If

r = Application.InputBox("Now input the annual interest rate to be used")
If Not IsNumeric(r) Then
MsgBox "You must enter a number."
Exit Sub
End If
If r < 0 Then
MsgBox "Please enter a positive value."
Exit Sub
End If

n = Application.InputBox("Finally, enter the number of years in which the loan needs to be paid off")
If Not IsNumeric(n) Then
MsgBox "You must enter a number."
Exit Sub
End If
If n < 0 Then
MsgBox "Please enter a positive value."
Exit Sub
End If

ratio = (r / 100) + 1

calc1 = y * (ratio ^ n)

calc2 = ((ratio ^ n) - 1) / (ratio - 1)

calc3 = calc1 / (calc2 * 12)

MsgBox ("The monthly payment required to pay off the loan is £") & Round(calc3, 2)

End Sub


But I need the input box to reset and show it again if an error message box comes up, instead of jumping to the next one.

[Bob Phillips]

I would suggest that you build a userform that allows all data to be input in one proicess cycle, and is validated for input.

[a2010]

I'm required to use VBA without userforms. I was thinking along the lines of a loop of some sort.
EDIT: Nevermind, I figured it out.

[nicky_cutti]

hi guyz...my programe is the same one to this as well...but i was wondering if u could solve my problem associated with this issue.....when the error message appears and we cllick ok...the i want to to reproduce or take me back to where i enterd something wrong...whereas it closes the whole thing any siggestion....reply asap...thnks.....



Ok Ive come up with:

Option Explicit
Private Sub Calculate_Monthly_Repayments_Click()
Dim y As Variant
Dim r As Variant
Dim n As Variant
Dim ratio As Double
Dim calc1 As Double
Dim calc2 As Double
Dim calc3 As Currency

y = Application.InputBox("Please enter the loan that is to be paid off")
If Not IsNumeric(y) Then
MsgBox "You must enter a number."
Exit Sub
End If
If y < 0 Then
MsgBox "Please enter a positive value."
Exit Sub
End If

r = Application.InputBox("Now input the annual interest rate to be used")
If Not IsNumeric(r) Then
MsgBox "You must enter a number."
Exit Sub
End If
If r < 0 Then
MsgBox "Please enter a positive value."
Exit Sub
End If

n = Application.InputBox("Finally, enter the number of years in which the loan needs to be paid off")
If Not IsNumeric(n) Then
MsgBox "You must enter a number."
Exit Sub
End If
If n < 0 Then
MsgBox "Please enter a positive value."
Exit Sub
End If

ratio = (r / 100) + 1

calc1 = y * (ratio ^ n)

calc2 = ((ratio ^ n) - 1) / (ratio - 1)

calc3 = calc1 / (calc2 * 12)

MsgBox ("The monthly payment required to pay off the loan is £") & Round(calc3, 2)

End Sub

But I need the input box to reset and show it again if an error message box comes up, instead of jumping to the next one.