Get FREE Advanced Excel Exercises with Solutions!

While working with borrowed loans, we have to calculate the amount of interest or capital that we have to pay for that loan. We can easily calculate interest on a loan in Excel using in-built financial functions named **PMT, IPMT, PPMT**, and **CUMIPMT**. In this article, I will show you how to use these functions to calculate the interest for a given period, interest in a given year, and** interest rate.**

**1. Determining Fixed Loan Repayment for Every Month or Year**

You can use **the PMT function** to calculate the fixed interest on a loan in Excel for a certain period.

Letâ€™s assume a scenario where we have a loan that amounts to $5000. The annual interest rate for the loan is 4% per annum. The loan was taken for 5 years. We need to calculate the interest from this given data. In this section, we will discuss five different methods to calculate interest on the loan in Excel.

**Introduction to PMT Function**

**Function Objective:**

Calculates the repayment for a loan based on a constant payment and constant interest rate.

**Syntax:**

**=PMT(rate, per, nper, pv, [fv], [type])**

**Argument Explanation:**

**Return Parameter:**

Payment for a loan is based on constant payments and a constant interest rate.

**Step 1:**

- Our first step is to select the cell where we want to have our total repayment for a certain period. We will select cell
**C10**to calculate the total repayment for a month. - Now we will write down the
**PMT**formula in that cell.

** ****Step 2:**

- Upon clicking
**ENTER**, we will get the fixed amount to pay every month or the monthly fixed repayment amount. This amount is the same for every month. It includes the portion of capital or principal and also the interest amount we have to pay in the first month.

**Step 3:**

- We can also calculate fixed repayment for
**every year**. To calculate the repayment amount to pay every year, we have to use the following formula.

- As we can see, we do not have to divide the
**annual interest rate**by 12. We are calculating the amount for a year. And the**Npr**or the total number of payments is now 5 as we have**5 years**to pay off the loan. The image below shows the fixed annual repayment amount.

**Read More: How to Calculate Interest Rate on a Loan in Excel (2 Criteria)**

**2. Calculating the Interest Payment on a Loan for a Specific Month or Year**

Although monthly or yearly repayment amounts over the loan term will be the same, the proportion of interest and capital you repay each period varies over the term. At the start of the loan you pay mostly interest and a little capital, but by the end of the term, you pay a little interest and mostly capital.

For each period of the loan, you can calculate the interest amount by using **the IPMT function**.

**Introduction to IPMT Function**

**Function Objective:**

Calculates the **interest payment** for a given period like a specific month or year.

**Syntax:**

**=IPMT(rate, per, nper, pv, [fv], [type])**

**Argument Explanation:**

**Return Parameter:**

Interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

**Step 1:**

- Our first step is to select a cell and write down the formula of
**IPMT**. We will select cell**C10**and write down the following formula.

**Step 2:**

- Upon clicking
**ENTER**, we will get the interest amount to pay in the first month.

**Step 3:**

- We can also calculate interest payments for
**a specific year**. To calculate the interest amount for the last year, we have to use the following formula.

- As we can see, we do not have to divide the
**annual interest rate**by 12. We are calculating the amount for a year. And the**Pr**or the period for which we want to find the interest is now**5**as we are calculating the interest amount for the last or**5th year**.**Total Period (F6)**is also 5 as our total period is 5 years. The image below shows the fixed annual interest amount to pay in the last or**5th year**.

- We can also calculate the weekly, quarterly, and semi-annual interest payments using
**IPMT**.

**Read More: How to Calculate Interest in Excel with Payments (3 Examples)**

**3. Computing Capital Payment for a Certain Interest Rate on a Loan**

We can also calculate the capital payment for a specific month or year using** the PPMT function** of Excel.

**Introduction to PPMT Function**

**Function Objective:**

Calculates the **capital payment** for a given period like a specific month or year.

** Syntax:**

**=PPMT(rate, per, nper, pv, [fv], [type])**

**Argument Explanation:**

**Return Parameter:**

The payment on the principal for a given period for an investment is based on periodic, constant payments and a constant interest rate.

**Step 1:**

- We will select cell
**C10**and write down the following formula of**PPMT**.

**Step 2:**

- Upon clicking
**ENTER**, we will get the capital amount to pay in the first month.

**Step 3:**

- We can also calculate capital payments for
**a specific year**. To calculate the capital amount for the last year, we have to use the following formula.

- As we can see, we do not have to divide the
**annual interest rate**by 12. We are calculating the amount for a year. And the**Pr**or the period for which we want to find the interest is now**5**as we are calculating the capital amount for the last or**5th year**.**Total Period (F6)**is also 5 as our total period is 5 years. The image below shows the annual capital amount to pay in the last or**5th year**.

**Note: The sum of interest payment and capital payment will be equal to the fixed repayment amount that we calculated in the first method.**

- Interest payment for the first month = $16.67 [
**Using IPMT Function**]

Capital payment for the first month = $75.42 [**Using PPMT Function**]

Total payment for the first month = 16.67+75.42 = 92.09 = Total Repayment for Every Month that we calculated using **PMT function in method 1**

So, the total amount to repay will be equal for every identical period. But the interest amount and capital amount will vary from period to period.

- We can also calculate the weekly, quarterly, and semi-annual capital payments using
**PPMT**.

**Read More: Create Late Payment Interest Calculator in Excel and Download for Free**

**Similar Readings**

**How to Create GST Interest Calculator in Excel (with Useful Steps)****How to Make HELOC Payment Calculator Using Principal and Interest in Excel****Daily Loan Interest Calculator in Excel (Download for Free)****How to Calculate Home Loan Interest in Excel (2 Easy Ways)****How to Create Prejudgment Interest Calculator in Excel**

**4. Calculating Cumulative Loan Interest for a Specific Month or Year in Excel**

You can use **the CUMIPMT function** to calculate the cumulative interest on a loan in Excel for a specific period like a specific month or year.

**Introduction to CUMIPMT Function**

**Function Objective:**

Cumulative interest paid on a loan between start_period and end_period.

**Syntax:**

**=CUMIPMT(rate, nper, pv, start_period, end_period, [type])**

**Argument Explanation:**

**Return Parameter:**

Cumulative interest paid on a loan over a specific period.

**Step 1:**

- First, we will select cell
**C10**and write down the formula below for cumulative interest for the first month.

**Step 2:**

After that, we will press** ENTER **and we will get the cumulative interest amount for the first month.

**Step 3:**

- We can also calculate cumulative interest for
**a specific year**. To calculate the cumulative interest amount to pay in the**last or 5th year**, we have to use the following formula.

- Arguments are almost similar to those used for calculating cumulative interest for the first month except for starting and ending periods. The
**Starting_period**is**49**as the last or fifth year starts after the 4th year or (4X12) = 48 months and ends after (5X12) = 60 months. So, the**End_period**is**60**. The image below shows the fixed annual repayment amount.

**Read More:**** How to Use Cumulative Interest Formula in Excel (3 Easy Ways)**

**5. Using Excel FV Function to ****Calculate Compound Interest on a Loan **

You can also calculate compound interest on a loan in Excel using **the FV function**.

**Introduction to FV Function**

**Function Objective:**

Calculates the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump-sum payment.

**Syntax:**

**FV(rate,nper,pmt,[pv],[type])**

**Argument Explanation:**

**Return Parameter:**

Future value.

**Step 1:**

- First, we will select cell
**C10**and write down the formula below for compound interest for the first month.

**Step 2:**

- Then upon clicking
**ENTER**, we will get the compound interest for the period.

**Read More:**** How to Calculate Accrued Interest on Fixed Deposit in Excel (3 Methods)**

**Things to Remember**

- Type argument in these functions is usually
**optional**. The number 0 or 1 indicates when payments are due. If the type is omitted, it is assumed to be 0. - Set the Type argument to 0 if payments are due at the end of the period. Set the Type argument to 1 if payments are due at the beginning of the period.

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**Conclusion**

In this article, we have learned to calculate interest on a loan in Excel. We learned how to calculate the total fixed repayment for every period, interest and capital payment for a specific period, cumulative and compound interest payment for a specific month or year using functions like** PMT, IPMT, PPMT,** **CUMIPMT, and FV **functions in Excel. I hope from now on you will find it very easy to calculate interest on your loans in Excel. If you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!

## Related Articles

**How to Create SIP Interest Calculator in Excel (with Easy Steps)****How to Create TDS Interest Calculator in Excel****Calculation of Interest During Construction in Excel****Car Loan Calculator in Excel Sheet – Download Free Template****How to Calculate Credit Card Interest in Excel (3 Easy Steps)****Bank Interest Calculator in Excel Sheet – Download Free Template**