Financial functions accrint, accrintm, amordegrc, amorlinc
Excel Function ACCRINT
The ACCRINT() function is used to calculate the interest accrued for a security that pays interest periodically.
This function takes the following parameters:
security issue date;first interest date;settlement date; annual coupon rate; par value; frequency and day count.
Issue Date | 1st Interest date | Settlement date | coupon rate | Par value | Payment frequency | ACCRINT | Formula |
01/02/2017 | 01/08/2017 | 31/12/2018 | 0.08 | 2000 | 1 | 306.666666666667 | =ACCRINT(L5,M5,N5,O5,P5,2) |
01/03/2017 | 03/07/2017 | 31/07/2018 | 0.02 | 1580 | 1 | 44.7666666666667 | =ACCRINT(L6,M6,N6,O6,P6,2) |
20/07/2017 | 02/01/2018 | 01/04/2018 | 0.06 | 10000 | 2 | 418.333333333333 | =ACCRINT(L7,M7,N7,O7,P7,2) |
01/03/2018 | 07/03/2018 | 09/09/2018 | 0.09 | 25000 | 2 | 1175 | =ACCRINT(L8,M8,N8,O8,P8,2) |
Excel Function ACCRINTM
The ACCRINTM() function is similar to ACCRINT except that it calculates the interest at maturity.
There is no first interest date associated with this function.
Issue Date | Settlement date | coupon rate | Par value | ACCRINTM | Formula | |
01/02/2017 | 31/12/2018 | 0.08 | 2000 | 306.6667 | =ACCRINTM(L17,M17,N17,O17) | |
01/03/2017 | 31/07/2018 | 0.02 | 1580 | 44.76667 | =ACCRINTM(L18,M18,N18,O18) | |
20/07/2017 | 01/04/2018 | 0.06 | 10000 | 418.3333 | =ACCRINTM(L19,M19,N19,O19) | |
01/03/2018 | 09/09/2018 | 0.09 | 25000 | 1175 | =ACCRINTM(L20,M20,N20,O20) |
Excel Function AMORDEGRC
The AMORDEGRC() function is used to calculate the linear depreciation of an asset for a given accounting period.
The calculation is linear and on prorated basis.
This function is often used in corporate financing where depreciation of buildings has to be included in the
asset accounting.
The function takes the following parameters.
cost of asset; date of purchase; first period; salvage amount; period of calculation; rate of depreciation; basis.
The last parameter basis is optional and specifies the financial day count to be used, with the default value being 0.
cost of asset | Purchase date | 1st period date | salvage | period of calculation | depreciation rate | AMORDEGRC | Formula |
1000 | 01/01/2017 | 03/06/2017 | 20 | 1 | 0.2 | 332 | =AMORDEGRC(L28,M28,N28,O28,P28,Q28) |
1500 | 12/02/2017 | 02/07/2017 | 25 | 2 | 0.15 | 300 | =AMORDEGRC(L29,M29,N29,O29,P29,Q29) |
2450 | 22/03/2017 | 12/08/2017 | 38 | 1 | 0.1 | 553 | =AMORDEGRC(L30,M30,N30,O30,P30,Q30) |
5000 | 01/02/2016 | 12/12/2017 | 40 | 2 | 0.125 | 449 | =AMORDEGRC(L31,M31,N31,O31,P31,Q31) |
Excel Function AMORLINC
The AMORLINC() function is used to calculate the linear depreciation of an asset for a given accounting period.
The calculation is linear and on prorated basis.
The function takes the following parameters.
cost of asset; date of purchase; first period; salvage amount; period of calculation; rate of depreciation; basis.
The difference between this function and AMORDEGRC is that, in the latter, a hidden calculation is
included automatically, which is not found in the former.
cost of asset | Purchase date | 1st period date | salvage | period of calculation | depreciation rate | AMORLINC | Formula |
1000 | 01/01/2017 | 03/06/2017 | 20 | 1 | 0.2 | 200 | =AMORLINC(L43,M43,N43,O43,P43,Q43) |
1500 | 12/02/2017 | 02/07/2017 | 25 | 2 | 0.15 | 225 | =AMORLINC(L44,M44,N44,O44,P44,Q44) |
2450 | 22/03/2017 | 12/08/2017 | 38 | 1 | 0.1 | 245 | =AMORLINC(L45,M45,N45,O45,P45,Q45) |
5000 | 01/02/2016 | 12/12/2017 | 40 | 2 | 0.125 | 625 | =AMORLINC(L46,M46,N46,O46,P46,Q46) |