Correct actual new balance after interest minus payment.

New Contributor

I start with an exact dollar and cent balance on a loan calculation. I compute interest to the nearest penny. 21.4 cents is posted as 21 cents. 21.5 would post as 22 cents, etc. When I add interest for the month, deduct the payment and reflect the new balance in C3 I.e. C3=C2 + A3 - B3, that balance is sometimes one cent higher or lower than the actual balance. For instance the first new balance ended as .68 when it was actually .6755. The added interest was .12 but the actual amount was .1186. Adding .68 plus .12 equals .80. However, the new balance shows .79 as the computed total is .7941. 

How do I get the new balance to reflect .80, not .79? I thought you used ABS as in the old Lotus 123 program but that doesn't do it. Can anyone tell me the function for fixing both the new balance and the interest computed co

4 Replies

@Oklahoma_Burning 

Round the result of all formulas to 2 decimal places. So for example if your formula for interest is

 

=interestcalculation

 

change it to

 

=ROUND(interestcalculation, 2)

 

etc.

Thanx for the quick response. Looked through the functions list and didn't see that one.  Have a great day.@Hans Vogelaar 

@Oklahoma_Burning 

interestcalculation was not meant as a real function, just as a placeholder for whatever you're using.

For example if you have

 

=D2*1.23%

 

change it to

 

=ROUND(D2*1.23%, 2)

Yes, I did that when i changed the new balance cells. Thanx for the reminder though. I don't know how I could forget the round function. Just old I guess. The spreadsheet works perfectly now thanx to your quick response.  @Hans Vogelaar