## Forum Discussion

# Why are Excel vs ChatGPT Interest calculations so different?

- May 06, 2023
MSegas wrote: ``Why is excel calculating $10.50 in the first formula?``

Garbage in, garbage out (GIGO)!

In the formula =Principal * ((1 + Rate/12)^(months/12) - 1), you are using a

Rate/12, but compounding by a*monthly rate*months/12.*number of years*Change Rate/12 to Rate.

-----

MSegas wrote: ``Why is excel calculating 11,592.80 in the second formula?``

In the formula =FV(Rate,months,0,-Principal,0), you are making a similar, but opposite mistake. You are using an

, but compounding by a*annual rate*.*number of months*Change Rate to Rate/12.

(And format F5 as General so that it shows the number of months, not the bogus date 3-Jan-1900.)

But Formulas 1 and 3 and your chatGPT inquiry calculate

.*just*__t__otal interestIn contrast, FV returns the ending balance, which

.*includes the principal*If you want just the total interest, the formula should be:

=FV(Rate/12,months,0,-Principal,0) - Principal

-----

MSegas wrote: ``Why is excel calculating 10.50 in the third formula?``

Essentially, the same mistake as in Formula 1.

Change Rate/12 to Rate.

-----

MSegas wrote: ``What could be the reason, CD calculator from Bankrate provides a different value?``

With the corrections above, we see 3 difference values for total interest:

118.70 chatGPT

123.93 Formulas 1 and 3 and Bankrate

126.78 Formula 2

Off-hand, I believe the difference between 123.93 and 126.78 is due to the different arithmetic methods and approximations of time.

That is, compounding 3 times is not the same as compounding once by 3/12.

As for the chatGPT result.... I suspect that chatGPT is rounding each period result, then summing the rounded values.

In general, the sum of rounded parts is often not the same as the rounded sum of the whole.

I will investigate further to confirm my "off-hand" thoughts.

-----

A final thought.... All of these methods are just estimates.

Each CD provider calculates and accumulates interest differently.

MSegas wrote: ``Why is excel calculating $10.50 in the first formula?``

Garbage in, garbage out (GIGO)!

In the formula =Principal * ((1 + Rate/12)^(months/12) - 1), you are using a __ monthly rate__ Rate/12, but compounding by a

__months/12.__

*number of years*

Change Rate/12 to Rate.

-----

MSegas wrote: ``Why is excel calculating 11,592.80 in the second formula?``

In the formula =FV(Rate,months,0,-Principal,0), you are making a similar, but opposite mistake. You are using an __ annual rate__, but compounding by a

__.__

*number of months*

Change Rate to Rate/12.

(And format F5 as General so that it shows the number of months, not the bogus date 3-Jan-1900.)

But Formulas 1 and 3 and your chatGPT inquiry calculate __ just total interest__.

In contrast, FV returns the ending balance, which __ includes the principal__.

If you want just the total interest, the formula should be:

=FV(Rate/12,months,0,-Principal,0) - Principal

-----

MSegas wrote: ``Why is excel calculating 10.50 in the third formula?``

Essentially, the same mistake as in Formula 1.

Change Rate/12 to Rate.

-----

MSegas wrote: ``What could be the reason, CD calculator from Bankrate provides a different value?``

With the corrections above, we see 3 difference values for total interest:

118.70 chatGPT

123.93 Formulas 1 and 3 and Bankrate

126.78 Formula 2

Off-hand, I believe the difference between 123.93 and 126.78 is due to the different arithmetic methods and approximations of time.

That is, compounding 3 times is not the same as compounding once by 3/12.

As for the chatGPT result.... I suspect that chatGPT is rounding each period result, then summing the rounded values.

In general, the sum of rounded parts is often not the same as the rounded sum of the whole.

I will investigate further to confirm my "off-hand" thoughts.

-----

A final thought.... All of these methods are just estimates.

Each CD provider calculates and accumulates interest differently.

- MiguelEgas2020May 06, 2023Copper Contributor
JoeUser2004 much appreciated!

- JoeUser2004May 06, 2023Bronze Contributor
I wrote: ``Off-hand, I believe the difference between 123.93 and 126.78 is due to the different arithmetic methods and approximations of time. That is, compounding 3 times is not the same as compounding once by 3/12.``

I confirmed that.

And IMHO, 126.78 is the correct estimate because it is consistent (or closer) to how CD interest is accumulated and compounded, namely 3 times monthly.

-----

I wrote: ``I suspect that chatGPT is rounding each period result, then summing the rounded values.``

Actually, the chatGPT result is a complete mystery to me. It seems to be GIGO(!).

Your implementation in G17:G21 does suffer from some rounding errors.

Not the summing of rounded values, but rounding some

, namely 1.00420833^0.25 and 1.01187003 - 1.*intermediate results to 8 decimal places*1.00420833 is the rounded result of 1 + 0.0505/12 (1.00420833333333)

But the origin of 1.01187003 is a complete mystery to me.

Disclaimer: I am not a chatGPT user. In fact, I am biased against it. ChatGPT just mashes together things that it finds on the internet. In a clever way, perhaps. But AFAIK, no one validates its results.

So, please help understand what you present in your Excel file.

Is that truly what chatGPT produced?!

First, it has the defect that I noted previously, namely compounding a

(Rate/12) by a*monthly rate*(months/12).*number of years*Second, even if we round 1 + Rate/12 to 8 decimal places, 1.00420833^0.25 is 1.00105043 (1.00105042624771),

__not 1.01187003.__The rest of the calculations are incorrect because of the mix of monthly rate and annual compounding.

But even if we correct that, 1+Rate is 1.05050000, and 1.05050000^0.25 is 1.01239272 (1.01239272151496), again

__not 1.01187003.__Finally, returning to my original theory, if chatGPT rounded and summed each monthly calculation, the result would be 126.78, once again

*not 118.70.*- MSegasMay 06, 2023Copper ContributorHi, Yes, it is in fact chat GPT exact output. I agree with you the best answer is 126.78, and chat GPT is doing something strange. I can't quite put my finger on it.