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
eakfasts 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
ealizes that he needs additional financing. His parents have offered to loan him an additional $70,000 to be
epaid 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 bo
ow $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
) 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 bo
ow 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
ate,# 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
eferences 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.
) In cell J6, delete the cu
ent value and hand enter XXXXXXXXXXIn cells J7 and J8, delete the cu
ent
value and hand enter -1500.
c) In cell E6, delete the cu
ent 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 cu
ent 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.
) 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)
) 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.
) 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
espectively. 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 Ru
ic
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 co
ectly
Inco
ect 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
eport filed.
Submitting identical file from another semester -100pts and a formal
eport 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 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)
&D
&F Page &P &A
Amortization
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 XXXXXXXXXXPER) 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 13
2 14
2 15
2 16
2 17
2 18
2 19
2 20
2 21
2 22
2 23
2 24
3 25
3 26
3 27
3 28
3 29
3 30
3 31
3 32
3 33
3 34
3 35
3 36
4 37
4 38
4 39
4 40
4 41
4 42
4 43
4 44
4 45
4 46
4 47
4 48
5 49
5 50
5 51
5 52
5 53
5 54
5 55
5 56
5 57
5 58
5 59
5 60
Cumulative Interest and Principal Payments per Yea
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
&D
&F Page &P &A