What is the Mathematical formula for CUMIPMT function of EXCEL(how it is calculated)?

Question

What is the Mathematical formula for CUMIPMT function of EXCEL(how it is calculated)? I want to calculate it mathematically. Please Help

Answer 1

The implementation of CUMIPMT in Excel is impossible for us to provide seeing as Excel is not open-source.

However numerous results show up in a Google search such as this implementation in Javascript, this other implementation also in Javascript, or PHPExcel's implementation in PHP.

The closest result would probably be to look at Open Office's C++ implementation - thats very close or identical to Excel's implementation.

It's available in the OpenOffice repository on Github

double SAL_CALL AnalysisAddIn::getCumipmt( double fRate, sal_Int32 nNumPeriods, double fVal,
    sal_Int32 nStartPer, sal_Int32 nEndPer, sal_Int32 nPayType ) THROWDEF_RTE_IAE
{
    double fRmz, fZinsZ;

    if( nStartPer < 1 || nEndPer < nStartPer || fRate <= 0.0 || nEndPer > nNumPeriods  || nNumPeriods <= 0 ||
        fVal <= 0.0 || ( nPayType != 0 && nPayType != 1 ) )
        THROW_IAE;

    fRmz = GetRmz( fRate, nNumPeriods, fVal, 0.0, nPayType );

    fZinsZ = 0.0;

    sal_uInt32  nStart = sal_uInt32( nStartPer );
    sal_uInt32  nEnd = sal_uInt32( nEndPer );

    if( nStart == 1 )
    {
        if( nPayType <= 0 )
            fZinsZ = -fVal;

        nStart++;
    }

    for( sal_uInt32 i = nStart ; i <= nEnd ; i++ )
    {
        if( nPayType > 0 )
            fZinsZ += GetZw( fRate, double( i - 2 ), fRmz, fVal, 1 ) - fRmz;
        else
            fZinsZ += GetZw( fRate, double( i - 1 ), fRmz, fVal, 0 );
    }

    fZinsZ *= fRate;

    RETURN_FINITE( fZinsZ );
}

Answer 2

The complete OpenOffice formula converted to C# based on Daniel's suggestion:

/// <summary>
/// The Excel CUMIPMT function as implemented by OpenOffice.
/// https://github.com/apache/openoffice/blob/c014b5f2b55cff8d4b0c952d5c16d62ecde09ca1/main/scaddins/source/analysis/financial.cxx
/// </summary>
/// <param name="rate">rate as double (0.05 for 5%)</param>
/// <param name="numberOfPayments">nper</param>
/// <param name="presentValue">pv</param>
/// <param name="startPeriod">start_period</param>
/// <param name="endPeriod">end_period</param>
/// <param name="type">0 (for payment at the end of period) or 1 (for payment at the beginning of the period</param>
/// <returns>The cumulative interest paid on a loan between start period and end period.</returns>
public double? CumulativeInterestPaid(double rate, int numberOfPayments, double presentValue, int startPeriod, int endPeriod, int type)
{
    if (startPeriod < 1 || endPeriod < startPeriod || rate <= 0.0 || endPeriod > numberOfPayments || numberOfPayments <= 0 || presentValue <= 0.0 || (type != 0 && type != 1))
        return null;

    var fRmz = GetRmz(rate, numberOfPayments, presentValue, 0.0, type);
    var fZinsZ = 0.0;
    var nStart = startPeriod;
    var nEnd = endPeriod;

    if (nStart == 1)
    {
        if (type <= 0)
            fZinsZ = -presentValue;

        nStart++;
    }

    for (var i = nStart; i <= nEnd; i++)
    {
        if (type > 0)
            fZinsZ += GetZw(rate, i - 2, fRmz, presentValue, 1) - fRmz;
        else
            fZinsZ += GetZw(rate, i - 1, fRmz, presentValue, 0);
    }

    fZinsZ *= rate;

    return fZinsZ;
}

// https://github.com/apache/openoffice/blob/c014b5f2b55cff8d4b0c952d5c16d62ecde09ca1/main/scaddins/source/analysis/analysishelper.cxx
private double GetZw(double fZins, double fZzr, double fRmz, double fBw, int nF)
{
    double fZw;
    if (fZins == 0.0)
        fZw = fBw + fRmz * fZzr;
    else
    {
        var fTerm = Pow(1.0 + fZins, fZzr);
        if (nF > 0)
            fZw = fBw * fTerm + fRmz * (1.0 + fZins) * (fTerm - 1.0) / fZins;
        else
            fZw = fBw * fTerm + fRmz * (fTerm - 1.0) / fZins;
    }

    return -fZw;
}

// https://github.com/apache/openoffice/blob/c014b5f2b55cff8d4b0c952d5c16d62ecde09ca1/main/scaddins/source/analysis/analysishelper.cxx
private double GetRmz(double fZins, double fZzr, double fBw, double fZw, int nF)
{
    double fRmz;
    if (fZins == 0.0)
        fRmz = (fBw + fZw) / fZzr;
    else
    {
        var fTerm = Pow(1.0 + fZins, fZzr);
        if (nF > 0)
            fRmz = (fZw * fZins / (fTerm - 1.0) + fBw * fZins / (1.0 - 1.0 / fTerm)) / (1.0 + fZins);
        else
            fRmz = fZw * fZins / (fTerm - 1.0) + fBw * fZins / (1.0 - 1.0 / fTerm);
    }

    return -fRmz;
}