In this article, we will create an EMI loan amortization in Microsoft Excel.
Use an Excel macro.
Step 1
- Start Microsoft Excel application.
- On the view tab uncheck the Gridlines.
The following figure shows the Excel working area.
![]()
- Add the title and names to Column C as in the following:
![]()
- Select the Range and right-click to change the Range (“D3”) name as in the following.
- Select the Range and right-click to change all range names D3 to D6 as in the following .
The following shows the change in Range (“D3”) name.
![]()
The following shows the change in Range (“D4”) name.
![]()
The following shows the change in Range (“D5”) name.
![]()
The following shows the change in Range (“D6”) name.
![]()
Step 2
- Add the following formula in Range(“D7”) =PMT(Rate/Npayment,Npayment*Year,Amount1,0).
- The following shows the PMT formula declarations:
![]()
Step 3
- On the insert tab click shapes then select the Round Rectangle shape in the Rectangle collections.
- Draw two rectangles and name them View and Clear as in the following.
The following shows drawing shapes in the spreadsheet.
![]()
(Hint: If you are new to Excel macros then read my previous article (Report Automation Template Using Excel Macro ) to learn how to assign Excel objects.)
- Select View shape and right-click to choose Assign Macro for Excel objects as in the following:
![]()
- Click the new button to activate VBA.
The following shows the assign macro for the Rectangle shape.
![]()
- Add the following code between Sub RoundedRectangle1_Click() and End Sub
- Sub RoundedRectangle3_Click()
- Dim i As Integer
- Dim k As Integer
- Dim j As Integer
- Dim totalpayment As Integer
- Dim setformula(1 To 4) As Variant
- Dim blnce As Variant
- Dim blnce1 As Variant
- Dim SourceRange As Range
- Dim Fillrange As Range
- Dim Source As Range
- Dim Fill As Range
- totalpayment = Sheet1.Range("D4").Value * Sheet1.Range("D5").Value
- MsgBox totalpayment
- k = 11
- j = 1
- For i = 1 To totalpayment
- Sheet1.Cells(k, j).Value = i
- k = k + 1
- Next i
- Sheets("sheet1").Activate
- setformula(1) = "=PMT(Rate/Npayment,Npayment*Year,Amount,0)"
- setformula(2) = "=PPMT(Rate/12,A11,Year*Npayment,Amount,0)"
- setformula(3) = "=IPMT(Rate/12,A11,Year*Npayment,Amount,0)"
- blnce = "=Amount+C11"
- Sheet1.Range("E11").Formula = blnce
- blnce1 = "=E11+C12"
- Sheet1.Range("E12").Formula = blnce1
- Sheet1.Range("B11:D11").Formula = setformula
- Set SourceRange = Worksheets("sheet1").Range("B11:D11")
- Set Fillrange = Worksheets("sheet1").Range("B11:D" & Range("A" & Rows.Count).End(xlUp).Row)
- SourceRange.AutoFill Destination:=Fillrange
- Set Source = Worksheets("sheet1").Range("E12")
- Set Fill = Worksheets("sheet1").Range("E12:E" & Range("A" & Rows.Count).End(xlUp).Row)
- Source.AutoFill Destination:=Fill
- End Sub
- Select the clear shape and right-click to choose Assign Macro and use the following:
![]()
- Click the New button to activate VBA.
The following shows how to assign a macro for a Rectangle shape.
![]()
- Add the following code between Sub RoundedRectangle2_Click() and End Sub.
- Sub RoundedRectangle5_Click ()
- Dim Fill as Range
- Set Fill = Worksheets ("sheet1").Range ("A11: E" & Range ("A" & Rows. Count).End (xlUp).Row)
- Fill.ClearContents
- End Sub
- Press Alt+F11 to exit from VBA.
Output
- Press the View button
The following figure shows the result of payment details.
![]()
![]()
- Press the Clear button.
The following clearly shows the result in the spreadsheet.
![]()
Conclusion
I hope you liked this article. Please provide your valuable suggestions. It will be useful for Excel macro beginners and those new to Excel.
(Hint: use Alt+F11 to activate VBA.)