How do I prevent decimal problems like this in my loop, Loan system

Question

I always get the wrong decimal in the 3rd entry of the loop. If I use the calculator, I get the right result but if I put it in excel I got the wrong result in decimal. What I want is to have the same result in excel and my system.

For example.

I tried using Decimal.Round(value,2). It resolves the 2nd entry from rounding off but the next problem comes in the 3rd entry. I see Principal: 773192.13. I should see 773192.12 because that's the result in Excel and because it rounds up for the principal the result in the balance go up.

This is the result in Excel. You will notice that I put some +1, -1, or -2. That's the result in my system it keeps subtracting or adding. I have -0.05 in my balance as the final result.

Excel Result

Principal: 765517.81 Interest: 128000.00 Balance: 24834482.19 
Principal: 769345.40 Interest: 124172.41 Balance: 24065136.79 
Principal: 773192.12 Interest: 120325.68 Balance: 23291944.67 
Principal: 777058.09 Interest: 116459.72 Balance: 22514886.58 
Principal: 780943.38 Interest: 112574.43 Balance: 21733943.21 
Principal: 780943.09 Interest: 108669.72 Balance: 20949095.12

System Result

Principal: 765517.81      Interest: 128000.00   Balance: 24834482.19 
Principal: 769345.40      Interest: 124172.41   Balance: 24065136.79 
Principal: 773192.13 +1     Interest: 120325.68   Balance: 23291944.66 -1
Principal: 777058.09      Interest: 116459.72   Balance: 22514886.57 -1 
Principal: 780943.38      Interest: 112574.43   Balance: 21733943.19 -2 
Principal: 784848.09      Interest: 108669.72   Balance: 20949095.11 -1

My code

Dim balance As Decimal= 25600000
Dim pmtmonthlyamortization As Decimal= 893517.81
Dim interestrate As Decimal= 6.0 / 100
Dim TotPmts As Double
Dim mprincipal As Decimal
Dim minterest As Decimal

TotPmts = CDbl(Val(5))

For i = 0 To TotPmts
    minterest = (balance * (interestrate / 12))
    mprincipal = (pmtmonthlyamortization - minterest)
    balance = (balance - mprincipal)

    MessageBox.Show(minterest & vbNewLine & mprincipal & vbNewLine & vbNewLine & balance)
Next

Possible duplicate of [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Ken White, Dec 29 '18 at 04:27
There is a brief explanation of how doubles are stored at https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/troubleshooting-data-types Decimal might yield a better result. — Mary, Dec 29 '18 at 04:47
I already tried using As Decimal Still, i get the same result — Joms, Dec 29 '18 at 04:50
If you know about and tried the `Decimal` type, that would imply you know it's wrong to use `Double` for anything involving money. Why would you post the code here showing `Double`? — Joel Coehoorn, Dec 29 '18 at 05:18
Joel Coehoorn sorry i forgot to change it before posting it, i already tried using Decimal still i get the wrong result. The decimal keeps on subtracting and adding — Joms, Dec 29 '18 at 05:35

Joel Coehoorn · Accepted Answer · 2018-12-29T20:38:30.563

I'm gonna focus on the Principal value for the third row. It sounds like you want to see 773192.12 here, in order to match the Excel calculation. However, the Excel calculation is wrong! 773192.13 is the right number, and the result from your program is already correct... though the program itself still needs some work.

The reason why is you can't charge a fractional cent of interest. You have to round when calculating the interest, before applying the interest charge to the amortization. So we change this line:

minterest = (balance * (interestrate / 12))

To this:

minterest = Math.Round((balance * (interestrate / 12)),2)

Additionally, make the same change in the Excel calculation, so the interest is rounded before it is subtracted from the amortization.

Now we have a very clear result for 773192.13. We can see this by checking the full interest calculations for the first three lines, which are as follows:

128000.0000 
124172.4110 
120325.6840

Note the extra fractional cents (.001 on line 2, .004 on line 3). If you don't round before removing this from the monthly amortization, you end up with fractional cents as part of the calculated principal and then balance. By the third result it totals 1/2 a penny, which is enough to show up on the screen.

You can also easily prove this, because 893517.81 - 120325.68 = 773192.13, not 773192.12. You can prove it in your head, without even using a calculator, if you just look at the cents portion of the numbers: 81 - 68 = 13 is clearly the correct answer.

In the future, this kind of thing is easier to debug if you show the results to 4 places until you're sure the calculation is correct. The code below lets you do it both ways:

Dim balance As Decimal = 25600000D
Dim pmtmonthlyamortization As Decimal = 893517.81D
Dim interestrate As Decimal = 6.0D / 100D
Dim TotPmts As Integer = 5

For i As Integer = 0 To TotPmts
    'Pick one line below at a time to un-comment so you can see the results when run both ways:
    'Dim minterest As Decimal = Math.Round(balance * (interestrate / 12D), 2)
    'Dim minterest As Decimal = balance * (interestrate / 12D)
    mprincipal = pmtmonthlyamortization - minterest
    balance -= mprincipal

    MessageBox.Show($"Principle: {mprincipal:F4} Interest: {minterest:F4} Balance: {balance:F4}")
Next

The code above also fixes some other issues, namely that you should always use Decimal rather than Double when working with money.

The one exception to this is if the contract here says that fractional interest should always round up to favor the bank

@aijomsaugmata Comments can't have certain formatted items. Please repost that. — Joel Coehoorn, Dec 29 '18 at 06:04
This is the result for ~Dim minterest As Decimal = Math.Round(balance * (interestrate / 12D), 2) Interest: 128000.00 Principal: 765517.81 Balance: 24834482.19 Interest: 124172.41 Principal: 769345.40 Balance: 24065136.79 Interest: 120325.68 Principal: 773192.13 Balance: 23291944.66 Interest: 116459.72 Principal: 777058.09 Balance: 22514886.57 Interest: 112574.43 Principal: 780943.38 Balance: 21733943.19 Interest: 108669.72 Principal: 784848.09 Balance: 20949095.10 — Joms, Dec 29 '18 at 06:09
@aijomsaugmata Yes, and that is the **correct** result. The Excel result is wrong. You can see this clearly on the third line for Excel, because 893517.81 - 120325.68 = 773192.13, not 773192.12. You don't even need a calculator to check it. Just look at the cents portion: 81 - 68 = 13, not 12. — Joel Coehoorn, Dec 29 '18 at 06:11
@aijomsaugmata make sure to read my full answer. There is more info there now than when it was first posted. — Joel Coehoorn, Dec 29 '18 at 06:14
@aijomsaugmata Don't post code or data in comments. There's not space, and you can't format it. Update the original question if you have new information. — Joel Coehoorn, Dec 29 '18 at 06:20
okay. so the right answer is .13 but how do i get a total of 0.00 in a balance? can you please explain it to me in a simple way i'm new in programming sorry. — Joms, Dec 29 '18 at 06:22
Change to a `While` loop that runs while the balance is > 0, rather than a `For` loop. Each iteration of the loop should check that you don't overpay. But you need to write that code yourself. It's important practice. — Joel Coehoorn, Dec 29 '18 at 06:28
I still get a -0.05 balance i already use the while loop, i think i'm doing it wrong — Joms, Dec 29 '18 at 06:40

How do I prevent decimal problems like this in my loop, Loan system

Excel Result

System Result

My code

1 Answers1