Click here to Skip to main content
14,599,199 members
Rate this:
Please Sign up or sign in to vote.
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:
Please Sign up or sign in to vote.

Solution 3

Why have you reposted this question? I have already given you the answer.
   
Rate this:
Please Sign up or sign in to vote.

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.
   
Comments
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:
Please Sign up or sign in to vote.

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.
So your problem is about comparing different roundings between Excel and your program.
   
Rate this:
Please Sign up or sign in to vote.

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.
   

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