Coach Café
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 $50,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.- Start Excel and open Tutorial 8_Coach CafeLoan Analysis.xlsx.
- Save the spreadsheet as Tutorial 8_Last Name_Coach CafeLoan Analysis.xlsx. (Replace “Last Name” with your last name.)
- In the Loan Analysis worksheet, in the range C7:J10, enter or calculate the loan analysis data you will use as the basis for additional calculations. Kevin wants to borrow $50,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%. Enter this value in cell C4. (See chart setup image)
- Based on the data you entered in step 3, make the following calculations:
- In cell J7, use the PMT function to calculate Kevin’s monthly payments on the $50,000 loan.
- In cell E8, delete the current value, then use the NPERfunction to calculate the number of monthly payment periods required to pay off the loan if the monthly payment entered in cell J8 is$1,200. Remember to enter $1,200 as a negative number in cell J8.
- In cell D8, calculate the total number of years required to pay off the loan.
- In cell H9, use the PV function to calculate the largest loan the café could repay in 5 years if the monthly payments were $1,500.
- In cell I10,use the FVfunction to calculate the principal at the end of 5 years with monthly payments of $1,500.
- Shade cells J7, E8, D8, H9, and I10 with the fill color of your choice.
- 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. In cell H8, use the PMT function to calculate the monthly payment, then change the loan (PV) to $60,000.
- 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:
- Use the PPMT function to calculate the Principal payment for each month
- Use the IPMT function to calculate the Interest payment for each month.
- Calculate the total payment for each month.
- Reduce the principal owed for each month by the amount paid in the previous month.
- Copy the formulas for all five years of the loan period.
- Apply shading of a different shade to the row containing the value of the last payment (the last value showing as a positive value in column D).
- Below the Amortization schedule, calculate the cumulative interest and principal payments in the appropriate cells as follows:
- 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.
- Use the CUMIPMT function to calculate the cumulative interest payments in each of the five years of the loan.
- Calculate the remaining principal at the end of each of the five years (row 80).
- At the top of the worksheet, in cells C5 and D5, calculate the total principal payments and interest payments. Show the results as positive values.
- Go to the Loan Analysis worksheet, change the Annual Interest Rate in cell C4 to 6.25%, then note the total interest paid in D5 of the Amortization worksheet.
Get Project Solution by contacting us
- via WhatsApp: +92-324-7042178- via email: codelogixstudio@gmail.com