EXCEL的CUMIPMT函数的数学公式是什么(如何计算)?我想通过数学方式进行计算。请帮忙。
EXCEL的CUMIPMT函数的数学公式是什么(如何计算)?我想通过数学方式进行计算。请帮忙。
CUMIPMT
是不可能的,因为Excel不是开源软件。然而,在Google搜索中会出现许多结果,例如用Javascript编写的这个实现、另一个用Javascript编写的这个实现,或者在PHP中的PHPExcel的实现。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 );
}
GetRmz
和 GetZw
分别是 PMT
和 FV
的德语对应词。 - sled/// <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;
}