Loan Analysis (the question is the first part, then the data is the tables)

Files Needed: File 09-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 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.

Start Excel and open File 9_Coach CafeLoan Analysis.xlsx.

Save the spreadsheet as File 9_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.

Based on the data you entered in step 3, make the following calculations:

In cell J7, use the PMT function to calculate Kevins 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.

	Coach Caf
	Loan Analysis
	Annual Interest Rate	5.35%
	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)		5						$951.60
	Payments (NPER)
	Business Loan (PV)
	Future Value (FV)

Coach Caf
Loan Summary
	Total Principal	Total Interest
Loan Schedule
Loan (PV)	Payments per Year	Years	Payments (NPER)	Annual Rate	Rate per Period (RATE)	Payment (PMT)
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