Loan Functions

Files Needed: Tutorial 08-Coach Cafe Business Plan.xlsx

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.

1. Start Excel and open Tutorial 9_Coach CafeLoan Analysis.xlsx.
2. Save the spreadsheet as Tutorial 9_Last Name_Coach CafeLoan Analysis.xlsx. (Replace “Last Name” with your last name.)
3. 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.
4. Based on the data you entered in step 3, make the following calculations:
a. In cell J7, use the PMT function to calculate Kevin’s monthly payments on the $50,000 loan.
b. 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.
c. In cell D8, calculate the total number of years required to pay off the loan.
d. 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.
e. In cell I10,use the FVfunction to calculate the principal at the end of 5 years with monthly payments of $1,500.
5. Shade cells J7, E8, D8, H9, and I10 with the fill color of your choice.
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. In cell H8, use the PMT function to calculate the monthly payment, then change the loan (PV) to $60,000.
7. 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:
a. Use the PPMT function to calculate the Principal payment for each month
b. Use the IPMT function to calculate the Interest payment for each month.
c. Calculate the total payment for each month.
d. Reduce the principal owed for each month by the amount paid in the previous month.
e. Copy the formulas for all five years of the loan period.
f. 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).
8. 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. Calculate the remaining principal at the end of each of the five years (row 80).
d. 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.
9. 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.
10. Save the file and submit it to your instructor.

Some one please assist on those functions.

Yes it is. I was asking for help in the homework to prepare for an exam

You need to ask for what you are stuck on. It is not reasonable for someone to do all your homework, and you don't learn. If there is a specific bit you are struggling on, give us your attempt and someone will help you solve it. I am sure for example you can save the spreadsheet with your name in the title!