SAP Effective Interest Rate vs Ms Excel IRR XIRR

[Gustavo_Vazquez]

Hi,

 

SAP Effective Interest Rate is MS Excel XIRR function according to SAP Note 839381 . This document is how you can validate these two functions.

 

In the Excel attached (no macros) there is a calculation process in SAP , XIRR and IRR .

 

Some points to keep in mind.

 

• You can convert from XIRR to IRR only if you calculate if your base is days (interest days) not years (periods)


• XIRR calculates on act/365


• IRR calculates on act/360


• You can iterate values in Excel with Data -> Whatif Analysis -> Goal Seek . This is used to get IRR for NPV = 0


• Use SAP function EFFEKTIVZINS_BERECHNEN to calculate XIRR in SAP, only cashflow is needed.


• Look at formulas in Excel. Look for comments in cells.

 

This document does not support Excel files, so I uploaded it in xml files and attached images for SAP function

 

SAP Function - Days Sheet

 



 



Effective Interest Rate



 

SAP Function - Year Sheet

 



 



 

Effective Interest Rate