Math question - discrepancy between Excel and HP-12C

[Roger Murdock]

There appears to be some discrepancy between how Excel and the 12C calculate present values when the term includes a partial period.

Test problem:

Assuming a 6% annual discount rate, what is the present value of $1 to be received six months from now?

In Excel, =PV(6%,.5,0,-1) gives 0.971286.

On the HP-12C, 1 CHS FV 6 i .5 n PV gives 0.970874.

Granted, the discrepancy is not exactly tremendous, but it is still noteworthy. For what it's worth, I think Microsoft is right on this one.

 

[PL1957]

There appears to be some discrepancy between how Excel and the 12C calculate present values when the term includes a partial period.

Test problem:

Assuming a 6% annual discount rate, what is the present value of $1 to be received six months from now?

In Excel, =PV(6%,.5,0,-1) gives 0.971286.

On the HP-12C, 1 CHS FV 6 i .5 n PV gives 0.970874.

Granted, the discrepancy is not exactly tremendous, but it is still noteworthy. For what it's worth, I think Microsoft is right on this one.

Looking at my most recent copy of the American Institute of Real Estate Appraisers Financial Tables, the correct present value factor for a single semi-annual payment at a 6% rate is 0.970874. It's on page 150 of you want to check.

 

[Pericles]

The formula being used in Excel compounds the interest semi-annually whereas the HP calculation reflects simple annual interest: hence the difference.

 

[Roger Murdock]

The textbook reference notwithstanding, I still think Excel is correct because a 3% semi-annual discount rate is *not* the same as a 6% annual rate - it is actually equivalent to a 5.91% annual rate.

 

[Terrel L. Shields]

I found a different wierdness in Word Perfect's table feature. It was a fractional problem and I had 2 different ownerships that were the same amount

The problem was to calculate a division of interest and it ran something like this

640 acres were "integrated" by the oil and gas commission- owned about 60 acres of of the section - They leased it at 1/8th royalty and two people split the interest

so 640 ÷ 60 x 1/8th x 50% = .00585938 by the other line exactly the same numbers were something like .00585835...hmm. finally I re input the 60 in one and bingo it matched the other. I never did figure out why it was keying on to produce a different number. They appeared to be identical numbers in the table.

I concluded that evil spirits exist in computer chips

 

[Pericles]

I think we can reconcile the discrepancy between Excel and the HP 12C. The first parameter in the Excel PV function is the interest rate per period (6 months) not the annual interest rate. So, replace 6% with 3% and replace .5 with 1 since we are considering just 1 payment period. If we do this, I believe the result in Excel will match that of the HP 12C and we can all rest easy.

 

[PL1957]

The textbook reference notwithstanding, I still think Excel is correct because a 3% semi-annual discount rate is *not* the same as a 6% annual rate - it is actually equivalent to a 5.91% annual rate.

You don't understand - the Financial Tables are NEVER incorrect.

 

[Roger Murdock]

I think we can reconcile the discrepancy between Excel and the HP 12C. The first parameter in the Excel PV function is the interest rate per period (6 months) not the annual interest rate. So, replace 6% with 3% and replace .5 with 1 since we are considering just 1 payment period. If we do this, I believe the result in Excel will match that of the HP 12C and we can all rest easy.

Yes, that is correct - so it really comes down to semantics.

To Excel, taking a 6% annual discount rate over a six-month period is *not* the same as taking a 3% semi-annual discount rate over a six-month period.

To the 12C, they are one and the same - which is not technically accurate.

 

[Pittsburgh Pete]

Yes, that is correct - so it really comes down to semantics.

To Excel, taking a 6% annual discount rate over a six-month period is *not* the same as taking a 3% semi-annual discount rate over a six-month period.

To the 12C, they are one and the same - which is not technically accurate.

No, the 12C is accurate. It performs the function of halving the interest rate internally while Excel does not.

I would recommend that you get the tables book to double check your PV factors. I always cross reference one or two periods in the Excel spreadsheet with the tables book to be certain I haven't incorrectly inputed anything.

If I were setting up a DCF in Excel the interest rate would be divided by the periods--i.e. if annually at 6%, a half year would be computed as 3% (or 1.5% quarterly). Numbers will mirror those generated by the 12C.

 

[Roger Murdock]

No, the 12C is accurate. It performs the function of halving the interest rate internally while Excel does not.

I would recommend that you get the tables book to double check your PV factors. I always cross reference one or two periods in the Excel spreadsheet with the tables book to be certain I haven't incorrectly inputed anything.

If I were setting up a DCF in Excel the interest rate would be divided by the periods--i.e. if annually at 6%, a half year would be computed as 3% (or 1.5% quarterly). Numbers will mirror those generated by the 12C.

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.