If your company has a loan, to keep it in good standing, you need to ensure that loan installments are honored. Knowing how much the installment is, and how often it has to be paid, will help you manage your finances better. Last year we shared a tip on how to use the PMT Function to calculate the monthly repayment on a loan, this time around we want to show you how to calculate a loan installment using an interactive financial program.
Note: Download the workbook to practice this exercise
Applies To: Microsoft® Excel® 2010 and 2013
1. Open the practice workbook
2. Press ALT & F11, this opens the Visual Basic for Applications(VBA) window.
3. Select the Insert menu, then Procedure.
4. Enter Loan_Calculation for the name.
5. Select OK.
6. Open the loan_calculation document
7. Copy the code and paste into the VBA window in Microsoft® Excel® .
a. Paste the code in the area between, Public Sub Loan_Calculation and End Sub.
8. Click the Save
9. Select the File
10. Select Close and Return to Microsoft Excel.
11. Select the View
12. Select Macros then View Macros.
13. Select the Options
14. Enter the letter H (capital) for the shortcut.
15. Enter the author’s name, purpose of the program and the date under description.
16. Select OK.
17. Close the window.
18. To run the macro; press CTRL & SHIFT & H.
19. Enter the amount you want to borrow, e.g 50000.
20. Select OK.
21. Enter the annual percentage rate for the loan, e.g. 15.
22. Select OK.
23. Specify the number of monthly installments for the loan repayment e.g. 48.
24. Select Yes if you make payment at the end of the month, otherwise select No.
25. The monthly installment amount will be displayed.
26. Select OK.
The monthly loan installment has been calculated using an interactive program that uses the PMT function.
