May 06 2023 09:22 AM
Hi folks,
I am hoping I can get someone to help me clarify why excel is not helping me get the correct answer.
I am trying to calculate the interest earn on a Certificate of Deposit with these factors:
Principal: $10,000
Term: 3 months
Rate: 5.05%
I got different results depending on different sources and I am trying to understand why.
In Excel:
Formula1: the result was $10.50
= Principal * ((1 + Rate/12)^(months/12) - 1)
Formula2: the result is: $11,592.80, in other words: $1,592.80
=FV(Rate,months,0,-Principal,0)
Formula3: the result was $10.50
=10000*((1+0.0505/12)^(3/12) - 1)
Using Bank Rate CD Calculator the result was: $123.93
Using Chat GPT: the result was: $118.70
Using the formula for calculating the interest earned on a Certificate of Deposit that pays interest monthly, we can calculate the amount earned on a CD with a principal of $10,000, an annual interest rate of 5.05%, and a term of 3 months as follows:
Interest Earned = Principal * ((1 + Rate/12)^(months/12) - 1)
Interest Earned = $10,000 * ((1 + 0.0505/12)^(3/12) - 1)
Interest Earned = $10,000 * ((1.00420833)^0.25 - 1)
Interest Earned = $10,000 * (1.01187003 - 1)
Interest Earned = $118.70 (rounded to the nearest cent)
Therefore, the interest earned on a Certificate of Deposit with a principal of $10,000, an interest rate of 5.05% and a term of 3 months is $118.70.
Thus, why the big differences? Perhaps something in the compounding period or the way the formatting is in excel?
Why is excel calculating $10.50 in the first formula?
Why is excel calculating 11,592.80 in the second formula
Why is excel calculating 10.50 in the third formula?
What could be the reason, CD calculator from Bankrate provides a different value?
Thank you for your help.
May 06 2023 10:31 AM - edited May 06 2023 10:33 AM
Solution@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.
May 06 2023 10:37 AM
@Joe User much appreciated!
May 06 2023 11:51 AM
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.
May 06 2023 01:13 PM
May 06 2023 01:16 PM
I am adding a screenshot of the prompt used with chat GPT and the full answer received, just in case.
May 06 2023 10:58 PM - last edited on Nov 09 2023 02:37 PM by
@MSegas wrote: ``I am adding a screenshot of the prompt used with chat GPT``
Thanks so much. That was very helpful.
May 06 2023 10:31 AM - edited May 06 2023 10:33 AM
Solution@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.