Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_CH11_GRADER_PS1_HW - Loan Analysis 1.4 Project Description: Janette Franklin, owner of Frank Solutions, LLC, is considering borrowing $30,000 to finance the renovation of her office building. She has been given three different loan options with various terms and interest rates. She knows that she can only afford to make payments of $530.00 a month and needs your help to determine which loan option is best. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the file named e06ch11_grader_h1_Loans.xlsx. Save the file with the name e06ch11_grader_h1_Loans_LastFirst, replacing LastFirst with your name. 0.000 2 On the LoanAnalysis worksheet in cell C4, calculate the annual percentage rate for Loan Option 1. Format the cell as Percentage with 2 decimal places. 10.000 3 In cell C6, calculate the total cumulative interest that would be paid throughout the life of the loan if payments were made at the end of the periods. Reference cell C5 for the end_period argument. Be sure that the result of the function is positive, and then format the cell as Currency. 10.000 4 In cell C7, calculate the total cost of the loan by adding the loan amount in cell C2 to the cumulative interest amount in cell C6. 4.000 5 In cell F3, calculate the end of the period monthly payment for Loan Option 2. Be sure the result of the function is positive, and then format the cell as Currency. 10.000 6 In cell F6, calculate the total cumulative interest that would be paid throughout the life of the loan if payments were made at the end of the periods. Reference cell F5 for the end_period argument. Be sure that the result of the function is positive, and then format the cell as Currency. 10.000 7 In cell F7, calculate the total cost of the loan by adding the loan amount in cell F2 to the cumulative interest amount in cell F6. 4.000 8 In cell I5, calculate the total number of quarterly payments required to pay off Loan Option 3 if payments are made at the end of the period. Format the cell as Number with 0 decimal places. 10.000 9 In cell I6, calculate the total cumulative interest that would be paid throughout the life of the loan if payments were made at the end of the periods. Reference cell I5 for the end_period argument. Be sure that the result of the function is positive, and then format the cell as Currency. 10.000 In cell I7, calculate the total cost of the loan by adding the loan amount in cell I2 to the cumulative interest amount in cell I6. 4.000 10 Updated: 10/05/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Excel Project Step Instructions Points Possible 11 You will create an amortization schedule for Loan Option 3 in cells C12:F35. All payments are end-of-the-period payments. Be sure that all formulas and functions result in a positive value. To begin, in cell C12, enter a formula that refers to the Loan Amount for Loan Option 3. Format the cell as Currency with 0 decimal places. 4.000 12 In cell D12, enter a function to calculate the first principal payment. Format the cell as Currency with 2 decimal places. 5.000 13 In cell E12, enter a function to calculate the first interest payment. Format the cell as Currency with 2 decimal places. 5.000 14 In cell F12, enter a formula to calculate the first ending balance. Format the cell as Currency with 2 decimal places. 4.000 15 In cell C13, enter a formula that refers to the ending balance in cell F12. Complete the amortization schedule as directed down to F35. Ensure that cells C13:F35 are formatted as Currency with 2 decimal places. 10.000 16 Ensure that the worksheets are correctly named and placed in the following order in the workbook: LoanAnalysis, Documentation. Save the workbook, exit Excel, and then submit the file as directed by your instructor. 0.000 Total Points Updated: 10/05/2017 2 100.000 Current_Instruction.docx Loan Option 1 Loan Amount Monthly Payment Annual Rate (APR) Loan Option 2 Loan Amount Monthly Payment Annual Rate (APR) $30,000 $460 Total Payments (NPER) Total Interest Total Cost of Loan Total Payments (NPER) Total Interest Total Cost of Loan 84 Amortization Schedule Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Beginning Balance 20171028165133pack_e06ch11_grader_h1_loans.xlsx Principal Payment Interest Payment Loan Option 2 $30,000 8.00% 60 Loan Option 3 $30,000 Loan Amount Quarterly Payment $1,588.38 8.05% Annual Rate (APR) Total Payments (NPER) Total Interest Total Cost of Loan Ending Balance 20171028165133pack_e06ch11_grader_h1_loans.xlsx Create Date By Whom mm/dd/yyyy Firstname Lastname Mod. Date By Whom Description Create Date Creator Sheet Name Mod. Description Workbook Name e06ch11Loans.xlsx Last Version Backup Name Purpose 
Purchase answer to see full attachment