14,599,199 members
Rate this:
See more:
I always get the wrong decimal in the 3rd entry of the loop i. 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) yes it resolve the 2nd entry from rounding off but the next problem comes in the 3rd entry

[ Principal: 773192.13 ] and the value that i need to show is
[ 773192.12 ] because that's the result in excel and because it round 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 or -1 -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

What I have tried:

```below is my code.

Dim balance As Double = 25600000
Dim pmtmonthlyamortization As Double = 893517.81
Dim interestrate As Double = 6.0 / 100

Dim TotPmts As Double
Dim mprincipal As Double
Dim minterest As Double

TotPmts = CDbl(Val(5))

For i = 0 To TotPmts
minterest = Decimal.Round((balance * (interestrate / 12), 2 )
mprincipal = Decimal.Round((pmtmonthlyamortization - minterest), 2 )
balance = Decimal.Round((balance - mprincipal), 2 )

MessageBox.Show(minterest & vbNewLine & mprincipal & vbNewLine & vbNewLine & balance)
Next```
Posted
Updated 29-Dec-18 1:21am

Rate this:

## Solution 3

Why have you reposted this question? I have already given you the answer.
Rate this:

## Solution 1

You've got Doubles in your calculations. This introduces errors in money calculations because of a lack of accuracy. If a variable is going to touch a money value or be used in a money calculation, use Decimal instead of double or single or float.

You've got a bunch of double types. Change them all to decimal and see what happens.
Member 14076249 29-Dec-18 0:19am

ahhh sorry i just copied it, but i already tried to changed them to decimal and i still get the same result it keeps adding and subtracting the decimal.
Dave Kreskowiak 29-Dec-18 9:51am

Excel isn't using two digits beyond the decimal point. Reformat the cells with the numbers to include five decimal digits and you'll have a better idea of the exact numbers being used (000,000.00000) to compare with the numbers in your own code.

The two digit display is NOT the actual value being used, both in your own code and in Excel!
Rate this:

## Solution 4

Quote:
Decimal problems subtracting that affect my output loan system

First loan rule: Loan values are never exact.
That is the reason why last payment always need a special handling.
`lastpmtmonthlyamortization = lastbalance + lastminterest`

If Excel use 893517.81 for every payment, chances are that it is not the exact value.
Rate this:

## Solution 2

A simple way to avoid rounding errors is to use only integers in the calculation and for the final result divide them by 100 to get the decimals.