In the following case study you will complete an amortization table.
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Open Ch7CaseStudy.xlsx file and save as LastNameFirstNameCh7CaseStudy.xlsx.
2 In cell E3, enter a formula to calculate the Number of Periods referencing the applicable cells in the Input Area. 2
3 In cell E4, enter a formula to calculate the monthly interest rate referencing the applicable cells in the Input Area. Format the result as Percent and 2 decimal places.
4 In cell E5, use a function to calculate the monthly payment referencing the applicable cells in the Summary Calculations area. Show the result as a positive number. 3
5 In the Payment # column, enter in values for 5 payments. Note: Be sure to complete the column only through row 15. 1
6 In cell B11 enter the correct reference to the beginning balance. 1
7 In the Interest Paid and Principal Payment columns, use financial functions to calculate the Interest Paid and Principal Payment for the first payment. Show all results as positive numbers. 10
8 In cell E11, enter a formula that will calculate the Ending Balance for the first payment.
9 In cell B12 enter the correct cell reference that retrieves the Ending Balance (after payment 1). 1
10 Complete the rest of the table by copying down the formulas. (Hint: be careful when copying the formula down the Beginning Balance column that you choose the correct cell to copy down. Depending on the column you copy down first, you may see zeros as results until the other columns are copied.)
11 In cell E6, enter a function that will calculate the cumulative interest paid after five payments. Reference the loan information in E2:E4 and the corresponding Payment number in the table for the start and end period arguments. Leave the result as a negative number. 5
12 Format all monetary values in the table and the summary calculations area as Accounting format to 2 decimal places.
13 Save and close the file. Upload the closed file to the XLCh7 Case Study link in Myitlab.
Total Points 32
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!