Calculating Present Value in Excel with HP-12C

ID: Q67700

The information in this article applies to:

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, versions 2.x, 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:

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

Calculates PV.

Uses the .5 payment and calculates PV.

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).

The odd period will automatically be calculated with simple interest.

You can show the discrepancy by doing the following:

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

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

The PV calculation of HP-12C for 48.5 periods falls OUTSIDE of the range of 48 and 49 periods because the odd period was calculated by the method described above.

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.

Additional query words: 2.0 2.00 2.1 2.10 2.2 2.20 2.21 3.0 4.0 ppmt

Keywords :
Version :
Platform :
Issue type :