Excel Financial Function Translation to Lotus Formats

ID: Q80594

The information in this article applies to:

Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

When saving a worksheet to a WKS, WK1, or WK3 file format, Microsoft Excel translates formulas to the corresponding Lotus formula. If there is no equivalent formula in Lotus, or if the equivalent formula requires fewer or more parameters than the Microsoft Excel function, you will receive the error message:

Cannot Write Record (Cell:XX): Continue Reporting each Error?

Most Microsoft Excel financial functions will translate to an equivalent Lotus formula. In many of the cases when an error is returned while saving to a Lotus format, the function requires some modification to the parameters.

MORE INFORMATION

There are three categories that a financial function will fall into when transferring to a Lotus format:

The function will translate with no modification.

Some modification to the parameters is needed. In many cases, this means using only the parameters required by Microsoft Excel, omitting the optional arguments.

The function will not translate under any circumstances because there is no equivalent Lotus function. IPMT(), MIRR(), and PPMT() fall into this category for all Lotus formats.

The following is a table listing all of the Microsoft Excel financial functions, and a number corresponding to the categories described above for the Lotus formats WKS, WK1, and WK3.


Function   WKS  WK1  WK3
--------   ---  ---  ---

DDB()       3    1    1
FV()        2    2    2
IPMT()      3    3    3
IRR()       1    1    1
MIRR()      3    3    3
NPER()      3    2    2
NPV()       2    2    2
PMT()       2    2    2
PPMT()      3    3    3
PV()        2    2    2
RATE()      3    2    2
SLN()       3    1    1
SYD()       3    1    1
VDB()       3    3    1  (This function is not available in
                          Microsoft Excel version 2.x.)

If, when saving the file to a Lotus format, Microsoft Excel returns an error message, the formula's calculated value will save to the Lotus file. If the function causing the error falls into category 2, some revision to the arguments may allow it to translate successfully, though the meaning of the function's results might change.

FV(), NPV(), PMT(), and PV() all translate if you specify only the required parameters, omitting any optional arguments. TERM() and RATE() translate to equivalent Lotus formulas only if the required PV (present value) function is set to zero (or omitted with a comma included as a placeholder) and the optional FV (future value) function is included.

(Note: the modification required for the NPV() function is to specify a cell reference for the value argument. Example: NPV(10%,B1) works with B1=10000 but NPV(10%,10000) will not work.)

REFERENCES

"Microsoft Excel User's Guide," for Windows, version 3.0, page 57

"Microsoft Excel Function Reference," for Windows, version 3.0, pages 47, 98, 133-134, 154, 161-162, 183-185, 189-190, 192, 226, 232, 244

"Lotus 1-2-3 Reference Manual Release 2.01," version 2.01 pages 4-7, 4-8

Additional query words: 2.0 2.00 2.01 2.1 2.20 2.21 3.0 4.00a

Keywords :
Version :
Platform :
Issue type :