Calculating Present Value in Excel with HP-12C

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.20, 2.21, 3.0
• Microsoft Excel for the Macintosh version 2.x and 3.0

SUMMARY

The calculation of the present value (PV) in Excel does not match HP-12C calculations when the period (nper) IS NOT an integer. The discrepancy is that HP-12C calculates the odd period differently than Excel does. The odd period will automatically be calculated at the beginning of the period. Odd period, by definition, is the partial payment (for example, in nper=48.5, .5 is the odd period).

According to Hewlett-Packard (HP) technical support, the HP-12C does the following:

1. Rounds nper down (for example, 48.5 payments becomes 48).

2. Calculates PV.

3. Uses the .5 payment and calculates PV.

4. Subtracts the difference between the 2 PV values (the reason for taking the difference in PV instead of finding the sum is the extra odd period payment).

You can show the discrepancy by doing the following:

1. See page 51 of the "HP-12C Owner's Manual and Problem-Solving Guide."

2. The following arguments are given:

         Rate: 1.25%;  nper: 48, 48.5, 49;  Payment: 150  Type: End
   

   Using the above arguments, the Excel calculation will yield the following PV results:

         @ nper = 48              PV = 5389.722136
         @ nper = 48.5            PV = 5430.653043
         @ nper = 49              PV = 5471.330505
   
      Using the above arguments, the HP-12C calculation will yield the
      following PV results:
   
         @ n = 48                 PV = 5389.722136
         @ n = 48.5               PV = 5356.245601
         @ n = 49                 PV = 5471.330505
   
   

MORE INFORMATION

If you want to use HP-12C to calculate like Excel's calculation for odd number of periods, contact HP Calculator Technical Support at (503) 757-2004 and ask for the booklet "HP-12C Solutions." See section 12-8 titled "Actual Calculations" in the booklet. Reprogram the HP-12C with the step-by-step instruction provided in the booklet. Then, follow the steps provided on page 51 of the "HP-12C Owner's Manual and Problem-Solving Guide."

The later versions of the HP calculators will calculate the odd period at the END. Such calculators include the HP-19B2, HP-17B2, HP-10B, HP-37E, HP-38E/C, and HP-18C.