14,599,199 members

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:**

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

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

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.

You've got a bunch of double types. Change them all to decimal and see what happens.

Comments

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.

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!

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

Quote:Decimal problems subtracting that affect my output loan system

First loan rule:

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.

So your problem is about comparing different roundings between Excel and your program.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject,
503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada
+1 416-849-8900 x 100