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 hes 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 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 Excel 2013 - Tutorial 9 Additional Project

?

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. (See chart setup image)

? Payments per Year - the number of payments he will be making over a year for all 4 cells

? Years - the number of years he plans to be paying on the loan for all 4 cells

? "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

? Annual Rate - the annual interest rate that will be applied to the loan for all 4 cells

? "Rate per Month" - the annual rate divided by the number of months in a year for all 4 cells

? "Business Loan (PV)" - the amount of money he plans to borrow for all 4 cells

? "Future Value (FV)" - Use the formula provided for all 4 cells. =FV(your monthly rate,# of payments, monthly payments)

? "Monthly Payments(PMT)" - Use the formula provided for all 4 cells =PMT(your monthly rate,# of payments, loan amount)

? 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:

a. In cell J7, use the PMT function to calculate Kevins monthly payments on the $50,000 loan.

b. In cell J8, enter -1200. In cells J9 and J10, enter -1500.

c. In cell E8, delete the current value, then use the NPER function to calculate the number of monthly payment periods required to pay off the loan if the monthly payment is the value entered in cell J8.

d. In cell D8, calculate the total number of years required to pay off the loan.

e. In cell H9, 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 J9.

f. In cells I7 - I10, use the FV function to calculate the principal at the end of 5 years.

5. Shade cells J7, E8, D8, H9, and I10 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. In cell H8, use the PMT function to calculate the monthly payment, and then change the loan (PV) to $60,000 and the interest rate to the Annual Interest Rate in cell C4.

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. (Make sure to use the monthly rate and the original loan amount as the PV)

b. Use the IPMT function to calculate the Interest payment for each month. (Make sure to use the monthly rate and the original loan amount as the PV)

c. Calculate the total payment for each month. (should be the same for all cells in the column)

d. Reduce the principal owed for each month by the amount of principle paid in the previous month.

e. Copy the formulas for all five years of the loan period. (Note that the total payment remains the same for the whole 5 years)

f. Apply shading of light orange 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). This is how much he still owes of his loan at the end of each year.

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%, and then note the total interest paid in D5 of the Amortization worksheet.

10. Save the file and submit it to your instructor.

	Coach Caf
	Loan Analysis Worksheet
	Annual Interest Rate
	Financial Value	Payments per Year	Years	Payments (NPER)	Annual Rate	Rate per Month	Business Loan (PV)	Future Value (FV)	Monthly Payments (PMT)
	Monthly Payment (PMT)
	Payments (NPER)
	Business Loan (PV)
	Future Value (FV)

	Coach Caf
	Amorization Worksheet
	Loan Summary
		Total Principal	Total Interest
	Loan Schedule
	Loan (PV)	Payments per Year	Years	Payments (NPER)	Annual Rate	Rate per Period (RATE)	Payment (PMT)
	$60,000	12	5	60	5.35%	0.44%	($1,139.99)
	Amortization Schedule
	Year	Period	Remaining Principal	Principal Payment	Interest Payment	Total Payment
	1	1
	1	2
	1	3
	1	4
	1	5
	1	6
	1	7
	1	8
	1	9
	1	10
	1	11
	1	12
	2	1
	2	2
	2	3
	2	4
	2	5
	2	6
	2	7
	2	8
	2	9
	2	10
	2	11
	2	12
	3	1
	3	2
	3	3
	3	4
	3	5
	3	6
	3	7
	3	8
	3	9
	3	10
	3	11
	3	12
	4	1
	4	2
	4	3
	4	4
	4	5
	4	6
	4	7
	4	8
	4	9
	4	10
	4	11
	4	12
	5	1
	5	2
	5	3
	5	4
	5	5
	5	6
	5	7
	5	8
	5	9
	5	10
	5	11
	5	12
	Cumulative Interest and Principal Payments per Year
		Year 1	Year 2	Year 3	Year 4	Year 5
	Months	1	13	25	37	49
		12	24	36	48	60
	Principal
	Interest
	Principal Remaining