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) |