Task 6 The following are instructions for creating loan payment models in Excel This section details how to construct loan payment models. 1. Open the assignment Excel workbook to the worksheet

"Task 6_Financial Functions" 2. Read the "Instructions." The instructions provide you an overview of the components required in the task. 3. Review the Problem Statement and Table provided in the "Variables" section. 4. Complete the following steps Model formulation steps in the "Model" section. Model Formulation Steps 5. Convert loan structure a. Calculate the loan structure for Quarterly and Monthly b. Loan principle does not change. c. Interest rate is divided by the number of payment periods (e.g., Quarterly is 4 payments per year). d. The number of payments is multiplied by the new period (e.g., Monthly is 12 payments per year). 6. Excel function elements a. Rate = interest rate per period. b. Per = specifies the period and must be in the range 1 to nper. c. Nper = the total number of payment periods in an annuity. d. Pmt = the amount of the payment for the period. Note: payments are entered as negative values. e. Pv = the present value, the total amount that a series of future payments is worth now. f. Fv = the future value, or a cash balance you want to attain after the last payment is made. If omitted, fv is assumed to be 0. g. Type = indicates when payments are due. The default is at the end of the period. The default is acceptable for these tasks. 7. Monthly payment a. Using the monthly loan structure, calculate the monthly payment. b. Excel function: =PMT(rate, nper, pv, [fv], [type]) 8. Principal payment a. Using the monthly loan structure, calculate the monthly principal amount for payment #5. b. Excel function: =PPMT(rate, per, nper, pv, [fv], [type]) 9. Interest payment a. Using the monthly loan structure, calculate the monthly interest amount for payment #5. b. Excel function: IPMT(rate, per, nper, pv, [fv], [type]) 10. Interest rate for a payment a. Using the quarterly loan structure, calculate the interest rate for a payment of $2600. b. Excel function: =RATE (nper, pmt, pv, [fv], [type]) 11. Number of required payments a. Using the quarterly loan structure, calculate the number of payment periods for a payment of $3000. b. Excel function: NPER(rate, pmt, pv, [fv], [type]) Page 11 | 14/n12. Future value of payments a. b. 13. Lender perspective a. Using the monthly loan structure, calculate the future value for a monthly payment of $700 and $1100 Excel function: =FV(rate, nper, pmt, [pv], [type]) 13.d.iii From perspective of the lender and using the quarterly loan structure, calculate the NPV and IRR for each of the payment amounts. b. Excel function: =NPV(rate, cash flows) c. Excel function: =IRR(cash flows to include initial investment) d. NPV and IRR Table i. Initial investment is the amount of the loan 1.50% Quarters Payment $2500 Payment $2750 Payment $3000 Payment $3500 1. Reminder, the initial investment is negative. ii. The amount of the cash flows is specified by the payment amount. iii. The NPV is determined by the Excel Formula: =Initial investment + NPV(Interest rate, cash flows) 1. Cell reference the interest rate. NPV and IRR Table Format Interest Rate (Discount Rate) iv. The IRR is determined by the Excel Formula: =IRR(cash flows) 1. Reminder, the IRR cash flows include the initial investment. "Money 1 Invest Period Investment Cash Flow 0 1 (20,000) $2,500 $ $ 14. Task 6 is complete. 13.d.i 2 2,500 $ "What I Get In Return For Investment" CF 4 CF 3 2,500 $ 13.d.ii 2,500 $ CF "Profit" CF 5 NPV 7 8 2,500 $2,500 $2,500 $2,500 $ (1,285) "Was it Profitable? 13.d.iii IRR 0.00% 13.d.iv