You have been asked to design a program using Excel and VBA to calculate the monthly payments for an Automobile Loan. The program should ask the user for the Loan amount, the Interest Rate and the Term of the loan. The program should then calculate the monthly payment and provide a message box displaying the Payment Amount.
Start Excel. If the Developer tab does not display on the Ribbon, click File, then click the Excel Options button, then, Customize Ribbon and Check the Developer Option check box.
To enable macros, click the Macro Security button on the Developer tab on the Ribbon. When the Trust Center dialog box is displayed, click ‘Enable all macros’ and then click the OK button.
Using the table below as a reference design the following Worksheet:
Enter the following formulas in the cells as follows:
Use the Create from Selection option to name all of the cells of data in Column B
In Cell B7 calculate the Rate per period using the names previously created to divide the Interest Rate by 12
In Cell B8 calculate the Number of Periods using the names previously created to multiply the Term times 12
In cell B9 use the PMT function and the names previously created to calculate the monthly
payment (hint use the Number of periods and the Rate per Period in your arguments)