Excel 计算特定投资期支付利息:ISPMT函数

ISPMT函数用于计算特定投资期内要支付的利息。Excel提供此函数是为了与Lotus1-2-3兼容。ISPMT函数的语法如下:


ISPMT(rate,per,nper,pv)

其中,rate参数为投资的利率。per参数为要计算利息的期数,此值必须为1~nper。nper参数为投资的总支付期数。pv参数为投资的当前值,对于贷款,pv参数为贷款数额。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ISPMT函数.xlsx”工作簿,切换至“Sheet1”工作表,本例的原始数据如图19-19所示。该工作表中记录了某贷款的年利率、利息的期数、投资的年限、贷款额,要求根据给定的数据计算在这些条件下对贷款第1个月支付的利息和对贷款第一年支付的利息。具体的操作步骤如下。

图19-19 原始数据

STEP01:选中A7单元格,在编辑栏中输入公式“=ISPMT(A2/12,A3,A4*12,A5)”,然后按“Enter”键返回,即可计算出对贷款第一个月支付的利息,如图19-20所示。

STEP02:选中A8单元格,在编辑栏中输入公式“=ISPMT(A2,1,A4,A5)”,然后按“Enter”键返回,即可计算出对贷款第一年支付的利息,如图19-21所示。

应确认所指定的rate参数和nper参数单位的一致性。例如,同样是四年期年利率为12%的贷款,如果按月支付,rate参数应为12%/12,nper参数应为4*12;如果按年支付,rate参数应为12%,nper参数为4。对所有参数,都以负数代表现金支出(如存款或他人取款),以正数代表现金收入(如股息分红或他人存款)。

计算第1个月支付的利息

图19-20 计算第1个月支付的利息

计算第一年支付的利息

图19-21 计算第一年支付的利息

Excel 计算给定期内投资利息偿还额:IPMT函数

IPMT函数用于基于固定利率及等额分期付款方式,计算给定期数内对投资的利息偿还额。IPMT函数的语法如下:


IPMT(rate,per,nper,pv,fv,type)

其中,rate参数为各期利率。per参数用于计算其利息数额的期数,必须为1~nper。nper参数为总投资期,即该项投资的付款期总数。pv参数为现值,或一系列未来付款的当前值的累积和。fv参数为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv参数,则假设其值为零(例如,一笔贷款的未来值即为零)。type参数为数字0或1,用以指定各期的付款时间是在期初还是期末,如果省略type参数,则假设其值为零。下面通过实例详细讲解该函数的使用方法与技巧。

打开“IPMT函数.xlsx”工作簿,切换至“Sheet1”工作表,本例的原始数据如图19-16所示。该工作表中记录了某贷款的年利率、用于计算其利息数额的期数、贷款的年限、贷款的现值,要求根据给定的数据计算在这些条件下贷款第一个月的利息和贷款最后一年的利息。具体的操作步骤如下。

图19-16 原始数据

STEP01:选中A7单元格,在编辑栏中输入公式“=IPMT(A2/12,A3*3,A4,A5)”,然后按“Enter”键返回,即可计算出贷款第1个月的利息,如图19-17所示。

STEP02:选中A8单元格,在编辑栏中输入公式“=IPMT(A2,3,A4,A5)”,然后按“Enter”键返回,即可计算出贷款最后一年的利息(按年支付),如图19-18所示。

应确认所指定的rate参数和nper参数单位的一致性。例如,同样是四年期年利率为12%的贷款,如果按月支付,rate参数应为12%/12,nper参数应为4*12;如果按年支付,rate参数应为12%,nper参数为4。对于所有参数,支出的款项,如银行存款,表示为负数;收入的款项,如股息收入,表示为正数。

图19-17 计算第1个月的利息

图19-18 计算最后一年的利息

Excel 计算付款期间累积支付利息:CUMIPMT函数

CUMIPMT函数用于计算一笔贷款在给定的start_period到end_period期间累计偿还的利息数额。CUMIPMT函数的语法如下:


CUMIPMT(rate,nper,pv,start_period,end_period,type)

其中,rate参数为利率;nper参数为总付款期数;pv参数为现值;start_period参数为计算中的首期,付款期数从1开始计数;end_period参数为计算中的末期;type参数为付款时间类型,为0时付款类型为期末付款,为1时付款类型为期初付款。下面通过实例详细讲解该函数的使用方法与技巧。

打开“CUMIPMT函数.xlsx”工作簿,切换至“Sheet1”工作表,本例的原始数据如图19-9所示。该工作表中记录了某笔贷款的年利率、贷款期限、现值,要求根据给定的数据计算该笔贷款在第1个月所付的利息。具体的操作步骤如下。

图19-9 原始数据

STEP01:选中A6单元格,在编辑栏中输入公式“=CUMIPMT(A2/12,A3*12,A4,13,24,0)”,用于计算该笔贷款在第2年中所付的总利息(第13期到第24期),输入公式后按“Enter”键返回计算结果,如图19-10所示。

STEP02:选中A7单元格,在编辑栏中输入公式“=CUMIPMT(A2/12,A3*12,A4,1,1,0)”,用于计算该笔贷款在第1个月所付的利息,输入公式后按“Enter”键返回计算结果,如图19-11所示。

图19-10 计算总利息

图19-11 计算第1个月的利息

应确认所指定的rate参数和nper参数单位的一致性。例如,同样是四年期年利率为10%的贷款,如果按月支付,rate参数应为10%/10,nper参数应为4*12;如果按年支付,rate参数应为10%,nper参数为4。

如果参数rate≤0、参数nper≤0或参数pv≤0,函数CUMIPMT返回错误值“#NUM!”。如果参数start_period<1、参数end_period<1或参数start_period>end_period参数,函数CUMIPMT返回错误值“#NUM!”。如果type参数不是数字0或1,函数CUMIPMT返回错误值“#NUM!”。

Excel 计算应计利息:ACCRINTM函数

ACCRINTM函数用于计算到期一次性付息有价证券的应计利息。ACCRINTM函数的语法如下:


ACCRINTM(issue,settlement,rate,par,basis)

其中,参数issue为有价证券的发行日。settlement为有价证券的到期日。rate为有价证券的年息票利率。par为有价证券的票面价值,如果省略par,函数ACCRINTM视par为1000。basis为日计数基准类型。参数basis的日计数基准如表19-1所示。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ACCRINTM函数.xlsx”工作簿,切换至“Sheet1”工作表,本例的原始数据如图19-5所示。该工作表中记录了某债券的发行日、到期日、息票利率、票面值等信息,要求根据给定的数据计算满足这些条件的应计利息。具体的操作步骤如下。

选中A8单元格,在编辑栏中输入公式“=ACCRINTM(A2,A3,A4,A5,A6)”,用于计算满足上述条件的应计利息,输入公式后按“Enter”键返回计算结果,如图19-6所示。

图19-5 原始数据

图19-6 计算应计利息结果

如果issue参数或settlement参数不是有效日期,函数ACCRINTM返回错误值“#VALUE!”。如果利率为0或票面价值为0,函数ACCRINTM返回错误值“#NUM!”。如果参数basis<0或参数basis>4,函数ACCRINTM返回错误值“#NUM!”。如果参数issue≥settlement参数,函数ACCRINTM返回错误值“#NUM!”。ACCRINTM的计算公式如下:

式中:

A=按月计算的应计天数。在计算到期付息的利息时指发行日与到期日之间的天数。

D=年基准数。

Excel 计算应计利息:ACCRINT函数详解

ACCRINT函数用于计算定期付息证券的应计利息。ACCRINT函数的语法如下:


ACCRINT(issue,fi rst_interest,settlement,rate,par,frequency,basis,calc_method)

其中,issue参数表示有价证券的发行日;first_interest参数表示证券的首次计息日;settlement参数表示证券的结算日,结算日是指在发行日之后,证券卖给购买者的日期;rate参数表示有价证券的年息票利率;par参数表示证券的票面值,如果该参数被省略,则ACCRINT函数将使用1000。frequency参数表示年付息次数,如果按年支付,参数frequency=1;按半年期支付,参数frequency=2;按季支付,参数frequency=4。basis参数表示日计数基准类型。如表19-1所示为basis参数的日计数基准。

表19-1 参数basis的日计数基准

参数basis的日计数基准

calc_method参数表示逻辑值,指定当结算日期晚于首次计息日期时,用于计算总应计利息的方法。如果值为TRUE(1),则计算从发行日到结算日的总应计利息;如果值为FALSE(0),则计算从首次计息日到结算日的应计利息。如果此参数被省略,则默认值为TRUE。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ACCRINT函数.xlsx”工作簿,切换至“Sheet1”工作表,本例的原始数据如图19-1所示。该工作表中记录了国债的发行日、首次计息日、结算日、票息率、票面值等信息,要求根据给定的数据计算出定期支付利息的债券的应计利息。具体的操作步骤如下。

STEP01:选中A10单元格,在编辑栏中输入公式“=ACCRINT(A2,A3,A4,A5,A6,A7,A8)”,用于计算满足上述条件的国债应计利息,输入公式后按“Enter”键返回计算结果,如图19-2所示。

STEP02:选中A11单元格,在编辑栏中输入公式“=ACCRINT(DATE(2010,3,5),A3,A4,A5,A6,A7,A8)”,用于计算满足上述条件(除发行日为2010年3月5日之外)的应计利息,输入公式后按“Enter”键返回计算结果,如图19-3所示。

STEP03:选中A12单元格,在编辑栏中输入公式“=ACCRINT(DATE(2010,4,5),A3,A4,A5,A6,A7,A8,TRUE)”,用于计算满足上述条件(除发行日为2010年4月5日且应计利息从首次计息日计算到结算日之外)的应计利息,输入公式后按“Enter”键返回计算结果,如图19-4所示。

图19-1 原始数据

计算国债应计利息

图19-2 计算国债应计利息

计算(除发行日为2010年3月5日之外)的应计利息

图19-3 计算(除发行日为2010年3月5日之外)的应计利息

计算(除发行日为2010年4月5日且应计利息从首次计算日计算到结算日之外)的应计利息

图19-4 计算(除发行日为2010年4月5日且应计利息从首次计算日计算到结算日之外)的应计利息

参数issue、first_interest、settlement、frequency和basis将被截尾取整。如果参数issue、first_interest或settlement不是有效日期,则ACCRINT函数将返回错误值“#VALUE!”。如果参数rate≤0或参数par≤0,则ACCRINT函数将返回错误值“#NUM!”。如果frequency参数不是数字1、2或4,则ACCRINT将返回错误值“#NUM!”。如果参数basis<0或basis>4,则ACCRINT将返回错误值“#NUM!”。如果参数issue≥settlement,则ACCRINT函数将返回错误值“#NUM!”。

函数ACCRINT的计算公式如下:

其中:

Ai=奇数期内第i个准票息期的应计天数。

NC=奇数期内的准票息期期数。如果该数含有小数位,则向上进位至最接近的整数。

NLi=奇数期内第i个准票息期的正常天数。