Need excel spreadsheet filled out per pdf instructions
Loan Analysis Files Needed: Tutorial 8_LoanAnalysis_LastName.xlsx and Tutorial_8_Loan Analysis_Instructions.pdf. Replace LastName with your last name for the excel file as usual. The Coach Café is a small neighborhood café that serves vegetarian breakfasts and lunches. The owner of the café, Keith Watson, started the café with $50,000 of his own money. Now that he’s up and running, he realizes that he needs additional financing. His parents have offered to loan him an additional $70,000 to be repaid within five years. Keith needs to analyze the loan payments and terms to ensure he can make the payments required. He has started a workbook and asks you to complete a Loan Analysis and Amortization Schedule with data related to the loan. 1. Start Excel and open Tutorial 8_Coach CafeLoan Analysis.xlsx. 2. Save the spreadsheet as Tutorial 8_Last Name_Coach CafeLoan Analysis.xlsx. (Replace “Last Name” with your last name.) Figure 1: Initial Table Setup for Instruction #3 3. In the Loan Analysis worksheet, in the range C5:J8, enter or calculate the loan analysis data you will use as the basis for additional calculations. Kevin wants to borrow $70,000 over a period of 5 years and make 12 payments each year. His parents have asked for an annual interest rate of 5.35%. (See chart setup image) (Make sure to use cell references instead of hand entering formula parameters, unless specifically told to. If you don’t, the cells will not update when modified.) a) Payments per Year - the number of payments he will be making over a year for all 4 cells b) Years - the number of years he plans to be paying on the loan for all 4 cells c) "Payments (NPER)" - the total number of payments he will make over the life of the loan (ie the number of years) for all 4 cells d) Annual Rate - the annual interest rate that will be applied to the loan for all 4 cells e) "Rate per Month" - the annual rate divided by the number of months in a year for all 4 cells f) "Business Loan (PV)" - the amount of money he plans to borrow for all 4 cells g) "Future Value (FV)" - Use the formula provided for all 4 cells. =FV(your monthly rate,# of payments, monthly payments) h) "Monthly Payments (PMT)" - Use the formula provided for all 4 cells =PMT(your monthly rate,# of payments, loan amount) i) Make sure to fill in all 4 rows in each column! 4. Based on the data you entered in step 3, make the following calculations: (Make sure to use cell references instead of hand entering formula parameters, unless specifically told to. If you don’t, the cells will not update when modified.) a) In cell J5, use the PMT function to calculate Kevin’s monthly payments on the $70,000 loan. b) In cell J6, delete the current value and hand enter -1200. In cells J7 and J8, delete the current value and hand enter -1500. c) In cell E6, delete the current value, then use the NPER(rate, pmt, pv) function to calculate the number of monthly payment periods required to pay off the loan if the monthly payment (pmt) is the value entered in cell J6. Use the rate and business loan values for that row. Rate is the "Rate Per", pmt is the "Monthly Payment", and pv is the "Business Loan". d) In cell F8, delete the current value and hand enter 6.25 instead. e) In cell D6, calculate the total number of years required to pay off the loan. f) In cell H7, use the PV function to calculate the largest loan the café could repay in 5 years if the monthly payments were the value in cell J7. g) In cells I5 – I8, use the FV function to calculate the principal at the end of 5 years. 5. Shade cells J5, E6, D6, F8, H7, and I8 with the fill color of light blue. 6. In the Amortization worksheet, reference the data from the appropriate cells in row 7 of the Loan Analysis worksheet to enter the data required for cells B8 to G8. 7. In cell H8, use the PMT function to calculate the monthly payment, and then change the loan (PV) to $75,000 and the interest rate to 6.25%. (Make sure to use the rate per period and also the original loan amount as the PV) (Make sure to use cell references instead of hand entering formula parameters, unless specifically told to.) a) Loan (PV) - enter the amount of the loan specified above. b) Payments per Year - enter the number of payments per year. c) Years – enter the number of years it takes to pay off the loan. d) Payments (NPER) – total number of payments over the life of the loan. e) Annual Rate – the annual rate specified above. f) Rate per Period (RATE) – the monthly rate using the annual rate as a reference g) Payment (PMT) – Use the pmt function as specified above. 8. Complete the Amortization schedule using the cell addresses from row 8. Use absolute references where needed. In cell D12, enter the loan amount as the Remaining Principal, then complete the schedule as follows: (Make sure to use cell references instead of hand entering formula parameters, unless specifically told to.) a) Put the PV value in cell D12. (make sure to use a cell reference for the PV) b) Use the PPMT function to calculate the Principal payment for each month. (Make sure to use the rate per period and also the original loan amount as the PV) c) Use the IPMT function to calculate the Interest payment for each month. (Make sure to use the rate per period and also the original loan amount as the PV) d) Calculate the total payment for each month. (should be the same for all cells in the column) e) Reduce the principal owed for each month by the amount of principle paid in the previous month. f) Copy the formulas for all five years of the loan period. (Note that the total payment remains the same for the whole 5 years) g) Apply shading of light orange to the cell containing the value of the last payment (the last value showing as a positive value in column D). 9. Below the Amortization schedule, calculate the cumulative interest and principal payments in the appropriate cells as follows: a) Use the CUMPRINC function to calculate the cumulative principal payments in each of the five years of the loan. Include absolute references to loan conditions as needed. b) Use the CUMIPMT function to calculate the cumulative interest payments in each of the five years of the loan. c) In cell H77, enter “Total” in bold. d) In cells H78 and H79 calculate the SUM of the cumulative interest and cumulative principle respectively. Multiply the results by -1 to show the results as positive values. Format with “all table” table boarders and color cell H77 the same color as the month cells next to it. e) Calculate the remaining principal at the end of each of the five years (row 80). This is how much he still owes of his loan at the end of each year. f) At the top of the worksheet, in cells C5 and D5, calculate the total principal payments and interest payments. (see cells H78 and H79) Show the results as positive values. 10. Change the Annual Interest Rate in cell F8 to 6.25%, and then note the changes in the table, totals, and formulas. 11. Save the file and submit it to your instructor Grading Rubric Grade Item: Points Loan Analysis Tab 40 pts Amortization Tab 60 pts total Loan Schedule 15 pts Schedule table 15 pts Cumulative table 20 pts Summary 5pts File named correctly Incorrect examples including but not limited to: Tutorial 8_LoanAnalysis_LastName (1) or Tutorial 8_LoanAnalysis_LastName.xlsx.xlsx 5 pts Common Additional Deductions: Submitting identical files as someone else in the class(s) -100pts and a formal report filed. Submitting identical file from another semester -100pts and a formal report filed. Taking liberties and making changes you want to make and not ones that are in the instructions. Ie formatting etc. -10~100pts depending upon infraction Using your own excel document instead of the one provided -100pts Loan Analysis Coach Café Loan Analysis Financial ValuePayments per YearYearsPayments (NPER)Annual RateRate per MonthBusiness Loan (PV)Future Value (FV)Monthly Payments (PMT) Monthly Payment (PMT) Payments (NPER) Business Loan (PV) Future Value (FV) &D &FPage &P&A Amortization Coach Café Loan Summary Total PrincipalTotal Interest Loan Schedule Loan (PV)Payments per YearYearsPayments (NPER)Annual RateRate per Period (RATE)Payment (PMT) Amortization Schedule YearPeriod (PER)Remaining PrincipalPrincipal PaymentInterest PaymentTotal Payment 11 12 13 14 15 16 17 18 19 110 111 112 213 214 215 216 217 218 219 220 221 222 223 224 325 326 327 328 329 330 331 332 333 334 335 336 437 438 439 440 441 442 443 444 445 446 447 448 549 550 551 552 553 554 555 556 557 558 559 560 Cumulative Interest and Principal Payments per Year Year 1Year 2Year 3Year 4Year 5 Months113253749 1224364860 Principal Interest Principal Remaining &D &FPage &P&A