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