Financial functions cumipmt, cumprinc, db, ddb, dollarde, dollarfr

Excel Function CUMIPMT

The CUMIPMT() function is used to calculate the cumulative interest paid on a loan for a given period.

This function can be used for calculating interest paid between any two dates or whole period.

The parameters are as follows:

interest rate; number of periods; present loan value; start period; period; payment period_type.

The last parameter type specifies payment period type:

0 - Payment made at the end of the period

1 - Payment made at the start of the period.

Loan Amount: \$100000

To be paid in 5 years

Annual interest: 7%

 Year CUMIPMT Formula Year 1 -6451.64215920455 =CUMIPMT(7%/12,60,100000,1,12,0) Year 2 -5200.31560031025 =CUMIPMT(7%/12,60,100000,13,24,0) Year 3 -3858.53054329593 =CUMIPMT(7%/12,60,100000,25,36,0) Year 4 -2419.74773601835 =CUMIPMT(7%/12,60,100000,37,48,0) Year 5 -876.955203268124 =CUMIPMT(7%/12,60,100000,49,60,0)

Excel Function CUMPRINC

The CUMPRINC() function is used to calculate the cumulative principal paid on an investment.

The calculation is confined to two specific periods based on a specific interest rate and payment schedule.

The parameters are as follows:

interest rate; number of periods; present loan value; start period; period; payment period_type.

Loan Amount: \$100000

To be paid in 5 years

Annual interest: 7%

 Year CUMPRINC Formula Year 1 -17309.7960892149 =CUMPRINC(7%/12,60,100000,1,12,0) Year 2 -18561.1226481092 =CUMPRINC(7%/12,60,100000,13,24,0) Year 3 -19902.9077051235 =CUMPRINC(7%/12,60,100000,25,36,0) Year 4 -21341.6905124011 =CUMPRINC(7%/12,60,100000,37,48,0) Year 5 -22884.4830451513 =CUMPRINC(7%/12,60,100000,49,60,0)

Excel Function DB

The DB() function is used to calculate the depreciation of an asset for a specified period.

The method of calculation adopted by this function is Fixed declining balance method.

This method reduces the value of the asset by a fixed percentage during each period.

This method is also the most used method in many countries.

The parameters are as follows:

initial cost; salvage value; life of asset in months; period of depreciation; month in year.

The last parameter is usually 12 unless otherwise, any other number of months per year to be used is specified.

Asset Value : 125000

Fixed depreciation per year: 10%

Salvage value : 1000

Period of calculation : 5 Years

 Year DB Formula Year 1 77375 =DB(125000,1000,5,1) Year 2 29479.875 =DB(125000,1000,5,2) Year 3 11231.832375 =DB(125000,1000,5,3) Year 4 4279.328134875 =DB(125000,1000,5,4) Year 5 1630.42401938738 =DB(125000,1000,5,5)

Excel Function DDB

The DDB() function is used to calculate the depreciation of an asset using Double declining balance method.

The double declining method is category of Accelarated declining method.

In this method, the rate of depreciation per year is doubled.

Asset Value : 125000

Fixed depreciation per year: 10%

Study

Period of calculation : 5 Years

 Year DB Formula Year 1 50000 =DDB(125000,1000,5,1) Year 2 30000 =DDB(125000,1000,5,2) Year 3 18000 =DDB(125000,1000,5,3) Year 4 10800 =DDB(125000,1000,5,4) Year 5 6480 =DDB(125000,1000,5,5)

Excel Function DOLLARDE

The DOLLARDE() function converts the dollar value in fraction to its decimal representation.

This function is most often with representing security prices which are given in fractional notation.

This function takes two parameters:

fractional_dollar; fraction

 Fractional value fraction DOLLARDE Formula 2.05 16 2.3125 =DOLLARDE(L75,M75) 5.12 16 5.75 =DOLLARDE(L76,M76) 5.001 32 5.003125 =DOLLARDE(L77,M77) 8.201 18 9.11666666666667 =DOLLARDE(L78,M78) 6.024 32 6.075 =DOLLARDE(L79,M79)

Excel Function DOLLARFR

The DOLLARFR() function converts the dollar value in decimal to its fractional representation.

This function does the reverse of the DOLLARDE function.

This function takes two parameters:

fractional_dollar; fraction

 Decimal value fraction DOLLARDE Formula 2.3125 16 2.05 =DOLLARFR(L88,M88) 5.75 16 5.12 =DOLLARFR(L89,M89) 5.003125 32 5.001 =DOLLARFR(L90,M90) 9.11666666666667 18 9.021 =DOLLARFR(L91,M91) 6.075 32 6.024 =DOLLARFR(L92,M92)