Instructions for using the IPMT function in Excel

Before you decide to invest in a certain field, make sure you consider it very carefully, calculating what the capital is spent and how much is earned over the period of time with how much interest. . The following article is a guide to using IPMT function to help you answer this question.

Instructions for using the IPMT function in Excel Picture 1

Description: The function returns the interest payment for an investment within a certain agreed period.

Syntax: IPMT (rate, per, nper, pv, [fv], [type]) .

Inside:

- rate: The interest calculated periodically, is required parameters.

- per: The number of periods for interest calculation is from 1 -> nper, is a required parameter.

- nper: The total number of terms to be paid during the investment period, is the required parameter.

- pv: The present value of the loan, is the required parameter.

- fv: Future value obtained after investment, is an optional parameter if the default value is omitted to 0.

- type: Time of interest payment, Optional parameter if omitted by default is 0. There are 2 values of type = 0 => final payment, type = 1 => payment at the beginning of the period.

Example: Give the following data table:

Instructions for using the IPMT function in Excel Picture 2

Calculate:

- Interest is due in the 1st month after investing.

- Interest due in the last year of the loan.

Perform the problem:

1. Interest is due in the 1st month after investing

In the cell to calculate enter the formula: = IPMT (D5 / 12, D6, D7 * 12, D8) .

Instructions for using the IPMT function in Excel Picture 3

Result:

Instructions for using the IPMT function in Excel Picture 4

2. Interest is due in the last year of the loan

In the cell to calculate enter the formula: = IPMT (D5,5, D7, D8) .

Instructions for using the IPMT function in Excel Picture 5

Result:

Instructions for using the IPMT function in Excel Picture 6

The above is a detailed guide on how to pay interest when you decide to invest in a certain period of time. Compare it with the interest payments and the profits earned and then make a decision.

I wish you the best investment efficiency.

Isabella Humphrey

Update 19 May 2020

You should read it

May be interested

How to use the MOD function and QUOTIENT function in Excel
injury in excel has many ways of doing it, can be used manually or using the calculation function.
8 little-known Excel functions that can save you a lot of work
even seasoned excel users often find themselves stuck performing tasks manually that could be automated with a few clever functions.
How to use MID functions to get strings in Excel
mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
How to use Excel's VALUE function
excel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
Instructions for using the TRIMRANGE function to clean up Excel tables
trimrange is a brand new excel function that is being rolled out to users. this new function has the ability to remove blank rows from a range of data.
How to use the function to delete spaces in Excel
deleting white space with functions in excel makes it easier for users to handle content, instead of traditional editing.
How to fix the SUM function doesn't add up in Excel
in the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
Instructions on how to count words in cells in Excel
in many cases you want to count the number of characters in a text string (text string) to calculate or combine with other functions, or to do something. you can then use the trim function, combining the len function, the substitute function. using the trim function to remove spaces, use the substitute function to replace the current text with new text in a text string, ...
How to use the SUBTOTAL function in Excel
the subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values in excel
Save time with these text formatting functions in Microsoft Excel
microsoft excel is a main application for anyone who has to work with numbers, from students to accountants. but its usefulness extends beyond a large database, it can do a lot of great things with text. the functions listed below will help you analyze, edit, convert, change text and save many hours of boring and repetitive tasks.