Using specialized functions
You have a large worksheet that contains the details of dozens of loans. A payment rate for each loan must be calculated according to the terms that have been provided for each. You will use the PMT function and the Function Arguments dialog box to complete this task.
1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.
https://github.com/alcadelina/Excel-
2. First, click to select cell G3:
3. Next, click Formulas → Financial → PMT:
4. The Function Arguments dialog box appears. Within this dialog box you need to enter all of the arguments. As the interest rate is stored in cell E3, type “E3” into the Rate text box:
5. As these are annual interest rates and the payments will be monthly, you need to divide this value by 12. Type “/12” following the cell reference in the Rate text box:
6. The next argument is Nper, or the number of payment periods over the life of the loan. This information is contained in cell D3, but it is provided in years. Because you need to enter it as months, type “D3*12” into the Nper text box:
7. The next argument is Pv, or present value. This is the amount of money that is being borrowed. This information is contained in cell C3, so type “C3” into the Pv text box:
8. Leave the Fv (Future Value) argument field empty. This argument will default to 0, which is what we want. (This means there will be no part of the loan left outstanding at the end of the payments.) We will also let the Type field default to 0, meaning payments will be due at the end of the payment period. Click OK to create the function:
9. You now have a result in the cell G3. You will also see the PMT function in the Formula Bar:
10. Now it is time to enter this formula for the rest of the data rows. To do this, click cell G3 to make it active, and then drag the AutoFill handle in the lower right corner of the cell down to G94:
11. Release the mouse button. You will see that the loan payments for each entry have been calculated:
12. Save the current workbook as Activity 1-2 Complete and then close Microsoft 365 Excel to complete this exercise.
13. Now, you can check out an example of a completed document in the link below:
No comments:
Post a Comment