Need ASAP

1 Start Excel. Download and open the file named Excel_Ch11_Prepare_Financial_Analysis_PartA.xlsx. Grader has automatically added your last name to the start of the filename. Save the file to the location where you are storing your files. N 2 The type of loan that the Turquoise Oasis Spa is considering is an amortized loan. An amortized loan is a loan with scheduled periodic payments consisting of both principal and interest. This is different from other types of loans that have interest-only payment features and balloon payments. On the LoanAnalysis1-3 worksheet, in cell B11, calculate the monthly payment from the bank's perspective for Loan Option 1 that will be due at the beginning of the period. 3 The RATE function calculates the interest rate per period for an investment or loan, given that you know the present value of the loan, payment amount, and number of payment periods. In cell E11, calculate the interest rate for Loan Option 2 from the bank's perspective in which the payment is due at the beginning of the period. 4 The number of periods function, or NPER function, calculates the number of payment periods for an investment or loan if you know the loan amount, interest rate, and payment amount. In cell B18, calculate the number of periods on Loan Option 3. 5 With the CUMIPMT function, Excel does not allow you to place a negative sign in front of the Ry, argument. Thus, to display numbers from the bank's perspective, as a positive number, you can simply place a negative sign before the function name. On the LoanAnalysis4 worksheet, in cell B13 calculate the total cumulative interest paid during a three-year loan from the bank's perspective. 6 In cell B17, calculate the total cumulative interest paid per year for the loan from the bank's perspective. Copy the formula down to cell B28. 7 In cell E13, calculate the total cumulative principal paid per year for the loan from the bank's perspective. Copy the formula down to cell E28. 8 In cell E17, calculate the total cumulative principal paid per year for the loan from the bank's perspective. Copy the formula down to cell E28. 9 When you create an amortization table, start with the simplest configuration. Initially, you should calculate the payment number, the payment amount, the interest and principal portions, and an ending balance for each payment. On the Amortize worksheet, in cell G6 calculate the payment from the bank's perspective using the given loan amount, term, and interest rate. 10 In cell G7, enter a formula that calculates the total amount that will be paid on the loan. 11 In cell G8, enter a formula that will calculate the total interest that will be paid on the loan. 12 In cell C12, reference cell C5 to reference the beginning balance of the loan and begin creating your amortization schedule. 13 In cell D12, use an absolute cell reference to reference cell G6 to reference the payment. Copy the formula down to cell D23. 14 In cell E12, calculate the monthly interest for the loan from the bank's perspective. Copy the formula down to cell E23. 15 In cell F12, calculate the monthly principle for the loan from the bank's perspective. Copy the formula down to cell F23. 16 In cell G12, enter a formula that will calculate the remaining balance after each payment by subtracting the principal payment from the beginning balance. Copy the formula down to cell G23. 17 18 In cell C13, enter a formula that will calculate the beginning balance after each payment by subtracting the principal payment from the beginning balance. Copy the formula down to cell C23 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Please note: This project continues in part B, which can be found in the Product Shared Library Formatting A1 G H 1 fx Turquoise Oasis Spa B C Turquoise Oasis Spa Expansion Loon Analysis First New Mexico Savings and Loan 2 Loan Option 2 $250,000 Loan Amount 6.25% Monthly Payment 10 Termoflen years) 12 Payments per year $2.807.00 Interest Rate end of period) Interest Rate (beginning of period) $250,000 53,243.00 8 12 5.651% Loan Option 1 6 Loan Amount 7 Interest Rate & Term of loan years) 9 Payments per year 10 Monthly Payment end of period) 11 Monthly Payment (beginning of period) 12 13 Loan Option 3 14 Loan amount 15 Interest Rate 16 Payments per a 17 Monthly Payment lend of period) 16 Total Number of Payments and of period) 19 20 21 22 21 $250,000 5.505 12 $3,663 20 25 20 27 28 29 30 31 22 33 34 35 36 37 30 40 41 42 Documentation Loan Analysis 1-3 A1 fx Turquoise Oasis Spa Conditional Formatting B D Turquoise Oasis Spa Expansion Loan Analysis First New Mexico Savings and Loan 8.65% Total Cumulative Principal Payments End of Period Payments Loan Option 4 6 Loan Amount $250,000 7 Interest Rate 8 Term of Loan Iyears) 3 9 Payments per Year 4 10 Quarterly Payment $18,984.59 11 12 Total Cumulative Interest Payments 13 End of Period Payments 14 15 Periodic Cumulative Interest Payments 16 Quarter Interest 17 1 16 2 19 3 20 4 21 5 22 6 23 7 24 25 5 26 10 27 28 12 29 10 31 32 35 34 35 36 37 31 39 40 41 42 Periodic Cumulative Principal Payments Ouarter Principal 1 2 1 4 5 6 7 9 10 11 12 DODUIN Lan Loan Analysis A1 Merge & Center fx Turquoise Oasis Spa $ -% % de Conditional Format Formatting as Table 1 D E Turquoise Oasis Spa Basic Loan Amortization K M 2 3 4 Input Variables 5 Amount of loan 6 interest Rate 7 Term of Loan (years) 8 Payments per Year 9 Starting Date of Loan 10 $30,000 6.45% 1 12 2/1/19 Calculated Values Number of Payments Monthly Payment Total of Payments Total interest Paid 12 Period Beginning Balance Payment Interest Principal Remaining Balance 11 12 13 14 15 2 3 4 5 16 17 Date 3/12 4/1/21 5/1/21 6/1/21 7/1/21 8/1/21 9/1/21 10/1/21 11/1/21 12/1/21 1/1/22 2/1/22 7 8 9 10 12 $0.00 $0.00 $0.00 18 19 20 21 22 23 24 Totals 25 26 27 28 29 30 31 32 13 36 35 36 37 38 39 40 41 Lonalitas Amortize Documentation Ready