EMI Loan Amortization in Microsoft Excel

In this article, we will create an EMI loan amortization in Microsoft Excel.

 

Use an Excel macro.

Step 1

1. Start Microsoft Excel application.
   
   
2. On the view tab uncheck the Gridlines.
   
   The following figure shows the Excel working area.
   
   
   
   
3. Add the title and names to Column C as in the following:
   
   
   
   
4. Select the Range and right-click to change the Range (“D3”) name as in the following.
   
   
5. 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

1. Add the following formula in Range(“D7”)  =PMT(Rate/Npayment,Npayment*Year,Amount1,0).
   
2. The following shows the PMT formula declarations:
   
   
   

Step 3

1. On the insert tab click shapes then select the Round Rectangle shape in the Rectangle collections.
   
   
2. 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.)
   
   
3. Select View shape and right-click to choose Assign Macro for Excel objects as in the following:
   
   
   
   
4. Click the new button to activate VBA.
   
   The following shows the assign macro for the Rectangle shape.
   
   
   
   
5. 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

6. Select the clear shape and right-click to choose Assign Macro and use the following:
   
   
   
   
7. Click the New button to activate VBA.
   
   The following shows how to assign a macro for a Rectangle shape.
   
   
   
   
8. 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

9. Press Alt+F11 to exit from VBA.

Output

1. Press the View button
   
   The following figure shows the result of payment details.
   
   
   
   
   
   
2. 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.)