Assignment #2Loan AmortizationA company is comfortable paying $2760 at the end of every month for the next 25 years to repay a loan. Calculate the maximum loan they can afford (Present value)...

Excel


Assignment #2 Loan Amortization A company is comfortable paying $2760 at the end of every month for the next 25 years to repay a loan. Calculate the maximum loan they can afford (Present value) using the 6.45% annual rate that the bank provided. You need to use the loan information to generate an amortization table for the life of the loan. Hint: to calculate the periodic rate (monthly rate), you need to divide annual rate by 12. a. Display the Assignment #2 worksheet and enter the data from above into the appropriate boxes for the loan Input. (10 marks) b. Insert a formula in cell E3 to calculate the Periodic Rate for the loan. (5 marks) c. Insert a formula in cell E4 to calculate the # of Payments (number of payments) for the loan. (5 marks) d. Insert a formula in cell E2 to calculate the Loan Amount they can afford based on the information given above (Use PV to calculate the loan amount and enter the pmt as a negative value in the PV function). (5 marks) e. Add the reference to the initial Beginning Balance in cell B8 and then using the fill handle complete the references for the rest of the cells in column “B”. (It must be equal to the ending balance of its immediate last period) (5 marks) f. Add the reference to the Monthly Payment in cell C8. It should be an absolute cell reference. (5 marks) g. Insert a formula in cell D8 to calculate the Interest Paid. (Amount of interest = monthly rate * Beginning balance) (5 marks) h. Insert a formula in cell E8 to calculate the Principal Payment. (Monthly payment – interest amount) (5 marks) i. Insert a formula in cell F8 to calculate the Ending Balance. (10 marks) j. Insert a formula in cell B9 to populate the new Beginning Balance. (10 marks) k. Use the fill handle (auto fill) to copy the formulas to the remainder of the table. Note that the table has only 60 pre-defined rows, but you need to keep going until your ending balance becomes zero. Total number of rows in the amortization table should be equal to the value in cell E4 (10 marks) Workbook Completion l. Complete the workbook by adding your name into the footer. (10 marks) m. Using freeze pain tool in excel, freeze the headers of the amortization table. (10 marks) n. Set the print view to the amortization table only. (5 marks) o. Save and close the file. Based on your instructor’s directions submit Assignment #2_FirstLast. (-10 marks if not applied)