Forum Discussion
Why are Excel vs ChatGPT Interest calculations so different?
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 number of years months/12.
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.
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 intermediate results to 8 decimal places, namely 1.00420833^0.25 and 1.01187003 - 1.
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 monthly rate (Rate/12) by a number of years (months/12).
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.
