compltete assignments instructions on the list of uploads
Grader - Instructions Excel 2019 Project
Exp19_Excel_Ch07_ML2_Finances Project Description:Your family is considering purchasing a house and investing in a business venture. You started the structure for a loan amortization table and the investment table. You will complete the first five years of the 20-year loan amortization table. To complete the table, you will enter formulas to calculate the beginning balance, monthly payment, and ending balance. You will use financial functions to calculate the interest and principal paid for each monthly payment. In addition, you want to calculate cumulative interest after the first year, total interest over the life of the loan, and the amount of principal paid after the first year. You also want to see how many months half or more of the payment is for interest. You will then focus your attention on completing an investment table using date functions, formulas, and a financial function to calculate the future value of the investment.
Steps to Perform: Step | Instructions | Points Possible |
Start Excel. Download and open the file named Exp19_Excel_Ch07_ML2_Finances.xlsx. Grader has automatically added your last name to the beginning of the filename. | ||
The first step is to enter a formula to reference the loan amount for the beginning balance for the first payment. | ||
A loan amortization table usually contains a column that displays the monthly payment for each row. | ||
The monthly payment indicates the total amount of the payment, which includes principal and interest. Interest is calculated based on the loan amount, the rate, the payment number, and the number of payments. | 7.5 | |
After calculating the interest paid, the rest of the monthly payment repays the principal. | 7.5 | |
The last column of the loan amortization table calculates the ending balance. | ||
The beginning balance for each payment is calculated and entered in column B. | ||
Ensure that Accounting Number Format is applied to the range B9:F68. | ||
You want to calculate the total interest paid for the first year. | ||
10 | Now you want to calculate the total interest paid for the entire loan. | |
11 | You want to calculate the cumulative principal paid for the first year. | |
12 | In cell F5, insert the COUNTIF function to count the number of payment periods in which the interest in the loan amortization table is higher than one-half of the monthly payment (cell D4). | |
13 | Apply General number format to cell F5. | |
14 | You want to extract the year and use it to determine the payoff year. | |
15 | You need to format the result in cell D4 as a number. | |
16 | At the end of each period, you will add $125 to the investment. | |
17 | You want to calculate the interest earned per period. | |
18 | You are ready to calculate the ending balance for each payment period. | |
19 | You will use a nested function to calculate the dates in column A. | |
20 | Copy the function from cell A8 to the range A9:A54 but preserve the fill formatting. | |
21 | In cell E56, insert the FV function to calculate the future value of the investment use references to the respective cells in the input area for the arguments. Make sure the result is positive. Leave the Type argument empty. | |
22 | Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of both sheets. | |
23 | Save and close Exp19_Excel_Ch07_ML2_Finances.xlsx. Exit Excel. Submit the file as directed. | |
Total Points | 100 |
Created On: 09/24/2019 4 Exp19_Excel_Ch07_ML2 - Finances 1.0