Outputting Amortization Schedule in Messagebox - Loop is not displayed-CodePudding

I am brand new to the forum and to Excel/VBA. I am taking Applied/Advanced Financial Analysis class and our assignment this week is to display an amortization schedule in a messagebox using 3 (or 4) input boxes. So full transparency: this is schoolwork, but I swear on everything thats holy that I have spent an excruciating amount of time on this assignment and I am stuck. Yes, I reached out to my professor, but unfortunately I am not knowledgable enough to follow her directions.

I can get all 4 input boxes to show up and the output messagebox as well, but it will not display the loop. I am trying to store the calculation for the loop in a variable, but I have no clue how to do that. Help please. I am desperate.

Here is my code: `

Sub PaymentScheduleCalculator()

Dim PV As Single '10000
Dim years As Single '2
Dim frequency As Double '12
Dim rate As Variant '4% APR

Dim Ppmt As Double
Dim Ipmt As Double
Dim Pmt As Single 'for pmt after each year

Dim i As Integer 'designation for loop
  
Dim Temp As Integer
Dim TempVars!

For i = 1 To n * frequency
Pmt = PV * rate / frequency
TempVars! = Temp & vbNewLine & i & _
               vbTab & FormatCurrency(PV, 2) & _
               vbTab & FormatCurrency(Pmt, 2) & _
               vbTab & FormatCurrency(Ipmt, 2) & _
               vbTab & FormatCurrency(-Ipmt, 2)
               PV = PV - Pmt   Ipmt
Next i
  
PV = InputBox("How much money do you want to borrow?", "Payment Calculator", 10000)
years = InputBox("If you borrow " & FormatCurrency(PV) & " - how many years do want to borrow the money for?", "Payment Calculator", 2)

rate = InputBox("If you borrow " & FormatCurrency(PV) & " for " & years & " years, " & "what interest rate are you paying?", "Payment Calculator", 0.04)
        If Right(rate, 1) = "%" Then
        rate = Val(Left(rate, Len(rate) - 1) / 100)
        Else
        rate = rate
        End If

frequency = InputBox("If you borrow " & FormatCurrency(PV) & " at " & FormatPercent(rate) & "," & " for " & years & " years, " & _
"how many payment intervals are there per year?", "Payment Calculator", 12)
'runs fine until here but does not display the loop
            
MsgBox "Loan Amount " & FormatCurrency(PV) & _
vbNewLine & "Number of Payments " & years * frequency & _
vbNewLine & "Interest Rate " & FormatPercent(rate) & _
vbNewLine & _
vbNewLine & "PMT # " & vbTab & "Balance " & vbTab & "Payment " & vbTab & "Interest " & vbTab & "Capital " & _
vbNewLine & RepeatCalc, , "Payment Calculator"

End Sub

` I have a feeling that I am designating the variable wrong or not at all and I don't know how to fix it and it is slowly driving me batsh*t crazy.

CodePudding user response：

There are several things wrong with your code...as others have pointed out as well.

First of, you want to run a For...next loop by using

For i = 1 To n * frequency

    'some code

next

The loop is initiated before those variables 'n' & 'frequency' have been assigned a value. Although your intention was to set 'frequency' by means of an Input box, 'n' is not set anywhere and will hence always be 0. The result is therefore in essence that the loop reads:

For i = 1 To 0 * 12 'which results in the loop not running at all

    'some code

next

So, properly set 'n' to whatever it needs to be or provide an input dialog for it.

Moreover, the actual intention of this loop is to extend the displayed text in the message box.

If you want to do that, you will need to start building the string first. According to your code your text should begin with:

"Loan Amount " & FormatCurrency(PV) & _
    vbNewLine & "Number of Payments " & years * frequency & _
    vbNewLine & "Interest Rate " & FormatPercent(rate) & _
    vbNewLine & _
    vbNewLine & "PMT # " & vbTab & "Balance " & vbTab & "Payment " & vbTab & "Interest " & vbTab & "Capital "

So if you assign that text to a variable you can then extend the text using the loop, like so:

Dim msgText As String
msgText = "Loan Amount " & FormatCurrency(PV) & _
    vbNewLine & "Number of Payments " & years * frequency & _
    vbNewLine & "Interest Rate " & FormatPercent(rate) & _
    vbNewLine & _
    vbNewLine & "PMT # " & vbTab & "Balance " & vbTab & "Payment " & vbTab & "Interest " & vbTab & "Capital "
    
    'now the loop extends 'msgText'
    For i = 1 To n * frequency
        Pmt = PV * rate / frequency
        msgText = msgText & vbNewLine & Str(i) & _
                       vbTab & FormatCurrency(PV, 2) & _
                       vbTab & FormatCurrency(Pmt, 2) & _
                       vbTab & FormatCurrency(Ipmt, 2) & _
                       vbTab & FormatCurrency(-Ipmt, 2)
        PV = PV - Pmt   Ipmt
    Next i

msgText = msgText & vbNewLine & RepeatCalc

Once that is completed, the whole string is contained in variable msgText You can then initiate the MsgBox with that text.

MsgBox msgText, vbOKOnly, "Payment Calculator"

One more thing, you have an InputBox for rate and you seem to try and remove the '%' sign at the end of the string in case somebody puts in a percentage with a sign. (good thinking in itself..:-))

Although this works, what it ends up with is a String type variable and, as rate is declared as Variant it has no issues with that... But it will screw up your calculation in your For..next loop, as Pmt = PV * rate / frequency can only work using values (i.e. Double\Integer etc.)

What you should do is declare rate as a Double and ensure that the result of your '%' removal will be converted to a Double value by means of -for example- using CDbl(Val(Left(rate, Len(rate) - 1) / 100))

So, your code, with some amendments should look more or less like the below. Mind you, you may have to ensure that all variables (including 'n') are properly set to ensure the calculation is correct. As, for example, Ipmt is not calculated anywhere...

Altered code:

Sub PaymentScheduleCalculator()

Dim PV As Single '10000
Dim years As Single '2
Dim frequency As Double '12
Dim rate As Double '4% APR
Dim strRate As String

Dim Ppmt As Double
Dim Ipmt As Double
Dim Pmt As Single 'for pmt after each year

Dim i As Integer 'designation for loop
  
Dim Temp As Integer

n = 1 'has to be properly set to relevant value?
  
PV = InputBox("How much money do you want to borrow?", "Payment Calculator", 10000)
years = InputBox("If you borrow " & FormatCurrency(PV) & " - how many years do want to borrow the money for?", "Payment Calculator", 2)

strRate = InputBox("If you borrow " & FormatCurrency(PV) & " for " & years & " years, " & "what interest rate are you paying?", "Payment Calculator", 0.04)
        If Right(strRate, 1) = "%" Then
            rate = CDbl(Val(Left(strRate, Len(strRate) - 1) / 100))
        Else
            rate = CDbl(strRate)
        End If

frequency = InputBox("If you borrow " & FormatCurrency(PV) & " at " & FormatPercent(rate) & "," & " for " & years & " years, " & _
"how many payment intervals are there per year?", "Payment Calculator", 12)

Dim msgText As String
msgText = "Loan Amount " & FormatCurrency(PV) & _
    vbNewLine & "Number of Payments " & years * frequency & _
    vbNewLine & "Interest Rate " & FormatPercent(rate) & _
    vbNewLine & _
    vbNewLine & "PMT # " & vbTab & "Balance " & vbTab & "Payment " & vbTab & "Interest " & vbTab & "Capital "
    
    'now extends the 'msgText' variable
    For i = 1 To n * frequency
        Pmt = PV * rate / frequency
        msgText = msgText & vbNewLine & Str(i) & _
                       vbTab & FormatCurrency(PV, 2) & _
                       vbTab & FormatCurrency(Pmt, 2) & _
                       vbTab & FormatCurrency(Ipmt, 2) & _
                       vbTab & FormatCurrency(-Ipmt, 2)
        PV = PV - Pmt   Ipmt
    Next i

msgText = msgText & vbNewLine & RepeatCalc

MsgBox msgText, vbOKOnly, "Payment Calculator"

End Sub