CUMIPMT function - The function of calculating accrued interest in Excel
When you invest in a field, you always expect to get the greatest possible return. You want to calculate how much interest you have to pay when taking out a loan in case of accrualing such numbers. Compared to income? The following article is to use CUMIPMT function to calculate accrued interest. It helps you plan the lowest interest rate payment plan.
Introducing CUMIPMT function
Description: The function helps calculate the accrued interest to be paid from the beginning of the period to the end of a certain investment loan.
Syntax: CUMIPMT (rate, nper, pv, start_period, end_period, type) .
Inside:
- rate: The interest rate to be paid, it is a required parameter.
- nper: The number of periods payable during the borrowing period, which is a required parameter.
- pv: The present value of the loan, is the required parameter.
- start_period: The first payment period of the loan, is a required parameter.
- end_period: The last period of interest payment of the loan, which is the required parameter.
- type: The basis of determining the payment time, is a required parameter. There are 2 values, type = 0 => payment at the end of the period, type = 1 => payment at the beginning of the period.
Example: Give the following data table:
Calculate:
- Interest payable in the first month.
- Interest payable from 2nd to 15th month.
1. Interest paid in the first month
In the cell to calculate, enter the formula: = CUMIPMT (D5 / 12, D6 * 12, D7,1,1,0) .
Result:
2. Interest paid from the 2nd to the 15th month
In the cell to calculate enter the formula: = CUMIPMT (D5 / 12, D6 * 12, D7,2,15,0) .
Result:
From February to November, total 14 months if paying interest by month => Total payable = 14 * 65 = 910 $ but accrued interest from February to 14 has to pay 892.89 $. Thus, accrued interest for many months saves you 1 money.
Above is an example and how to use CUMIPMT function to help you calculate the amount of interest to pay when borrowing investment capital.
Good luck!
You should read it
- How to use the MOD function and QUOTIENT function in Excel
- How to use the SUM function to calculate totals in Excel
- How to use the SUBTOTAL function in Excel
- NPER function - The function calculates the period number of an investment in Excel
- COUPNUM function - The function returns the number of interest payments on a security in Excel
- Basic Excel functions that anyone must know
- How to use Hlookup function on Excel
- DCOUNTA function, how to use the function to count non-empty cells in Excel
May be interested
- How to calculate interest rate using the RATE function in Excelyou can absolutely calculate interest rates right in excel. the following are detailed instructions on how to calculate interest rates using the rate function in microsoft excel.
- Intrate function - Calculate interest rates in Excelthe following article details how to use the intrate function to calculate the interest rate of invested securities.
- DURATION function - The function returns the periodic periodic interest payments in Excelthe following article details how to use the duration function to determine the period of periodic interest payment.
- MS Excel 2007 - Lesson 6: Calculation in Excelexcel supports many calculation functions, logic functions for calculating, filtering, collating and analyzing data. this is also a feature that makes excel an indispensable tool for office jobs. in this article, tipsmake.com will show you how to use the overview calculation function in excel 2007.
- DCOUNTA function, how to use the function to count non-empty cells in Exceldcounta functions in excel to count non-empty cells in database fields or lists according to specific conditions.
- VARP function - Functions for calculating variance based on an entire population in Excelvarp function: the function calculates the variance on the entire set, the arguments of the function can only be arrays or references, where the logical values and presented as numbers when typing directly are counted.
- PRODUCT function in Excel, syntax, usagethe product function in excel helps to quickly calculate the product of multiple values, instead of having to enter each number with a multiplication sign. this function is useful in calculating financial, scientific and accounting data.
- How to use the IF function in Excelthe if function in excel helps you easily check conditions and return corresponding results, providing optimal support in calculating and processing data. this is one of the important functions, widely used to automate the comparison and classification of information.
- Calculation of age in Excelto calculate age in excel, we can use these two functions as year or datedif functions.
- The easiest way to calculate the percentage (%)calculating percentages on excel doesn't have to use excel functions, it's just a normal calculation. so how to calculate the fastest percentage, and also need the most accurate. the following article will guide you how to calculate the most accurate percentage value.