1. Need excel formulas to B10 and B11. Answers given in attachment.

Explanation of answers: The monthly payment is $1,663.79. At the end of year 10, the company still owes $171,876 out of the 300K it borrowed, and will make this large payment to pay the loan in full (in addition to the monthly payment). The present value of all the payments, including the monthly payments and the pay-off payment, is $261,220.81. It is lower than the amount SoftLuxe borrowed due to the fact that the discounting rate used (5%) is larger than the rate charged for the loan (3%). The company assumes it can invest money and earn 5% nominal annual rate.

2. Examine the model that was set up on the Loan Analysis worksheet for one possible set of input values. Complete a Data Table in the range A15:B26 to find the monthly payment for different values of the loans interest rate, ranging from 2.0% to 4.5% in increments of 0.25%.

3. Use another Data Table, in the range A30:F35, to find the present value of all the loans related costs for different values of the loan term, as listed in the first column of the range, and the loan amount, as listed in the first row of the range, assuming the nominal annual loan rate is 3%.

Inputs Loan Amount Nominal Annual Loan Rate Term in Years Paid in full end of year Nominal Discounting rate Outputs Monthly Payment Pay Off Payment PV of all Loan costs $300,000 3.0% 20 10 5% $1,663.79 $171,876 $261,220.81 A B G I J 17 E F 1 SoftLuxe Loan Analysis 2 Inputs 3 Loan Amount $300,000 4 Nominal Annual Loan Rate 3.00% 5 Term In Years 20 6 Paid in full end of year 10 7 Nominal Discounting rate 5% 8 Outputs 9 Monthly Payment $1,663.79 10 Pay Off Payment 11 PV of all Loan costs 12 13 Find the monthly payment for different values of the nominal rate, varying from 2% to 4.5% 14 15 Nominal Annual Rate 16 2.00% 2.25% 18 2.50% 19 2.75% 20 3.00% 3.25% 22 3.50% 23 3.75% 24 4.00% 25 4.25% 26 4.50% 27 28 Find Presend Value of all Loan costs for the following combinations of term and loan amount 29 30 $270,000 $300,000 $350,000 $425,000 $450,000 31 10 32 15 33 20 34 25 35 30 36 21 Inputs Loan Amount Nominal Annual Loan Rate Term in Years Paid in full end of year Nominal Discounting rate Outputs Monthly Payment Pay Off Payment PV of all Loan costs $300,000 3.0% 20 10 5% $1,663.79 $171,876 $261,220.81 A B G I J 17 E F 1 SoftLuxe Loan Analysis 2 Inputs 3 Loan Amount $300,000 4 Nominal Annual Loan Rate 3.00% 5 Term In Years 20 6 Paid in full end of year 10 7 Nominal Discounting rate 5% 8 Outputs 9 Monthly Payment $1,663.79 10 Pay Off Payment 11 PV of all Loan costs 12 13 Find the monthly payment for different values of the nominal rate, varying from 2% to 4.5% 14 15 Nominal Annual Rate 16 2.00% 2.25% 18 2.50% 19 2.75% 20 3.00% 3.25% 22 3.50% 23 3.75% 24 4.00% 25 4.25% 26 4.50% 27 28 Find Presend Value of all Loan costs for the following combinations of term and loan amount 29 30 $270,000 $300,000 $350,000 $425,000 $450,000 31 10 32 15 33 20 34 25 35 30 36 21