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.