When youâ€™re dealing with your personal finances, you have to have clear knowledge about it. Not knowing general financial concepts can lead you to financial disasters. There are two types of financial terms to pay the loan. The first one is** Flat Rate Interest **and another is **Reducing the Balance Rate**. If you read this article, you will be clear about these two financial terms. We can easily calculate simple interest in reducing the balance in Excel. This will save you a lot of time and energy. Today, in this article, we’ll learn **four **quick and suitable steps to calculate the simple interestÂ on reducing the balance in Excel effectively with appropriate illustrations.

## Introduction to Simple Interest on Reducing Balance

There are two types of financial terms to pay the loan. The first one is **Flat Rate Interest** and another is **Reducing the Balance Rate**. Reducing the Balance Rate is a better approach when you are handling your loan. This is widely used by banks and financial institutions. We will calculate the Reducing Rate of Interest using the above loan details.

- A loan of amount
**$100,000.00** - Annual percentage rate (APR)
**50%** - Tenure of the Loan:
**1 year** - Payment Frequency:
**Monthly**

If the Payment Frequency is Monthly, then at first, we have to calculate the Rate for a month:

= Annual Percentage Rate / 12

= 9.50% / 12

= 0.792%

= 0.00792

The next thing is: we have to calculate the monthly PMT for the loan using Excelâ€™s PMT function:

=PMT(rate, nper, loan)

Here,

- rate = 0.00792
- nper = 12; [nper = number of total periods]
- loan = -100,000

It returns the value: **(8,768.35)**

## 4 Quick Steps to Calculate Simple Interest on Reducing Balance in Excel

Letâ€™s assume we have an **Excel **large worksheet that contains the information about the simple interest in the reducing balance. We will calculate the **simple interest** in reducing balance from our dataset using Excelâ€™s **PMT**, **IPMT**, and **PPMT** financial formulas. **PMT **stands for **Payment**, **IPMT **is used to get the **interest of payment**, and **PPMT **is used to get the **principal payment**. We will apply these financial functions to calculate the simple interest in reducing balance. We will do those **four **easy and quick steps, which are also time-saving. Hereâ€™s an overview of the dataset for today’s task.

Letâ€™s follow the instructions below to calculate the simple interest on the reduced balance!

### Step 1: Use PMT Function to Calculate Payment

First of all, we will calculate the payment by using the **PMT** financial function. One can pay oneâ€™s payment every week, month, or year by using this function. The syntax of the function is,

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

Where the **rate **is the interest rate of the loan, **nper **is the total number of payments per loan, **pv **is the present value i.e. the total value of all the loan payments at present, **[fv]** is future value i.e. the cash balance one wants to have after the last payment is done, and **[type]** specifies when the payment is due.

Letâ€™s follow the instructions below to calculate the payment using the **PMT function!**

- First, select cell
**C11**and write down the**PMT function**in that cell. The PMT function is,

`=PMT(E$4/E$6,E$5*E$6,E$7)`

- Where
**E$4**is the**Annual Interest Rate**,**E$6**is the**number of payments per year**,**E$5**is the**number of years**, and**E$7**is the**original price of the car**. We use the**dollar ($) sign**for the absolute reference of a cell.

- Hence, simply press
**ENTER**on your keyboard. As a result, you will get the payment**($8,768.35)**as the output of the**PMT function**.

- Further,
**AutoFill**the**PMT function**to the rest of the cells in column**C**.

- After completing the above process, you will be able to calculate the payment of the loan per month which has been given in the below screenshot.

**Read More:** Convert Compound Interest to Simple Interest in Excel

### Step 2: Apply IPMT Function to Determine Interest of Payment

After calculating the payment, now, we will calculate the interest of payment by applying the** IPMT function**. The syntax of the function is,

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

Where the **rate **is the interest rate per period, **per **is a specific period; must be between 1 and nper, **nper **is the total number of payment periods in a year, **pv **is the current value of a loan or investment, **[fv]**is the nper payments future worth, **[type]** is the payments behavior.

Letâ€™s follow the instructions below to calculate the interest of payment by using the **IPMT function!**

- First of all, select cell
**D11**and type the**IPMT function**in the**Formula Bar**. The**IPMT function**in the**Formula Bar**is,

`=IPMT(E$4/E$6,B11,E$5*E$6,E$7)`

- Where
**E$4**is the**Annual Interest Rate**,**E$6**is the**number of payments per year**,**B11**is the**number of months**,**E$5**is the**number of years**, and**E$7**is the**original price**of the car. We use the**dollar ($) sign**for the absolute reference of a cell.

- Further, simply press
**ENTER**on your keyboard. As a result, you will get the interest of payment**($791.67)**as the output of the**IPMT**Â**function.**

- Hence,
**AutoFill**the**IPMT function**to the rest of the cells in column**D**. - After completing the above process, you will be able to calculate the
**interest of payment**of the simple interest on the reduced balance per month which has been given in the below screenshot.

### Step 3: Insert PPMT Function to Calculate Principal of Payment

In this step, we will calculate the principal of payment by using the **PPMT function**. This is the easiest financial function. The syntax of the function is,

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

Where the **rate **is the interest rate per period, **per **is a specific period; must be between 1 and nper, **nper **is the total number of payment periods in a year, **PV **is the current value of a loan or investment, **[fv]**is the nper payments future worth, **[type]** is the payments behavior.

Letâ€™s follow the instructions below to calculate the principal of payment by using the **PPMTÂ function**.

- First of all, select cell
**E11**and type the**PPMT function**in the**Formula Bar**. The**PPMT function**in the**Formula Bar**is,

`=PPMT(E$4/E$6,B11,E$5*E$6,E$7)`

- Where
**E$4**is the**Annual Interest Rate**,**E$6**is the number of**payments per year**,**B11**is the number of**month**,**E$5**is the number of**years**,**E$7**is the**original price**of the car. We use the**dollar ($) sign**for the absolute reference of a cell.

- After that, again, press
**ENTER**on your keyboard. As a result, you will get the interest of payment**($7,976.68)**as the output of the**PPMT function**.

- Further,
**AutoFill**the**PPMT function**to the rest of the cells in column**E**. - While performing the above process, you will be able to calculate the
**principal payment**of the simple interest on the reduced balance per month which has been given in the below screenshot.

**Read More: **How to Calculate Simple Interest Loan Payments in Excel

### Step 4: Use Mathematical Formula to Calculate Simple Interest on Reducing Balance

This is the final step to calculating the simple interest on the reducing balance in **Excel**. After calculating the payment per month, the interest of payment per month, and the principal of payment per month, now, we will calculate the balance of the loan by using those values. Follow the instructions below to calculate the balance using the **mathematical **function!

- First of all, select cell
**F11**to apply the mathematical summation formula.

- After selecting cell
**F11,**write down the following formula in the**Formula Bar**. The formula is,

`=F10+E11`

- Where
**F10**is the initial price of the car, and**E11**is the total payment after the**first month**.

- After that, press
**ENTER**on your keyboard. As a result, you will get the balance after the first month. The balance becomes**$92,023.32**after the**first month**.

- While performing the above process, you will be able to calculate the
**simple interest on the reducing balance**per month. After the 12th month, you will be able to pay the total loan which has been given in the below screenshot.

## Things to Remember

👉 Each month, the loan debt is decreased as a result of your payments. The accumulated interest must decrease as the loan balance decreases.

👉** #DIV/0 **error occurs when the **denominator **is **0 **or the reference of the cell is not **valid**.

👉 The **#NUM!** error occurs when the **per** argument is less than 0 or is greater than the **nper** argument value.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

I hope all of the suitable steps mentioned above to **calculate simple interest on the reducing balance **will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

**Related Articles**

- How to Calculate Simple Interest and Compound Interest in Excel
- How to Calculate Daily Simple Interest in Excel

**<< Go Back to Simple Interest Formula in Excel |Â ****Excel for Finance**** | ****Learn Excel**