Last week we showed you how you can keep your company in good standing by honouring your loan installments, and we showed you how to work out your loan installment using an interactive financial program. In relation, it’s important to know the periodic payment, principal amount and interest rate that you’ll be paying, which you can determine by generating an amortization table using a custom program.
In our example we’ve used the PPMT function in the custom program to generate the amortization table. The PPMT function returns the periodic payment on the principal for an investment. This is based on constant payments and a constant interest rate.
Note: Download the workbook to practice this exercise
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 _Table for the name.
5. Select OK.
6. Open the amortization table document.
7. Copy the code and paste into the VBA window in Excel.
a. Paste the code in the area between, Public Sub Loan_Table and End Sub.
- Click the Save button.
- Select the File menu.
- Select Close and Return to Microsoft Excel.
- Select the View tab.
- Select Macros then View Macros.
13. Select the Options button.
14. Enter the letter A (capital) for a shortcut key.
15. Enter the author’s name, program title and the date under description.
16. Select OK.
17. Close the window.
18. To run the macro; press CTRL & SHIFT & A.
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. Select Yes to display an amortization table, otherwise select NO.
26. Select OK.
