# How to Calculate Simple Interest on Reducing Balance in Excel

## 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. 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, we have to calculate the Rate for a month: Annual Percentage Rate / 12 =Â 0.00792

We also 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

This 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.

### Step 1 – Use the PMT Function to Calculate Payment

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.

• Select cell C11 and use the PMT function in that cell:
`=PMT(E\$4/E\$6,E\$5*E\$6,E\$7)`

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.

• Press Enter. You will get the payment (\$8,768.35) as the output of the PMT function.

• AutoFill the PMT function to the rest of the cells in column C.

• You will be able to calculate the payment of the loan per month which has been given in the below screenshot.

### Step 2 – Apply the IPMT Function to Determine Interest of Payment

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.

• Select the cell D11 and use the IPMT function in the Formula Bar:
`=IPMT(E\$4/E\$6,B11,E\$5*E\$6,E\$7)`

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.

• Press Enter. You will get the interest of payment (\$791.67).

• AutoFill the IPMT function to the rest of the cells in column D.

### Step 3 – Insert the PPMT Function to Calculate Principal of Payment

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.

• Select the cell E11 and type the PPMT function in the Formula Bar:
`=PPMT(E\$4/E\$6,B11,E\$5*E\$6,E\$7)`

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.

• Press Enter.

• AutoFill the PPMT function to the rest of the cells in column E.

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

• Select cell F11.

• Use the following formula:
`=F10+E11`

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

• Press Enter.

• AutoFill to the other cells in the column to determine when the balance will be reduced to 0.

## Things to Remember

Each month, the loan debt is decreased as a result of the 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF