Integrated Computer Application - Borrowing Costs

Objective:

To calculate and compare the monthly payments for two possible business loans using the PMT function in Excel. This assignment will help you become proficient in using Excel to enter data, format cells, and use financial functions.

Problem:

Nike needs to pay an invoice to a supplier but currently has no cash. To fulfill this obligation, Nike must borrow money. Your task is to calculate the monthly payment for two possible business loans and provide a recommendation to Nike management.

Excel provides hundreds of functions of which many are financial functions related to loans and investments. The PMT function is used to determine the payment amount to repay a loan. The IPMT function computes the interest portion of each payment. The PPMT function supplies the amount of each payment that is applied to the principle of the loan. The INTRATE function calculates the interest rate necessary to grow an initial deposit to a selected amount. The NPER function counts the number of payments that are needed to repay a loan.

Instructions:

Use the PMT function to determine the monthly payment for two possible business loans. To successfully use the PMT function, you should know about the cost of borrowing money. This cost is based upon the amount of money (called the principal), the interest rate (usually described as the annual percentage rate), and the time it will take to repay all of the money (number of payments). The three loan values are all that is needed to identify the payment amount using the PMT function, but there is one very important detail that you always need to consider. You must be consistent about the units that you use for the rate and the number of payments. Typically the interest rate unit is converted to match the unit for the number of payments. The PMT function usage defined by Excel: =PMT(rate, nper, pv) where rate is the interest rate, nper is the number of payments, and pv is the loan amount. Provide a recommendation to the Nike management as part of the report.

Here's a glimpse of what the finished worksheet should resemble after completion.

1. Set Up the Worksheet:

   • Open Microsoft Excel and create a new workbook.
   • Save the workbook as "Nike_Loan_Payment_Calculation_LastName_FirstName.xlsx".

2. Data and Format:

   • Adjust: row height and column width, create column labels for headings, use PMT function, merge & center, font color and size, cell borders, fill color, formatting styles

   • Enter the worksheet title, Nike, in cell A1 and the worksheet subtitle, Borrowing Costs, in A2. 

   • Enter the heading Business Loan Request A in B4 and Business Loan Request B in D4.

   • Format the title with the title style. The subtitle should be dark blue, 14 point, italic font and the two column headings should be blue, 11 point font size. Merge and center the title and subtitle across the four columns of the report.

   Loan Information:

   • Beginning in row 5, enter labels and format the cells as shown in Figure 1. 

   • The ranges A5:B10, A12:B14, D5:D10, and D12:D14 have borders, column A is filled yellow, and columns B and D filled light blue. 

   • Auto-fit the width of column A and set the width of column B and D to 23.

   • Format B5 and D5 using the comma style.

   • Format cells B6, B8, D6, and D8 with the percent style, and with four decimal places.

   • Enter the 800,000 as the loan amount in both scenarios. 

   • Enter 6.9% as the annual interest rate for loan A and 8.6% for loan B. To indicate that the loan will be paid back with monthly payments, enter 12 in cells B7 and D7.

   • Convert the annual interest rate to a monthly rate so the number of payments and the interest rate are in the same unit of measure. Enter a formula in cells B8 and D8 that divides the annual interest rate by the payments per year for each scenario.

   • Enter 7 as the number of years for the loan in cell B9 and 5 years in Loan Request B. 

   • The total payments in B10 can be calculated by entering a formula that multiplies the payments per year by the number of years.

   • Also, create the correct formula to calculate the total payments for Loan B.

   • To use the PMT function to calculate the monthly payments amount in cell B12. Reference the appropriate cells in column B for the rate, number of payments, and the loan amount.

   • Enter a formula in B13 to multiply the monthly payments cell by the payments per year cell. 

   • Enter a formula in B14 to multiple the monthly payments cell by the total payments cell. 

   • Next, calculate the monthly payments, total annual cost, and total loan payments for Loan B. 

   • Monthly payments, total annual cost, and total loan payments will be negative numbers.

   Recommendation:

   • Enter the text,” Which loan would you recommend and why?” in cell A17.

   • Use the word wrap and change the height of the row to 30. Fill the cell yellow.

   • Merge the cells in the range A18:D23 and provide a recommendation and reason for the loan you suggest Nike obtain. Use the paragraph alignment to display all of the text in your recommendation.

   • Enter a comment in either cell B4 or D4 with the text “My Recommendation”, depending upon the scenario you recommended in A18.

• By following these steps, you'll create a clear and professional worksheet that effectively communicates your recommendation based on calculated data.