To help you work faster when calculating in Excel. The following article will summarize the most detailed functions for calculating interest, accumulated principal, and asset depreciation on Excel.

first . CUMIPMT . function

• Function: calculate the accrued interest for the loan from the first period to the last period.
• Syntax: CUMIPMT (rate, nper, pv, start_period, end_period, type)
• Using:

-Rate: Interest rate, required parameter

-Nper: Total number of payment periods, required parameter

-Pv: Current value, required parameter

-Start_period : First period, required parameter

-End_period: Last period, required parameter

-Type: Payment term, required parameter

+ Type = 0 : Payment at the end of the period

+ Type = 1 : Payment at the beginning of the period

Example: For the following spreadsheet, the values entered correspond to the parameters of the function in Excel. Let's calculate:

– Interest payable on the loan from period 9 to period 16

-The amount of interest payable in the first month

2 . CUMPRINC . function

• Function : calculate the accumulated principal amount paid for the loan from the first period to the last period.
• Syntax: CUMPRINC(rate, nper, pv, start_period, end_period, type)
• Using:

-Rate: Interest rate, required parameter

-Nper: Total number of payment periods, required parameter

-Pv: Current value, required parameter

-Start_period : First period, required parameter

-End_period: Last period, required parameter

-Type: Payment term, required parameter

+ Type = 0 : Payment at the end of the period

+ Type = 1 : Payment at the beginning of the period

Example: Given the following worksheet, the values entered correspond to the parameters of the function in Excel, calculate:

-Total principal payable of the loan from period 9 to period 16

-Total principal payable in the first month

3 . DB . function

• Function: calculate the depreciation of assets with specific maturities according to the fixed declining balance method.
• Syntax: DB(cost,salvage,life,period,[month])
• Using:

-Cost: initial cost of the product, required parameter

-Salvage: value after depreciation (recovery value of the asset), required parameter

-Life: Number of depreciation periods, required parameters

-Period: Period you want to depreciate, required parameter

-Month: The number of months of the first year, if omitted, the default is 12

Example: Given the spreadsheet as shown below, with the entered data equivalent to the parameters of the DB function in Excel, calculate:

– Depreciation in the first year:

-Depreciation in the 6th year in 7 months

4 . DDB . function

• Function: Calculates the depreciation of assets for the specified period using the compound declining balance method
• Syntax: DDB(cost,salvage,life,period,[factor])
• Using:

-Cost: initial cost of the product, required parameter

-Salvage: value after depreciation (recovery value of the asset), required parameter

-Life: Number of depreciation periods, required parameters

-Period: Period you want to depreciate, required parameter

-Factor: Balance descending rate, if omitted will default to 2 (double reduction method)

Example: Given a spreadsheet with the data entered corresponding to the parameters of the DDB function in Excel, calculate:

– Calculate the first month's depreciation

-Calculate depreciation in the first year

– Calculate depreciation in the second year with factor = 1.5

Good luck !