XL98: Equivalents of Lotus 1-2-3 @IPAYMT and @PPAYMT

ID: Q185114

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SUMMARY

When you open a Lotus 1-2-3 worksheet in Microsoft Excel, the @IPAYMT and @PPAYMT functions are not converted. Only the resulting values of these functions are converted. However, Microsoft Excel has equivalent functions that calculate the same values.

FUNCTIONS IN LOTUS VS. MICROSOFT EXCEL

The Lotus 1-2-3 @IPAYMT function calculates the interest payments for a given number of periods for an investment that is based on periodic, constant payments and a constant interest rate. The @PPAYMT function calculates the principal payments for a given number of periods for an investment that is based on periodic, constant payments and a constant interest rate.

The IPMT and PPMT functions in Microsoft Excel are equivalent to the @IPAYMT and @PPAYMT functions in Lotus 1-2-3. The major difference is that the Lotus 1-2-3 functions provide arguments that span multiple periods while the Microsoft Excel functions provide one argument for a single period. However, because you can use arrays in most Microsoft Excel functions, you can use an array of periods in the period (per) argument.

The following table lists the syntax for the Lotus 1-2-3 functions and their equivalent Microsoft Excel functions.


 Lotus 1-2-3                           Microsoft Excel
 -------------------------------------------------------------------
 
 @IPAYMT(principal;interest;           IPMT(rate,per,nper,pv,fv,type)
 term;start-period;end-period;
 type;future-value)
 
 @PPAYMT(principal;interest;           PPMT(rate,per,nper,pv,fv,type)
 term;start-period;end-period;
 type;future-value)

NOTE: In Lotus 1-2-3, the following arguments are optional: end-period, type, and future-value. In Microsoft Excel, the fv and type arguments are optional.

EXAMPLES

Calculating Interest for a Single Period Using @IPAYMT and IPMT

Sample Data:

The following formulas calculate the amount of interest due in the sixth period of a 24-month $500 loan at 8% annual interest.

In Lotus 1-2-3:

The following function

@IPAYMT(500;.08/12;24;6)

returns 2.68203.

In Microsoft Excel:

The following function

=IPMT(.08/12,6,24,500)

returns ($2.68).

NOTE: In Microsoft Excel, interest paid out is a cash outflow (a negative cash flow), and the formula returns a negative value. To return a positive value, add a negative sign to the value for the pv argument (for example, use -500).

Calculating Interest for Multiple Periods Using @IPAYMT and IPMT

Sample Data:

The following formulas calculate the amount of interest due in the first three months of a 12-month $1000 loan at 10% annual interest.

In Lotus 1-2-3:

The following function

@IPAYMT(1000;.10/12;12;1;3)

returns 23.00491.

In Microsoft Excel:

The following function

=SUM(IPMT(.10/12,{1,2,3},12,1000))

returns ($23.00).

NOTE: In Microsoft Excel, you must enter this function as an array by pressing COMMAND+RETURN.

Calculating Principal for a Single Period Using @PPAYMT and PPMT

Sample Data:

The following formulas calculate the principal payment for the last payment of a 30-month $1500 loan at 10% annual interest.

In Lotus 1-2-3:

The following function

@PPAYMT(1500;.10/12;30;30)

returns 56.24837.

In Microsoft Excel:

The following function

=PPMT(.10/12,30,30,1500)

returns ($56.25).

NOTE: In Microsoft Excel, interest paid out is a cash outflow (a negative cash flow), and the formula returns a negative value. To return a positive value, add a negative sign to the value for the pv argument (for example, use -500).

Calculating Principal Payments for Multiple Periods Using @PPAYMT and PPMT

Sample Data:

The following formulas calculate the principal payments paid out in the last 12 months of a 48-month $90000 loan at 9% annual interest.

In Lotus 1-2-3:

The following function

@IPAYMT(90000;.09/12;48;37;48)

returns 25610.25.

In Microsoft Excel:

The following function

=SUM(PPMT(.09/12,{37,38,39,40,41,42,43,44,45,46,47,48},48,90000))

returns ($25610.25).

NOTE: In Microsoft Excel, you must enter this function as an array by pressing COMMAND+RETURN.

REFERENCES

For more information about the IPMT function, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

ipmt

and then click Show Topics. Select the "IPMT" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

For more information about the PPMT function, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text

ppmt

and then click Show Topics. Select the "PPMT" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: financial accrued

Keywords : xlformula xl123Quattro
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto