Payment calculation for Rate discount

[Kawaka Ku]

I need help for my payment calculation. When I applied Rate Discount into the worksheet for calculate loan amortize, the looping in interest rate, monthly payment, and remain principal balance are not continue even I setup a loop like this:

For loopIndexInteger = 0 To maximumInteger

SheetObj.Cells(exRow, 2) = SheetObj.Cells.Item(exRow, 4).value * (prod3disd1 / 100) / 12
SheetObj.Cells(exRow, 3) = MonthlyPmt - SheetObj.Cells.Item(exRow, 2).value

Next loopIndexInteger

still having problem.

This is my output look like in excel worksheet Please see # Payment in 3, 6, 9.

Loan Amount: $30,000.00
Interest Rate: 8.00%
Years of Loan 10
Monthly Payment: $363.98

#Payment Interest Principal Pmt Principal Bal

1 200.00 163.98 29,836.02
2 198.91 165.08 29,670.94
3 147.52 216.46 29,504.76
4 196.70 167.28 29,337.48
5 195.58 168.40 29,169.08
6 145.00 218.98 28,999.56
7 193.33 170.65 28,828.91
8 192.19 171.79 28,657.12
9 118.68 245.30 28,484.18
10 189.89 174.09 28,310.09
11 188.73 175.25 28,134.84
12 187.57 176.42 27,958.43

------------------------------------------------------------------------------------------------------------------
Also, when I set option strict on, it gave me an error message said " option strict on disallows late binding". This is how I coding in my vb: SheetObj.Cells(exRow, 3) = MonthlyPmt - SheetObj.Cells.Item(exRow, 2).value.

Thank you.

[kenobewan]

If you are getting one output and no error (except strict one), then there is a problem with the loop. From what I can see there is one syntax error - just use next not next loopIndexInteger.

Please show how you declare and get a value for maximumInteger...

Hope that this helps.

[Kawaka Ku]

If you are getting one output and no error (except strict one), then there is a problem with the loop. From what I can see there is one syntax error - just use next not next loopIndexInteger.

Please show how you declare and get a value for maximumInteger...

Hope that this helps.

Thank you for your replied. I know that I have problem in the loop but just cannot find the problem. I have been tried the For and next first, it cause the same problem as well.

Thank.

[kenobewan]

Please show how you declare and get a value for maximumInteger...

[Kawaka Ku]

Please show how you declare and get a value for maximumInteger...

This is how I declare and setup for the loop.

Dim loopIndexInteger As Integer
Dim maximumInteger As Integer

For loopIndexInteger = 0 To maximumInteger

SheetObj.Cells(exRow, 2) = SheetObj.Cells.Item(exRow, 4).value * (Prod3Disd1TextBox.Text / 100) / 12

SheetObj.Cells(exRow, 3) = MonthlyPmt - SheetObj.Cells.Item(exRow, 2).value

Next loopIndexInteger

Thanks,

[kenobewan]

You declare maximumInteger as an integer, but it does not have a value - therefore the loop does not work correctly. Where are you going to get the value from?

[Kawaka Ku]

You declare maximumInteger as an integer, but it does not have a value - therefore the loop does not work correctly. Where are you going to get the value from?

You are right. I forgot to assigned the value for the variable for maximuminteger. However, I tried the other way like this:

Dim LoopIndexInteger as Integer


SheetObj.Cells(exRow, 2) = PrinAmt * (Intdisc / 12)

SheetObj.Cells(exRow, 3) = MonthlyPmt - SheetObj.Cells(exRow, 2).value

PrinAmt = PrinAmt - SheetObj.Cells(exRow, 3).value

SheetObj.Cells(exRow, 4) = PrinAmt - SheetObj.Cells(exRow, 3).value

SheetObj.Cells(exRow, 4) = PrinAmt - SheetObj.Cells.Item(6, 3).value


If Product2TextBox.Text <> "" And PrincipalTextBox.Text <> "" And NumpmtTextBox.Text <> "" And InterestRateTextBox.Text <> "" And Prod2DiscountCheckBox.Checked = True And Prod2M1TextBox.Text <> "" And Prod2D1TextBox.Text <> "" And Prod2Disd1TextBox.Text <> "" And _
Prod2M1 = SheetObj.Cells(exRow, 1).value Then

For LoopIndexInteger = 1 to 20

PrinAmt = PrinAmt - SheetObj.Cells(exRow, 3).value

SheetObj.Cells(exRow, 2) = SheetObj.Cells.Item(exRow, 4).value * Prod2disd1 / 100) / 12

SheetObj.Cells(exRow, 3) = MonthlyPmt - SheetObj.Cells.Item(exRow, 2).value

Next loopIndexInteger

and still won't work correctly. Please see the out put for Excel worksheet.

16 128.75 158.95 22,561.48
17 108.27 179.43 22,401.62
18 106.72 180.98 18,651.69
19 105.69 182.01 18,469.68
20 104.66 183.04 18,286.64
21 103.62 184.08 18,102.56
22 102.58 185.12 17,917.44
23 101.53 186.17 17,731.28
24 100.48 187.22 17,544.05
25 99.42 188.28 17,355.77
26 98.35 189.35 17,166.42
27 97.28 190.42 16,975.99
28 96.20 191.50 16,784.49
29 95.11 192.59 16,591.90
30 94.02 193.68 16,398.22
31 92.92 194.78 16,203.44

I applied with 1% interest rate disd into line 17 and the loop is fine in Interest and Principal Pmt. But in Remain Balance is not correct.

Please see the following excel worksheet. If I applied 2% into line 17 and the loop is seem to be incorrect.

17 89.61 198.09 22,401.62
18 104.71 182.99 18,294.99
19 103.67 184.03 18,110.96
20 102.63 185.07 17,925.88
21 101.58 186.12 17,739.76
22 100.53 187.18 17,552.59
23 99.46 188.24 17,364.35
24 98.40 189.30 17,175.05
25 97.33 190.38 16,984.67
26 96.25 191.45 16,793.22
27 95.16 192.54 16,600.68
28 94.07 193.63 16,407.05
29 92.97 194.73 16,212.32
30 91.87 195.83 16,016.49
31 90.76 196.94 15,819.55

In the above worksheet, after I applied interest rate of 2% into line 17 and the following line #18 and down is seem to be not in the loop. Could you please help me with this problem?
Thanks.

[kenobewan]

Please confirm whether you receive an error, your line that calculates the incorrect column and whether the loop completes. If the loop is now problem free, I am leaning towards a problem in the calculation...