VBA error in calculations. Need more decimals?

[resolate]

Hello People,

I am making an excel file to compare different sets of loans with eachother and calculate best alternatives given some parameters.

For allowing the calculations i need to make a table of total payments for the loan, already this is where things go wrong. For some reason i get rounding errors while making the table through VBA as compared to making the table in excel. The two pictures below show what happens:

In the first situation i calculate the table by using the sheet functions. Here are some more explanations about the formulas for each column:

A: just number 1 to infinity
B: Capital to be paid off at start of session (= Cell F from row -1)
C: Total annuity to be paid off (= cell J5 ; in this cell i used the function: =ABS(BET(J5;J3;J1)) to calculate this)
D: Intrest part to be paid off (= cell B of same row * cell (J5 ; in this cell i used the function: =((1+J4)^(1/12))-1 which calculates monthly intrest given a yearly intrest rate)
E: capital part to be paid off (= cell C of same row - cell D of same row)
F: Is rest capital after this period (= cell B of same row - cell E of same row) (this cell will be used in the next row)

loan-excelf.jpg

As you can see after 15 years (=180 monthly periods) the loan results to 0 where everything is calculated by excel ws

Below you can see the table that is derived from a VBA macro:

loan-vbaf.jpg

As you can see in period 180: the result is not 0 and I think this is because of rounding errors or not enough decimal spaces used in the VBA macro.
For generating this table, I actually did the same technique as described above in the ws functions, but now i am able to generate the table when n(=#years) increases (so if n=20 then there will be 240 monthly periods)

Below is the code i use to generate this table:

Please Login or Register  to view this content.

Is there anyone who understands my problem and has a solution for this problem?

I am aware that there may be faster calculation methods (maybe use range array instead of FOR LOOP?) for the VBA macro, but since i am only a beginner, i am already very happy that this works. I will finetune my code once i learn more about the effectiveness off VBA.

Thank you for your reaction

[TMS]

Define all your variables as Double, not Integer. Integer has no decimal places.

[resolate]

Thank you for your response TMS,

None of the items in the table is actually stored in a variable through VBA. The only variables i use are those to create my loop and 1 to store the number of months in (which can be an integer, no need to be double). Any other thoughts?

[resolate]

Thank you for your response TMS,

None of the items in the table is actually stored in a variable through VBA. The only variables i use are those to create my loop and 1 to store the number of months in (which can be an integer, no need to be double). Any other thoughts?

[TMS]

Suggest you post a sample workbook and highlight your expected results (manually calculated examples).

[resolate]

I have added the excel sheet as an attachment.

The sheet named: "(A) HL" is the sheet where i calculate everything through the worksheet functions.

The sheet named: "(A)" is where i create the table through VBA code.

I highlighted only 2 cells, as you can see the rounding already starts there, which results in more errors along the way.

Thank you for your help.

[TMS]

I made a copy of sheet (A) and then ran your code. I then added some simple comparison formulae.

It can be seen that the comparison between your original sheet (A) and sheet (A) HL starts to fail early on.

However, when I compare the values generated on my machine, the output is identical to the values on sheet (A) HL.

Not sure what else I can offer.

See the attached updated example.

[resolate]

Dear TMS,

Thank you for your effort. However this leaves me confused... So actually you just used my code and it works for you whereas it doesnt work for me? Are there settings in my excel that i should change?

Thanx again

[TMS]

Yes, I just used your code. I first copied sheet (A) fo reference and then executed the code. At face value, the values looked ok. So then I added the comparison formulae to check if the values were actually the same. And they are.

I cannot imagine what would have that effect.

I'll see if anyone else has a view.

[xlnitwit]

Hi,

I suspect the rounding problem occurs because of the currency formatting on the cell and your code using the Value property- that will truncate to 4DP. Please try amending the code to use Value2

Please Login or Register  to view this content.

[TMS]

@xlnitwit: why would it work differently on my machine?

[xlnitwit]

@TMS

I would imagine that yours, like mine, views the cells as custom formatted since they don't match regional currency settings. Thus the truncation does not occur.

[snb]

You should round all results to 2 decimals before proceeding; rounding the formatting of cells on 2 decimals<> rounding the .values

Het heeft alles met afronden te maken.
Gebruik:

Please Login or Register  to view this content.

Waarom niet op helpmij geplaatst ?