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
    1. Sub RoundedRectangle3_Click()  
    2. Dim i As Integer  
    3. Dim k As Integer  
    4. Dim j As Integer  
    5. Dim totalpayment As Integer  
    6. Dim setformula(1 To 4) As Variant  
    7. Dim blnce As Variant  
    8. Dim blnce1 As Variant  
    9. Dim SourceRange As Range  
    10. Dim Fillrange As Range  
    11. Dim Source As Range  
    12. Dim Fill As Range  
    13. totalpayment = Sheet1.Range("D4").Value * Sheet1.Range("D5").Value  
    14. MsgBox totalpayment  
    15. k = 11  
    16. j = 1  
    17. For i = 1 To totalpayment  
    18. Sheet1.Cells(k, j).Value = i  
    19. k = k + 1  
    20. Next i  
    21. Sheets("sheet1").Activate  
    22. setformula(1) = "=PMT(Rate/Npayment,Npayment*Year,Amount,0)"  
    23. setformula(2) = "=PPMT(Rate/12,A11,Year*Npayment,Amount,0)"  
    24. setformula(3) = "=IPMT(Rate/12,A11,Year*Npayment,Amount,0)"  
    25. blnce = "=Amount+C11"  
    26. Sheet1.Range("E11").Formula = blnce  
    27. blnce1 = "=E11+C12"  
    28. Sheet1.Range("E12").Formula = blnce1  
    29. Sheet1.Range("B11:D11").Formula = setformula  
    30. Set SourceRange = Worksheets("sheet1").Range("B11:D11")  
    31. Set Fillrange = Worksheets("sheet1").Range("B11:D" & Range("A" & Rows.Count).End(xlUp).Row)  
    32. SourceRange.AutoFill Destination:=Fillrange  
    33. Set Source = Worksheets("sheet1").Range("E12")  
    34. Set Fill = Worksheets("sheet1").Range("E12:E" & Range("A" & Rows.Count).End(xlUp).Row)  
    35. Source.AutoFill Destination:=Fill  
    36. 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.
    1. Sub RoundedRectangle5_Click ()  
    2. Dim Fill as Range  
    3. Set Fill = Worksheets ("sheet1").Range ("A11: E" & Range ("A" & Rows. Count).End (xlUp).Row)  
    4. Fill.ClearContents  
    5. 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.)

Up Next
    Ebook Download
    View all
    Learn
    View all