Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. 0.00% 30 Initial Rate + Premium Number of Days Maturity (Years) First 30-Day Rate Initial Rate + Premium Last 30-Day Rate f. Caloulate the total dollar interest cost. Loan Amount First 30-Day Rate Last 30-Day Rate Total Interest Cost 0.00007 0.00007 g. Calculate the 60-day rate of interest. Loan Amount Total Interest Cost 60-Day Rate Assume that the loan is rolled over each 60 days throughout the year under the same conditions and terms. Calculate the effective annual rate of interest. 60-Day Rate Periods in Year Effective Annual Rate 0.00007 6.08333 i. Which loan would you choose, and why? because it has the lowest effective rate. Points 1 Requirements 1 In cell E31, by using cell references to the given data, calculate the total interest rate (prime interest rate plus the interest rate premium). 2 Incel E32, by using cell references to the given data, caloulate the loan maturity in years. 3 In cell E33, by using cell references to the given data, caloulate the total interest payment 4 In cell E39, by using cell references to the given data, caloulate the 60-day interest rate. 5 In cell E44, by using cell references to the given data, caloulate the number of 60-day periods 6 In cell E45, by using cell references to the given data, caloulate the effective annual interest 7 In cell E52, by using cell references to the given data, calculate the initial interest rate (first 30- dav interest ratel. 8 In cell E58, by using cell references to the given data, calculate the maturity in years. 9 In cell E59, by using cell references to the given data, caloulate the first 30-day interest rate. In cell E61, by using cell references to the given data, calculate the interest rate corresponding to the initial interest rate plus the premium rate. In cell E62, by using cell references to the given data, calculate the last 30-day interest rate. In cell E69, by using cell references to the given data, calculate the total interest cost. In cell E75, by using cell references to the given data, caloulate the 60-day interest rate. In cell E81, by using cell references to the given data, calculate the effective annual interest In cell C85, type either First Citizen or First American depending on which loan choice is Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 1 1 Your company is considering manufacturing protective cases for a popular new smartphone. Management decides to borrow $200,000 from each of two banks, First American and First Citizen. On the day that you visit both banks, the quoted prime interest rate is 7%. Each loan is similar in that each involves a 60-day note, with interest to be paid at the end of 60 days. The interest rate was set at 2% above the prime rate on First American's fixedrate note. Over the 60-day period, the rate of interest on this note will remain at the 2% premium over the prime rate regardless of fluctuations in the prime rate. First Citizen sets its interest rate at 1.5% above the prime rate on its floating-rate note. The rate charged over the 60 days will vary directly with the prime rate. To Do Create a spreadsheet to calculate and analyze the following for the First American loan: a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. b. Calculate the 60-day rate on the loan. Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. e. Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. f. Calculate the total dollar interest cost. g. Calculate the 60-day rate of interest. h. Assume that the loan is rolled over each 60 days throughout the year under the same conditions and terms. Calculate the effective annual rate of interest i. Which loan would you choose, and why? Solution Days in year Loan Amount Prime Rate -today Prime Rate - in 30 days Maturity (days) First American Premium First Citizen Premium 3651 ####### 7.0071 7.507 60 2.00% 1.507 a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. ####### Loan Amount Prime + Premium Maturity (Years) Total Dollar Interest b. Calculate the 60-day rate on the loan. Loan Amount Total Dollar Interest 60-Day Rate $ - Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. 60-Day Rate 0.00007 Number of 60-Day Periods/Year Effective Annual Rate Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. Prime Rate Premium Initial Rate, First 30-Day Rate 7.007 1.502 Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. 0.00% 30 Initial Rate + Premium Number of Days Maturity (Years) First 30-Day Rate Initial Rate + Premium Last 30-Day Rate f. Caloulate the total dollar interest cost. Loan Amount First 30-Day Rate Last 30-Day Rate Total Interest Cost 0.00007 0.00007 g. Calculate the 60-day rate of interest. Loan Amount Total Interest Cost 60-Day Rate Assume that the loan is rolled over each 60 days throughout the year under the same conditions and terms. Calculate the effective annual rate of interest. 60-Day Rate Periods in Year Effective Annual Rate 0.00007 6.08333 i. Which loan would you choose, and why? because it has the lowest effective rate. Points 1 Requirements 1 In cell E31, by using cell references to the given data, calculate the total interest rate (prime interest rate plus the interest rate premium). 2 Incel E32, by using cell references to the given data, caloulate the loan maturity in years. 3 In cell E33, by using cell references to the given data, caloulate the total interest payment 4 In cell E39, by using cell references to the given data, caloulate the 60-day interest rate. 5 In cell E44, by using cell references to the given data, caloulate the number of 60-day periods 6 In cell E45, by using cell references to the given data, caloulate the effective annual interest 7 In cell E52, by using cell references to the given data, calculate the initial interest rate (first 30- dav interest ratel. 8 In cell E58, by using cell references to the given data, calculate the maturity in years. 9 In cell E59, by using cell references to the given data, caloulate the first 30-day interest rate. In cell E61, by using cell references to the given data, calculate the interest rate corresponding to the initial interest rate plus the premium rate. In cell E62, by using cell references to the given data, calculate the last 30-day interest rate. In cell E69, by using cell references to the given data, calculate the total interest cost. In cell E75, by using cell references to the given data, caloulate the 60-day interest rate. In cell E81, by using cell references to the given data, calculate the effective annual interest In cell C85, type either First Citizen or First American depending on which loan choice is Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 1 1 1 Your company is considering manufacturing protective cases for a popular new smartphone. Management decides to borrow $200,000 from each of two banks, First American and First Citizen. On the day that you visit both banks, the quoted prime interest rate is 7%. Each loan is similar in that each involves a 60-day note, with interest to be paid at the end of 60 days. The interest rate was set at 2% above the prime rate on First American's fixedrate note. Over the 60-day period, the rate of interest on this note will remain at the 2% premium over the prime rate regardless of fluctuations in the prime rate. First Citizen sets its interest rate at 1.5% above the prime rate on its floating-rate note. The rate charged over the 60 days will vary directly with the prime rate. To Do Create a spreadsheet to calculate and analyze the following for the First American loan: a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. b. Calculate the 60-day rate on the loan. Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. e. Assuming that the prime rate immediately jumps to 7.5% and after 30 days it drops to 7.25%, calculate the interest rate for the first 30 days and the second 30 days of the loan. f. Calculate the total dollar interest cost. g. Calculate the 60-day rate of interest. h. Assume that the loan is rolled over each 60 days throughout the year under the same conditions and terms. Calculate the effective annual rate of interest i. Which loan would you choose, and why? Solution Days in year Loan Amount Prime Rate -today Prime Rate - in 30 days Maturity (days) First American Premium First Citizen Premium 3651 ####### 7.0071 7.507 60 2.00% 1.507 a. Calculate the total dollar interest cost on the loan. Assume a 365-day year. ####### Loan Amount Prime + Premium Maturity (Years) Total Dollar Interest b. Calculate the 60-day rate on the loan. Loan Amount Total Dollar Interest 60-Day Rate $ - Assume that the loan is rolled over each 60 days throughout the year under identical conditions and terms. Calculate the effective annual rate of interest on the fixed-rate, 60-day First American note. 60-Day Rate 0.00007 Number of 60-Day Periods/Year Effective Annual Rate Next, create a spreadsheet to calculate the following for the First Citizen loan: d. Calculate the initial interest rate. Prime Rate Premium Initial Rate, First 30-Day Rate 7.007 1.502