Math question - discrepancy between Excel and HP-12C

[Pittsburgh Pete]

But Pete, a 6% annual discount rate is actually not equivalent to a 3% semi-annual or 1.5% quarterly rate. Much as a 6% annual growth rate does not produce a 12% return over two years.

Playing with the numbers I can't help but think that the variance has something to do with the number of periods and whether they are compounded monthly, quarterly, or semi-annually.

I get your answer (.971286) if you don't use the blue "g" key which converts to monthly compounding. If you do use the blue keep to convert to monthly you would get .970518. The figure of .970874 assumes either semi-annual or annual compounding.

Playing with excel, if the 6% interest rate is divided by two (for semi-annual) the PV factor matches the tables book (1/(1+I)^n).

BTW, I cannot get the PV factor that you got with the 12C. Using your input I get .971286 (i.e. 1=FV i=6 n=.5)

 

[Roger Murdock]

BTW, I cannot get the PV factor that you got with the 12C. Using your input I get .971286 (i.e. 1=FV i=6 n=.5)

That's odd....maybe HP changed the functionality at some point? Tell me your exact keystrokes.

 

[Pittsburgh Pete]

That's odd....maybe HP changed the functionality at some point? Tell me your exact keystrokes.

If I input FV=1, n=.5, and i=6 and solve for PV I get .971286; if I run the i and n through the "g" key I get .970518 which is listed in the tables book for monthly compounding--as it should since the "g" key converts all to monthly compounding.

The number in the tables book for semiannual or annual compounding I can generate in excel by use of the formula 1/(1+i)^n by splitting the interest rate in half and computing for one period: 1/(1+3%)^1. This formula yields the figure in the table book for 6% semiannual or annual "frequency of conversion" for both semi-annual and annual.

It's clearly the number of compounding periods and how they are expressed in the calculator or in excel.

Still don't understand how you got .970874 using the 12C?

 

[Pericles]

How I got .970874 using the 12C: annual rate is 6% so periodic rate is 3% (i=3) for 1 period (n=1) with FV of -1.

 

[Roger Murdock]

If I input FV=1, n=.5, and i=6 and solve for PV I get .971286; if I run the i and n through the "g" key I get .970518 which is listed in the tables book for monthly compounding--as it should since the "g" key converts all to monthly compounding.

The number in the tables book for semiannual or annual compounding I can generate in excel by use of the formula 1/(1+i)^n by splitting the interest rate in half and computing for one period: 1/(1+3%)^1. This formula yields the figure in the table book for 6% semiannual or annual "frequency of conversion" for both semi-annual and annual.

It's clearly the number of compounding periods and how they are expressed in the calculator or in excel.

Still don't understand how you got .970874 using the 12C?

In my original post, I didn't say anything about a monthly compounding rate - I am using a 6% annual rate (compounding annually). So the "g" key has no business here. All it does is divide the rate by 12 and multiply the number of periods by 12, anyway.

In any case, my point - that Excel and the 12C give different results for the exact same inputs - stands. Given a future value of -1, an yield/discount rate of 6% per period, and a .5 period term, they will give different present values. And I think that Excel's approach is more accurate.

On the 12C, you can get a PV of .970874 through the following keystrokes:

[f] [REG] [f] [6] [1] [CHS] [FV] [6] [.] [5] [n] [PV]

If the display does not show .970874, there is something wrong with your 12C.

You can try it on this "virtual" 12C....

http://www.epx.com.br/ctb/hp12c.php

 

[PLCG]

I have come to the conclusion that there is no such thing as an exact answer because I have an HP12C Platinum and an HP12C Anniversary edition and they will frequently give slightly different results.

I started to review for the exam and my results didn't match the book answers done on an HP12C. Sometimes the result was right between two possible answers on the review question - really - which to choose - it was very disconcerting! I believe the test is done with an HP12C, so I bought an HP12C and bingo, my results always matched. Now since all 3 HP12C models give slightly different results, I figure all results will vary slightly with different calculators or software. It doesn't happen in the 2+2=4 range, but it does happen.

 

[Pericles]

At the risk of taking another whack at a dead horse, I think we can all agree that the HP12C result of .9708 is correct. The problem seems to be the disparate result given by Excel. If we carefully consider the parameters, the problem goes away. Rate is the periodic rate, not the annual rate. Since we have a six-month period, the rate should be 6/2. Nper is the number of periods, which is one. Therefore, I would use the function =PV(6%/2,1,,-1), which yields the same result as the HP12C.

 

[Pittsburgh Pete]

At the risk of taking another whack at a dead horse, I think we can all agree that the HP12C result of .9708 is correct. The problem seems to be the disparate result given by Excel. If we carefully consider the parameters, the problem goes away. Rate is the periodic rate, not the annual rate. Since we have a six-month period, the rate should be 6/2. Nper is the number of periods, which is one. Therefore, I would use the function =PV(6%/2,1,,-1), which yields the same result as the HP12C.

I think I whacked that same horse a couple of posts back! But I do believe you are correct. Case closed?

 

[Roger Murdock]

At the risk of taking another whack at a dead horse, I think we can all agree that the HP12C result of .9708 is correct. The problem seems to be the disparate result given by Excel. If we carefully consider the parameters, the problem goes away. Rate is the periodic rate, not the annual rate. Since we have a six-month period, the rate should be 6/2. Nper is the number of periods, which is one. Therefore, I would use the function =PV(6%/2,1,,-1), which yields the same result as the HP12C.

Why would you think we can all agree, when I've already explicitly sided with Excel? I think the 12C is misinterpreting the problem and using an invalid shortcut in the calculation. I did not specify a semi-annual rate of 3%. Using a partial period does not change the specified annual rate of 6%.

Think about it - by your logic, to calculate the PV of $1 received two years from now, we would have a "two-year period" and would therefore use a rate of 6% x 2 = 12%, which is not accurate. Can we agree on that?

 

[Pericles]

I think we need to keep clear the distinction between simple interest and compound interest. The PV of $1 for a "2-year period" is in fact equivalent to a 12% rate of simple interest. However, if you recast the "2-year period" as two "1-year periods" then you are effectively compounding interest. The HP12C computes compounded interest based on the periodic value of n for n>1 and simple interest when n is less than or equal to1 and > 0. The original question that stimulated this enjoyable exchange was a matter of confounding simple interest with compounding. Can we all agree now?