# Create Home Loan Calculator in Excel Sheet with Prepayment Option

We’ve taken a Loan Amount of \$100,000 for 1-year tenure at an 8% interest rate. The reimbursement system is monthly. We have to repay the loan as EMI. We made a prepayment of \$5,000 while taking the loan.

We want to calculate all these entities in the column header of the blank cells. These are the EMI, Interest, Principal, and the Remaining Principal amount.

### Step 1 – Calculate the EMI Amount of Home Loan Calculator with Prepayment Option in Excel Sheet

The EMI, or Equated Monthly Instalment, is one of the equally divided monthly payments that must be settled in order to pay off an outstanding loan within a predetermined period of time.

• Select cell F9 and use the formula below, then hit the Enter button.
`=D4-D5`

D4 and D5 represent the cell references of Loan Amount and Prepayment respectively.

This Principal Remaining amount is at the beginning of the first month. The EMI will be calculated based on this amount.

• Use the formula below in cell C10 and hit Enter.
`=PMT(\$F\$6/\$F\$5,\$F\$4*\$F\$5,\$F\$9)`

F6 is the Interest Rate, F5 is the Payments Per Year, F4 is the Tenure and F9 is the Remaining Principal amount. We used the dollar (\$) sign for the absolute reference of a cell. In this formula, we used the PMT function, which determines the loan payment using fixed payments and a fixed interest rate.

We’ll convert this into a positive value.

• Select cell C10 and edit the formula:
`=ABS(PMT(\$F\$6/\$F\$5,\$F\$4*\$F\$5,\$F\$9))`

• Use the Fill Handle tool and drag it down to cell C21 to get the same EMI amount as other months.

### Step 2 – Count the Interest Per Month

• Select cell D10 and use the formula below, then hit the Enter button.
`=ABS(IPMT(\$F\$6/\$F\$5,B10,\$F\$4*\$F\$5,\$F\$9))`

We used the same arguments and cell references as in Step 1. The new thing that hasn’t been used before is the cell reference B10. It denotes the number of Months. For this particular cell, it’s the first month.

### Step 3 – Determine the Paid-up Capital Per Month

• Select cell E10 and use the formula below, then hit the Enter button.
`=C10-D10`

The EMI consists of the monthly Interest and the monthly paid-up Principal amount.

### Step 4 – Determine the Remaining Principal Amount

• Select cell F10 and use the formula below, then hit the Enter button.
`=F9-E10`

F9 and E10 represent the Principal Remaining of last month and the Principal of the present month, respectively.

• Use the Fill Handle tool and drag it down to cell F21.

### Step 5 – Evaluate the Total Amount to be Paid

• Select cell D6 and use the formula below, then hit the Enter button.
`=SUM(C10:C21)`

The SUM function sums up the EMIs in the range C10:C21.

## Advantages of Making a Prepayment

With a prepayment, the monthly paid-up Principal is increasing continuously, because our EMI is constant over the period of 12 months.

Without making any kind of prepayment, our loan status would be like the image below.

The Total Amount to be Paid here is greater than with a \$5,000 Prepayment.

## Related Articles

<< Go Back to Loan CalculatorFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF